From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Durumdara <durumdara(at)gmail(dot)com>, Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Can I start Update row in After Insert trigger function? |
Date: | 2022-05-23 17:41:56 |
Message-ID: | d5b04cb8-99c2-8af5-d028-273c4fe245e5@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2022-05-23 17:48:38 | Re: list tablespaces named in custom format dump |
Previous Message | dzagashev@gmail.com | 2022-05-23 17:40:34 | Re: psql connect over ssl load balancer |