Ignore an error that occurs in a redshift stored procedure

user_433342 0
Viewed 689

I need to ignore an error when running a procedure, but the error still seems to propagate. Is it possible to just swallow it somehow?

CREATE OR REPLACE PROCEDURE myproc()
AS $$
BEGIN
  EXECUTE 'bad_statement;';
EXCEPTION WHEN OTHERS THEN
    NULL;
END;
$$ LANGUAGE plpgsql;

Then

call myproc();

will result in:

[2021-01-06 17:08:42] [42601][500310] Amazon Invalid operation: syntax error at or near "bad_statement";

Tested it in postgres and it ignores the error correctly.

amazon-redshift
1 Answers

Unfortunately I don't think there is a way to do this. Amazon's documentation states

In an Amazon Redshift stored procedure, the only supported handler_statement is RAISE. Any error encountered during the execution automatically ends the entire stored procedure call and rolls back the transaction. This occurs because subtransactions are not supported.

If an error occurs in the exception handling block, it is propagated out and can be caught by an outer exception handling block, if one exists.

stored-procedure-trapping-errors

user_142962540
Related