On 5/23/22 10:35 AM, Durumdara wrote:
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.
You need to read:
https://www.postgresql.org/docs/current/plpgsql-trigger.html
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.
Yes an INSERT is a new value only there is no old row(value) available.
In the doc link I posted:
"
TG_OP
Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE
telling for which operation the trigger was fired.
"
Use this to modify what value(NEW/OLD) you are working with. Call
trigger with UPDATE and INSERT.
Version: PGSQL 9.6-11
Thank you for your help!
Best regards,
dd
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
