Update Multiple Columns in a Trigger Function in Plpgsql

Update multiple columns in a trigger function in plpgsql

While @Gary's answer is technically correct, it fails to mention that PostgreSQL does support this form:

UPDATE tbl
SET (col1, col2, ...) = (expression1, expression2, ..)

Read the manual on UPDATE.

It's still tricky to get this done with dynamic SQL. I'll assume a simple case where views consist of the same columns as their underlying tables.

CREATE VIEW tbl_view AS SELECT * FROM tbl;

Problems

The special record NEW is not visible inside EXECUTE. I pass NEW as a single parameter with the USING clause of EXECUTE.

As discussed, UPDATE with list-form needs individual values. I use a subselect to split the record into individual columns:

UPDATE ...
FROM  (SELECT ($1).*) x

(Parenthesis around $1 are not optional.) This allows me to simply use two column lists built with string_agg() from the catalog table: one with and one without table qualification.

It's not possible to assign a row value as a whole to individual columns. The manual:

According to the standard, the source value for a parenthesized
sub-list of target column names can be any row-valued expression
yielding the correct number of columns. PostgreSQL only allows the
source value to be a row constructor or a sub-SELECT.

INSERT is implemented simpler. If the structure of view and table are identical we can omit the column definition list. (Can be improved, see below.)

Solution

I made a couple of updates to your approach to make it shine.

Trigger function for UPDATE:

CREATE OR REPLACE FUNCTION f_trg_up()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl  regclass := quote_ident(TG_TABLE_SCHEMA) || '.'
                  || quote_ident(substring(TG_TABLE_NAME from '(.+)_view$'));
   _cols text;
   _vals text;
BEGIN
   SELECT INTO _cols, _vals
          string_agg(quote_ident(attname), ', ')
        , string_agg('x.' || quote_ident(attname), ', ')
   FROM   pg_attribute
   WHERE  attrelid = _tbl
   AND    NOT attisdropped   -- no dropped (dead) columns
   AND    attnum > 0;        -- no system columns

   EXECUTE format('
   UPDATE %s
   SET   (%s) = (%s)
   FROM  (SELECT ($1).*) x', _tbl, _cols, _vals)
   USING NEW;

   RETURN NEW; -- Don't return NULL unless you knwo what you're doing
END
$func$;

Trigger function for INSERT:

CREATE OR REPLACE FUNCTION f_trg_ins()
  RETURNS TRIGGER
  LANGUAGE plpgsql AS
$func$
DECLARE
   _tbl regclass := quote_ident(TG_TABLE_SCHEMA) || '.'
                 || quote_ident(substring(TG_TABLE_NAME FROM '(.+)_view$'));
BEGIN
   EXECUTE format('INSERT INTO %s SELECT ($1).*', _tbl)
   USING NEW;

   RETURN NEW;  -- Don't return NULL unless you know what you're doing
END
$func$;

Triggers:

CREATE TRIGGER trg_instead_up
INSTEAD OF UPDATE ON a_view
FOR EACH ROW EXECUTE FUNCTION f_trg_up();

CREATE TRIGGER trg_instead_ins
INSTEAD OF INSERT ON a_view
FOR EACH ROW EXECUTE FUNCTION f_trg_ins();

Before Postgres 11 the syntax (oddly) was EXECUTE PROCEDURE instead of EXECUTE FUNCTION - which also still works.

db<>fiddle here - demonstrating INSERT and UPDATE

_{Old sqlfiddle}

Major points

• Include the schema name to make the table reference unambiguous. There can be multiple table of the same name in one database with multiple schemas!

• Query pg_catalog.pg_attribute instead of information_schema.columns. Less portable, but much faster and allows to use the table-OID.

  • How to check if a table exists in a given schema

• Table names are NOT safe against SQLi when concatenated as strings for dynamic SQL. Escape with quote_ident() or format() or with an object-identifer type. This includes the special trigger function variables TG_TABLE_SCHEMA and TG_TABLE_NAME!

• Cast to the object identifier type regclass to assert the table name is valid and get the OID for the catalog look-up.

• Optionally use format() to build the dynamic query string safely.

• No need for dynamic SQL for the first query on the catalog tables. Faster, simpler.

• Use RETURN NEW instead of RETURN NULL in these trigger functions unless you know what you are doing. (NULL would cancel the INSERT for the current row.)

• This simple version assumes that every table (and view) has a unique column named id. A more sophisticated version might use the primary key dynamically.

• The function for UPDATE allows the columns of view and table to be in any order, as long as the set is the same.
  The function for INSERT expects the columns of view and table to be in identical order. If you want to allow arbitrary order, add a column definition list to the INSERT command, just like with UPDATE.

• Updated version also covers changes to the id column by using OLD additionally.

how to create trigger after update of a table on multiple column in postgresql?

As documented in the manual the column name(s) are listed after the OF keyword.

So it should be:

create trigger save_information 
     after update of day, month
     on table_name 
for each row execute procedure save_function();

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

That would simply work like this:

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

Trigger function that Updates some columns after update of a specific column

You can use the old pseudo record to access the values before the update. It doesn't make sense to set old values though. And such a trigger makes no sense for INSERT as there is no old value that can change here. And you're not lowering the price by 20% you set it to 20%.

Something like the following should work as you want.

CREATE OR REPLACE FUNCTION automatic_update()
                           RETURNS TRIGGER
AS
$$ 
BEGIN
  IF new.starting_date <> old.starting_date THEN
    new.price := old.price * .8; 
  END IF; 

  RETURN new; 
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER automation
               BEFORE UPDATE
               ON public.ticketsforsale
               FOR EACH ROW
               EXECUTE PROCEDURE automatic_update();

You could also move the condition to the triggers WHEN.

CREATE OR REPLACE FUNCTION automatic_update()
                           RETURNS TRIGGER
AS
$$ 
BEGIN
  new.price := old.price * .8; 

  RETURN new; 
END;
$$ 
LANGUAGE plpgsql;

CREATE TRIGGER automation
               BEFORE UPDATE
               ON public.ticketsforsale
               FOR EACH ROW
               WHEN (new.starting_date <> old.starting_date)
               EXECUTE PROCEDURE automatic_update();

Postgres rule or trigger to update multiple rows after update of single row

Use an AFTER trigger:

create table loc_tbl (id integer, x_loc numeric);
insert into loc_tbl values (1000,12.7), (1500,13.2), (1001,12.7), (1502,13.2), (1002,12.8);

CREATE OR REPLACE FUNCTION public.x_loc_fnc()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
BEGIN
    IF NEW.x_loc != OLD.x_loc THEN
        UPDATE 
            loc_tbl
        SET 
            x_loc = NEW.x_loc
        WHERE 
            left(NEW.id::varchar, 3) = left(id::varchar, 3);
    END IF;
RETURN null;
END;

$function$
;

CREATE TRIGGER 
    loc_trg 
AFTER UPDATE ON 
    loc_tbl 
FOR EACH ROW EXECUTE FUNCTION 
    x_loc_fnc();

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1000 |  12.7
 1500 |  13.2
 1001 |  12.7
 1502 |  13.2
 1002 |  12.8

UPDATE loc_tbl SET x_loc = 12.9 WHERE id = 1000;
UPDATE 1

select * from loc_tbl ;
  id  | x_loc 
------+-------
 1500 |  13.2
 1502 |  13.2
 1001 |  12.9
 1002 |  12.9
 1000 |  12.9

Postgresql using Trigger to update column values after inserting values

It is terribly inefficient to update the row after you inserted it. Better is to use a BEFORE trigger that can modify the new row before it is inserted:

CREATE OR REPLACE FUNCTION cal() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN 
   NEW.amount_hkd := NEW.amounts * NEW.currency_conversion_rate;
   RETURN NEW; 
END;$$;

CREATE TRIGGER update_amount 
   BEGORE INSERT ON tableA FOR EACH ROW 
   EXECUTE PROCEDURE cal();

---

Related Topics