PostgreSQL Exception Handling

Question

I am new to PostgreSQL. Could anybody please correct this query.

BEGIN TRANSACTION;

BEGIN;
    CREATE TABLE "Logs"."Events"
    (
        EventId BIGSERIAL NOT NULL PRIMARY KEY,
        PrimaryKeyId bigint NOT NULL,
        EventDateTime date NOT NULL DEFAULT(now()),
        Action varchar(12) NOT NULL,
        UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
        PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
    );

    CREATE TABLE "Logs"."EventDetails"
    (
        EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
        EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
        Resource varchar(64) NOT NULL,
        OldVal varchar(4000) NOT NULL,
        NewVal varchar(4000) NOT NULL
    );


    COMMIT TRANSACTION;
    RAISE NOTICE 'Task completed sucessfully.'
EXCEPTION;
    ROLLBACK TRANSACTION;
    RAISE ERROR @ErrorMessage, @LineNumber, @ErrorState --how to catch errors and display them????
END;

Questions:

1. How to print a message like 'PRINT' in T-SQL?
2. How to raise errors with exception information?

Answer 1

You could write this as a psql script, e.g.,

START TRANSACTION;
CREATE TABLE ...
CREATE TABLE ...
COMMIT;
\echo 'Task completed sucessfully.'

and run with

psql -f somefile.sql

Raising errors with parameters isn't possible in PostgreSQL directly. When porting such code, some people encode the necessary information in the error string and parse it out if necessary.

It all works a bit differently, so be prepared to relearn/rethink/rewrite a lot of things.

Answer 2

Use the DO statement, a new option in version 9.0:

DO LANGUAGE plpgsql
$$
BEGIN
CREATE TABLE "Logs"."Events"
    (
        EventId BIGSERIAL NOT NULL PRIMARY KEY,
        PrimaryKeyId bigint NOT NULL,
        EventDateTime date NOT NULL DEFAULT(now()),
        Action varchar(12) NOT NULL,
        UserId integer NOT NULL REFERENCES "Office"."Users"(UserId),
        PrincipalUserId varchar(50) NOT NULL DEFAULT(user)
    );

    CREATE TABLE "Logs"."EventDetails"
    (
        EventDetailId BIGSERIAL NOT NULL PRIMARY KEY,
        EventId bigint NOT NULL REFERENCES "Logs"."Events"(EventId),
        Resource varchar(64) NOT NULL,
        OldVal varchar(4000) NOT NULL,
        NewVal varchar(4000) NOT NULL
    );

    RAISE NOTICE 'Task completed sucessfully.';    
END;
$$;

Answer 3

Just want to add my two cents on this old post:

In my opinion, almost all of relational database engines include a commit transaction execution automatically after execute a DDL command even when you have autocommit=false, So you don't need to start a transaction to avoid a potential truncated object creation because It is completely unnecessary.

Answer 4

To catch the error message and its code:

do $$       
begin

    create table yyy(a int);
    create table yyy(a int); -- this will cause an error

exception when others then 

    raise notice 'The transaction is in an uncommittable state. '
                 'Transaction was rolled back';

    raise notice '% %', SQLERRM, SQLSTATE;

end; $$ 
language 'plpgsql';

Haven't found the line number yet

UPDATE April, 16, 2019

As suggested by Diego Scaravaggi, for Postgres 9.2 and up, use GET STACKED DIAGNOSTICS:

do language plpgsql $$
declare
    v_state   TEXT;
    v_msg     TEXT;
    v_detail  TEXT;
    v_hint    TEXT;
    v_context TEXT;
begin

    create table yyy(a int);
    create table yyy(a int); -- this will cause an error

exception when others then 

    get stacked diagnostics
        v_state   = returned_sqlstate,
        v_msg     = message_text,
        v_detail  = pg_exception_detail,
        v_hint    = pg_exception_hint,
        v_context = pg_exception_context;

    raise notice E'Got exception:
        state  : %
        message: %
        detail : %
        hint   : %
        context: %', v_state, v_msg, v_detail, v_hint, v_context;

    raise notice E'Got exception:
        SQLSTATE: % 
        SQLERRM: %', SQLSTATE, SQLERRM;     

    raise notice '%', message_text; -- invalid. message_text is contextual to GET STACKED DIAGNOSTICS only

end; $$;

Result:

NOTICE:  Got exception:
        state  : 42P07
        message: relation "yyy" already exists
        detail : 
        hint   : 
        context: SQL statement "create table yyy(a int)"
PL/pgSQL function inline_code_block line 11 at SQL statement
NOTICE:  Got exception:
        SQLSTATE: 42P07 
        SQLERRM: relation "yyy" already exists

ERROR:  column "message_text" does not exist
LINE 1: SELECT message_text
               ^
QUERY:  SELECT message_text
CONTEXT:  PL/pgSQL function inline_code_block line 33 at RAISE
SQL state: 42703

Aside from GET STACKED DIAGNOSTICS is SQL standard-compliant, its diagnostics variables (e.g., message_text) are contextual to GSD only. So if you have a field named message_text in your table, there's no chance that GSD can interfere with your field's value.

Still no line number though.