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

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.