Update multiple columns in a trigger function in plpgsql

Question

Given the following schema: ``` create table account_type_a ( id SERIAL UNIQUE PRIMARY KEY, some_column VARCHAR ); create table account_type_b ( id SERIAL UNIQUE PRIMARY KEY, some_other_column VARCHAR ); create view account_type_a view AS select * from account_type_a; create view account_type_b view AS select * from account_type_b; ``` I try to create a generic trigger function in plpgsql, which enables updating the view: ``` create trigger trUpdate instead of UPDATE on account_view_type_a for each row execute procedure updateAccount(); create trigger trUpdate instead of UPDATE on account_view_type_a for each row execute procedure updateAccount(); ``` An unsuccessful effort of mine was: ``` create function updateAccount() returns trigger as $$ declare target_table varchar := substring(TG_TABLE_NAME from '(.+)_view'); cols varchar; begin execute 'select string_agg(column_name,$1) from information_schema.columns where table_name = $2' using ',', target_table into cols; execute 'update '…

Accepted Answer

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.

Update multiple columns in a trigger function in plpgsql

Solution

Problem

Related problems