Modify OLD to be returned by DELETE in postgresql trigger function

Question

I have a trigger function in postgresql which will insert rows in the audit table on INSERT, UPDATE and DELETE operations. In my tables, there is a column called audit_id and I need to write the ID of inserted audit row in this field. This is my function

CREATE OR REPLACE FUNCTION my_audit_trigger()
 RETURNS trigger LANGUAGE plpgsql
AS $function$
declare
  audit_pk bigint;
begin
IF TG_OP = 'INSERT'
THEN
INSERT INTO audit.table_audit (rel_id, table_name, operation, after)
VALUES (TG_RELID, TG_TABLE_NAME, TG_OP, to_jsonb(NEW)) returning id into audit_pk;
 NEW.audit_id := audit_pk;
RETURN NEW;
ELSIF TG_OP = 'UPDATE'
THEN
IF NEW != OLD THEN
 INSERT INTO audit.table_audit (rel_id, table_name, operation, before, after)
VALUES (TG_RELID, TG_TABLE_NAME, TG_OP, to_jsonb(OLD), to_jsonb(NEW)) returning id into audit_pk;
END IF;
 NEW.audit_id := audit_pk;
RETURN NEW;
ELSIF TG_OP = 'DELETE'
THEN
INSERT INTO audit.table_audit (rel_id, table_name, operation, before)
VALUES (TG_RELID, TG_TABLE_NAME, TG_OP, to_jsonb(OLD)) returning id into audit_pk;
OLD.audit_id := audit_pk;
RETURN OLD;
END IF;
end;
$function$;

As a result, when inserting or updating my table rows, I get back the audit id of the corresponding operation, but when I run DELETE command, I get back the audit ID of the previous operation, not of the DELETE itself. So I guess the problem is in OLD.audit_id := audit_pk;

More specifically, I run for example INSERT INTO table VALUES (this, that) RETURNING audit_id and I get back audit_id of the INSERT operation.

After, when running DELETE FROM table WHERE id = sth RETURNING audit_id I get audit_id of the INSERT operation, not of the DELETE.

Any help is appreciated, thank you.

P.S. This is how I create trigger

CREATE TRIGGER table_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON table
FOR EACH ROW
EXECUTE PROCEDURE my_audit_trigger();

I don't quite understand. How do you "get back" something from a `DELETE`? — Laurenz Albe, Apr 03 '19 at 10:47
Welcome to StackOverflow! Could you improve your question by sharing the output you're seeing and what you'd expect instead? — A. Stam, Apr 03 '19 at 10:48
When I run ``` DELETE FROM table WHERE id = 12 RETURNING * ``` I get back a row which is returned by the trigger function — Sophio, Apr 03 '19 at 10:48
We can't see how the `id` field, which is returned into `audit_pk`, obtains its value, as that field is not specified in the insert statement. The issue must lie there - where is that value coming from, and why is it being set to an unexpected value? There isn't a problem I can see in the trigger itself, it must be in the actual data in that audit table. Bearing in mind it's not even the table that the insert or update statements write to, so how is that value being set in the `affiliate_audit` table? Only you have access to the info to figure that out. — 404, Apr 03 '19 at 12:27
@404 The id gets auto-generated on every insert in the audit table. I think that part is ok as it works exactly as expected for INSERT and UPDATE. When running the DELETE, OLD contains the whole row that is being deleted, including the audit_id of the previous operation. What I am trying to do is to update that old audit_id with a new one but that does not work, I don't know why — Sophio, Apr 03 '19 at 12:36
Ignore the OLD bit for the moment. When you do a `DELETE ... RETURNING *`, is the returned `audit_id` the same as the `id` field of the deletion audit row in `audit.table_audit`? If so then the trigger is correct. If you think `audit_id` should be a new value, then you have to ensure that `id` is written with a new value when a deletion record is inserted. — 404, Apr 03 '19 at 12:42
@404 That is the whole issue, when I do ```DELETE ... RETURNING *``` returned audit_id is not the same as the id field of the DELETE operation in the ```audit.table_audit``` instead, it is the same as the id of the previous operation, e.g. INSERT. On the other hand, when I do ```INSERT ... RETUNING *```, audit_id is the same as the ```id``` of the insert operation in the audit table. P.S. audit table id is an integer, auto incremented. New row gets added to this table every time an ```INSERT, UPDATE or DELETE``` happens — Sophio, Apr 03 '19 at 12:48
@404 PostgreSQL 10.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit — Sophio, Apr 03 '19 at 13:02
I was hoping to do this for REST API (using [PostgREST](http://postgrest.org/)) for an endpoint that has a `status` and a `last_modified` field. When `DELETE` is received I was hoping to put "deleted" in the `status` and `NOW()` in the `last_modified` fields, and have it show that in the returned value when the `Prefer: return=representation` header is specified. — user9645, Dec 31 '19 at 14:58

score 1 · Accepted Answer · answered Nov 19 '19 at 14:57

I have similar problem. That seems that PG just does not support modification of OLD now, but, probably, this feature will be included into TODO list.

Currently you can modify only NEW for INSERT and UPDATE statements

For details look into this mail thread: Does 'instead of delete' trigger support modification of OLD

Modify OLD to be returned by DELETE in postgresql trigger function

1 Answers1