How to update the tables NEW values after INSERT Trigger in PostgreSQL/PostGIS?

Question

I try to automatize some calculations on tables in my database. I try to perform some UPDATE on rows that are newly inserted in a table, but I newer used NEW or OLD statements before. I tried writing the code that updates happen on new values by assigning NEW.[tablename], but it wont work. Isn't there any statement in the beginning of the trigger function to specify running the function only on new values, I cannot find useful information about this.

CREATE OR REPLACE FUNCTION cost_estimation() 
    RETURNS TRIGGER AS
$func$
    DECLARE
        a INTEGER := 3;

    BEGIN
        UPDATE NEW.cost_table
            SET  column4 = a;

        UPDATE NEW.cost_table
            SET column 5 = column4 - column2;   

    [...]


    RETURN NEW;                       
    END
$func$ language plpgsql

UPDATE: Thank you for the answers so far. My original code is written based on the update structure, and needs to be rewritten when omitting UPDATE. I should give a better example of my situation. Easy spoken: I have a table (T1) which will be filled with data from another table (T2). After data is inserted in T1 from T2 I want to run calculations on the new values inside of T1.(The code includes PostGIS functionalities):

  CREATE OR REPLACE FUNCTION cost_estimation() 
            RETURNS TRIGGER AS
        $func$
            
            BEGIN
           
                NEW.column6 = column2 FROM external_table WHERE
                        St_Intersects(NEW.geom, external_table.geom) LIMIT1;

               
                NEW.column8 = CASE 
                        WHEN st_intersects(NEW.geom, external_table2.geom) then 'intersects'
                        WHEN (NEW.column9 = 'K' and NEW.column10 <= 6) then 'somethingelse'
                        ELSE 'nothing'
                   END
                FROM external_table2;


            [...]
    
    
            RETURN NEW;                       
            END
        $func$ language plpgsql

CREATE TRIGGER table_calculation_on_new
BEFORE INSERT OR UPDATE ON cost_estimation
FOR EACH ROW EXECUTE PROCEDURE road_coast_estimation();

After inserting values in my table no calculations will be performed.

UPDATE2: I checked my tables again and detected that another trigger was blocking the table operation. The code in the lower half is working fine now, thanks to @a_horse_with_no_name.

If you change the new record you have to define your trigger as a before trigger as I already mentioned in my answer. — user330315
– user330315, Commented Jun 23, 2020 at 16:41
Perhaps a good place to start would be the Postgress Documentation on Create Trigger. And perhaps links within there. — Belayer
– Belayer, Commented Jun 24, 2020 at 4:08

user330315 · Accepted Answer · 2020-06-23 12:35:57Z

5

NEW and OLD aren't "statements", those are records that represent the modified rows from the DML statement that fired the trigger.

Assuming the trigger is defined on cost_table you can simply change the fields in the NEW record. No need to UPDATE anything:

CREATE OR REPLACE FUNCTION cost_estimation() 
    RETURNS TRIGGER AS
$func$
DECLARE
  a INTEGER := 3;
BEGIN
  new.column4 := a;
  new.column5 := new.column4 - new.column2;
  return new;
END;
$func$ language plpgsql

For this to work the trigger needs to be defined as a BEFORE trigger:

create trigger cost_table_trigger
  BEFORE insert or update on cost_table
  for each row execute procedure cost_estimation();

answered Jun 23, 2020 at 12:35

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

DrSnuggles Over a year ago

The original code is more complex and a lot of stuff happens in each update block so I need to preserve the update structure. Is there not a "wrapper" where I can state something like: "on NEW entries ... update1,2,3 ... END"?

user330315 Over a year ago

new only contains a single row/record so your question doesn't really make sense to me.

Akhilesh Mishra Over a year ago

@DrSnuggles if you see the trigger block here it is already defined that it will call the Function before Insert operation on table. so it is doing the same thing what you are asking.

Collectives™ on Stack Overflow

How to update the tables NEW values after INSERT Trigger in PostgreSQL/PostGIS?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related