PostgreSQL Exception Handling

To catch the error message and its code:

do $$       begin    create table yyy(a int);    create table yyy(a int); -- this will cause an errorexception 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 errorexception 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 onlyend; $$;

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 statementNOTICE:  Got exception:        SQLSTATE: 42P07         SQLERRM: relation "yyy" already existsERROR:  column "message_text" does not existLINE 1: SELECT message_text               ^QUERY:  SELECT message_textCONTEXT:  PL/pgSQL function inline_code_block line 33 at RAISESQL 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.

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

DO LANGUAGE plpgsql$$BEGINCREATE 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;$$;

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.