Throw exception in postgresql function

Question

I'm new to Postgresql, so I don't want to create my own structures for some basic tasks....
My task is to create function and throw exception (RAISE EXCEPTION 'test') in some conditions for client to catch it or rethrow in case of not handling it properly. The problem is that before throwing I wanted to LOG the exception to special table in the database, but realized that throwing error is rollbacking the changes made before! Is there any way to change this behavior or all I can do - add some CODE output parameter and throw errors on the client based on that CODE? Sample code that I'm using now:

CREATE OR REPLACE FUNCTION fn_...()
...
BEGIN
    IF nretry_count >= nmax_retry
    THEN
      INSERT INTO log VALUES (error_type, value) VALUES (1,'Max retry exceeded!');
      RAISE EXCEPTION 'Max retry count exceeded';
    END IF;
END
$$ LANGUAGE plpgsql;

maybe you can work with some kind of trigger before insert. In a Trigger-Function you could raise the exception and log the data to another table. postgresql.org/docs/9.6/static/plpgsql-trigger.html — Ben H
– Ben H, Commented Feb 13, 2017 at 8:50
@BenH not in this case: trigger can be set on some table action. In my case I have no table action, just some conditions, not directly related to any table. — 0x49D1
– 0x49D1, Commented Feb 13, 2017 at 9:34

Laurenz Albe · Accepted Answer · 2017-02-13 08:41:26Z

4

You could start a transaction and set a SAVEPOINT in your application. Then, after you catch the exception, you ROLLBACK TO SAVEPOINT, create the log entry and COMMIT.

There is no way to throw an exception from inside a function and perform a data modification that is not rolled back, because there are no “autonomous transactions” in PostgreSQL.

answered Feb 13, 2017 at 8:41

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

0x49D1 Over a year ago

Ok, got it: so I can't use functions to create some action chains with exceptions in some cases. Have to do that from client.

Collectives™ on Stack Overflow

Throw exception in postgresql function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related