From: | Durumdara <durumdara(at)gmail(dot)com> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Can I start Update row in After Insert trigger function? |
Date: | 2022-05-23 17:35:59 |
Message-ID: | CAEcMXhmxEhD=7-uHDivP5Z76PYkgGOqBZikv8Jqtyja3S8Q4rw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello!
I need to log some row changes in a table with JSON format (row_to_json).
In the Before Update I can select the row into a column with JSON format.
And in After Update I can log this column into an archive table.
Plus I have the prior data in the JSON_TEXT field.
It's ok.
FUNCTION Before_Update
...
BEGIN
select row_to_json(thistable) into json_text_var
from thistable where id = OLD.id;
NEW.json_text = json_text_var;
RETURN NEW;
END;
FUNCTION After_Update
...
BEGIN
insert into logtable select 'thistable', NEW.id, NEW.json_TEXT;
RETURN NEW;
END;
But this technique isn't working in insert, because no OLD.id. and OLD row:
select row_to_json(thistable) into json_text_var
from thistable where id = ???.id;
No row!!! Only NEW.nn variables.
Only way if I call an update:
FUNCTION After_Insert
...
BEGIN
-- We try to update the log with a dummy update
-- This calls Before/After Update, logs + fills the json field
update thistable set json_text = json_text where id = NEW.id;
RETURN NEW;
END;
Does this cause problems?
If I call an Update in the Row before After_Insert finishes?
Version: PGSQL 9.6-11
Thank you for your help!
Best regards,
dd
From | Date | Subject | |
---|---|---|---|
Next Message | dzagashev@gmail.com | 2022-05-23 17:40:34 | Re: psql connect over ssl load balancer |
Previous Message | Tom Lane | 2022-05-23 17:32:39 | Re: psql connect over ssl load balancer |