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    How to print a message like  PRINT  in T-SQL  How to raise errors with exception information

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[postgresql] PostgreSQL Exception Handling

Examples related to postgresql

Examples related to plpgsql