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;