PostgreSQL 9.5: Error handling exceptions

Viewed 23

I have developed an Oracle procedure to handle execeptions during a script execution, however, I am trying to figure out how do do the same for PostgreSQL and I can't find any examples, also I tried using IF NOT EXISTS in the creation syntax, but it seems the version of postgresql im using does not recognize it.

Oracle Procedure

  CREATE OR REPLACE PROCEDURE exec_neo_command (str IN VARCHAR2)
IS
  col_already_exists EXCEPTION;
  object_already_exists EXCEPTION;
  columns_indexed EXCEPTION;
  col_alreadyNull EXCEPTION;
  col_alreadyNull2 EXCEPTION;
  
  PRAGMA EXCEPTION_INIT(object_already_exists, -955);
  PRAGMA EXCEPTION_INIT(col_already_exists, -01430);
  PRAGMA EXCEPTION_INIT(columns_indexed, -1408);
  PRAGMA EXCEPTION_INIT(col_alreadyNull, -01442);
  PRAGMA EXCEPTION_INIT(col_alreadyNull2, -1451);
BEGIN
-- dbms_output.put_line (str || ' = command in exec_neo_command'); -- FOR DEBUGGING ONLY
  EXECUTE IMMEDIATE str;
EXCEPTION
  WHEN col_already_exists OR object_already_exists OR columns_indexed THEN
   dbms_output.put_line(str || ': Already exists, skipping...'); 
   
  WHEN col_alreadyNull OR col_alreadyNull2 THEN
  dbms_output.put_line(str || ': Already NULL, skipping...'); 
END exec_neo_command;

and I execute it as following

BEGIN
       exec_neo_command ('ALTER TABLE XtkEnumValue ADD iOrder NUMBER(20) DEFAULT 0');
       exec_neo_command ('ALTER TABLE XtkReport ADD iDisabled NUMBER(3) DEFAULT 0');
       exec_neo_command ('ALTER TABLE XtkWorkflow ADD iDisabled NUMBER(3) DEFAULT 0');             
END;
/   

However, I need to handle these following exeptions in the same way for postgreSQL

Erreur PostgreSQL : ERROR:  column "idisabled" of relation "xtkworkflow" already exists\n.   
Erreur PostgreSQL : ERROR:  column "imaxpersotime" of relation "nmsdelivery" already exists\n

The error trapping doc seems to point out a way (https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING) but I don't see any codes in the error to create the handles? can someone provide an example?

43.6.8. Trapping Errors

By default, any error occurring in a PL/pgSQL function aborts execution of the function and the surrounding transaction. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause. The syntax is an extension of the normal syntax for a BEGIN block:

[ <<label>> ]
[ DECLARE
    declarations ]
BEGIN
    statements
EXCEPTION
    WHEN condition [ OR condition ... ] THEN
        handler_statements
    [ WHEN condition [ OR condition ... ] THEN
          handler_statements
      ... ]
END;
0 Answers
Related