Pseudo code
INSERT INTO table_a
INSERT INTO table_b
RAISE EXCEPTION
UPDATE table_b
Is there a way to keep the inserted table_a tuples but not the table_b tuples when this code exists within a function?
PostgreSQL 9.6 – How to Perform Partial Rollback in Functions
postgresqlpostgresql-9.6transaction
Related Question
- Postgresql – Raise Multiple Exceptions Postgres
- PostgreSQL – Trigger to Update Column on Insert or Update in Another Table
- PostgreSQL – Handling Unknown Column Types During Migration
- PostgreSQL – Preventing Implicit Casting from bytea to text
- PostgreSQL – How to Perform Periodic Commits in Purge Function
- PostgreSQL – Handling Transactions Without Rollback on Error
- Postgresql – In which tables values will be inserted if I insert values into a view that its result is made out of inner join of 3 tables
Best Answer
Something like this:
DECLARE _exception_message text; _exception_hint text; BEGIN INSERT INTO table_a BEGIN INSERT INTO table_b RAISE EXCEPTION UPDATE table_b RETURN; EXCEPTION WHEN OTHERS THEN GET STACKED DIAGNOSTICS _exception_message = MESSAGE_TEXT, _exception_hint = PG_EXCEPTION_HINT; RETURN; END; ENDReference: https://stackoverflow.com/questions/42638907/savepoint-in-postgresql-function