CODEWITHSUNDEEP
postgresql
dvasdekis
dvasdekis

Reputation: 167

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

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.

Upvotes: 1

Views: 757

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

That would simply work like this:

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

Upvotes: 3

Related Questions