Re: Can I start Update row in After Insert trigger function? - Mailing list pgsql-general

From Adrian Klaver
Subject Re: Can I start Update row in After Insert trigger function?
Date
Msg-id d5b04cb8-99c2-8af5-d028-273c4fe245e5@aklaver.com
Whole thread Raw
In response to Can I start Update row in After Insert trigger function?  (Durumdara <durumdara@gmail.com>)
Responses Re: Can I start Update row in After Insert trigger function?
List 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.klaver@aklaver.com



pgsql-general by date:

Previous
From: "dzagashev@gmail.com"
Date:
Subject: Re: psql connect over ssl load balancer
Next
From: Rob Sargent
Date:
Subject: Re: list tablespaces named in custom format dump