Changing the value of multiple columns in a BEFORE INSERT trigger in Postgresql

Question

I have a table, on which I want to update multiple columns when a new row is inserted. My current trigger, which only updates a single column, looks like the below (simplified):

CREATE OR REPLACE FUNCTION func_only_updates_one_column()
RETURNS trigger AS
    $BODY$
BEGIN
    NEW.one_column := (SELECT rslt.one_column FROM
        (SELECT one_column, two_column, three_column, more_column
         FROM NEW
         JOIN a_very_complicated_query avcq
         ON avcq.one_column =  NEW.one_column
         AND avcq.two_column =  NEW.two_column) rslt;
    RETURN NEW;
END
    $BODY$
    LANGUAGE 'plpgsql';

CREATE TRIGGER trig_update_table BEFORE INSERT on target_table
FOR EACH ROW EXECUTE PROCEDURE func_only_updates_one_column();

I could create another trigger for my other column with the same logic, or run the query multiple times, but I don't want to run a SELECT from a_very_complicated_query multiple times - it's quite expensive as-is, and my application depends on this query being fast.

How can I update multiple columns in a BEFORE INSERT trigger with a single subquery referring to the NEW row? I only want to run the SQL once. I am using Postgresql 11.

Laurenz Albe · Accepted Answer · 2020-01-16 05:00:35Z

3

That would simply work like this:

SELECT rslt.one_column,
       rslt.two_column,
       ...
INTO NEW.one_column,
     NEW.two_column,
     ...
FROM /* your complicated query */

answered Jan 16, 2020 at 5:00

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

1 Comment

dvasdekis Over a year ago

So easy!! Thank you :)

Collectives™ on Stack Overflow

Changing the value of multiple columns in a BEFORE INSERT trigger in Postgresql

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related