Update multiple columns in a trigger function in p

问题:

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 ' || target_table || ' set (' || cols || ') =  select ($1).*
           where id = ($1).id' using NEW;
  return NULL;
end;
$$ language plpgsql;

The problem is the update statement. I am unable to come up with a syntax that would work here. I have successfully implemented this in PL/Perl, but would be interested in a plpgsql-only solution.
Any ideas?

Update

As @Erwin Brandstetter suggested, here is the code for my PL/Perl solution. I incoporated some of his suggestions.

create function f_tr_up() returns trigger as $$
  use strict;
  use warnings;
  my $target_table = quote_ident($_TD->{'table_name'}) =~ s/^([\w]+)_view$/$1/r;
  my $NEW = $_TD->{'new'};
  my $cols = join(',', map { quote_ident($_) } keys $NEW);
  my $vals = join(',', map { quote_literal($_) } values $NEW);
  my $query = sprintf(
    "update %s set (%s) = (%s) where id = %d",
    $target_table,
    $cols,
    $vals,
    $NEW->{'id'});
  spi_exec_query($query);
return;
$$ language plperl;

回答1:

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

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

Read the manual on UPDATE once more.

It's still tricky to get his done with dynamic SQL. Since you didn't specify, I am assuming 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. Assuming the structure of view and table are identical I omit the column definition list. (Can be improved, see below.)

Solution

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

Trigger function for UPDATE:

CREATE OR REPLACE FUNCTION f_trg_up()
  RETURNS TRIGGER AS
$func$
DECLARE
   tbl  text := 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::regclass
   AND    NOT attisdropped   -- no dropped (dead) columns
   AND    attnum > 0;        -- no system columns

   EXECUTE format('
   UPDATE %s t
   SET   (%s) = (%s)
   FROM  (SELECT ($1).*) x
   WHERE  t.id = ($2).id'
   , tbl, cols, vals) -- assuming unique "id" in every table
   USING NEW, OLD;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Trigger function for INSERT:

CREATE OR REPLACE FUNCTION f_trg_ins()
  RETURNS TRIGGER AS
$func$
DECLARE
    tbl text := quote_ident(TG_TABLE_SCHEMA) || '.'
             || quote_ident(substring(TG_TABLE_NAME from '(.+)_view$'));
BEGIN
   EXECUTE 'INSERT INTO ' || tbl || ' SELECT ($1).*'
   USING NEW;

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

Triggers:

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

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

SQL Fiddle demonstrating INSERT and UPDATE.

Major points

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

• Query pg_attribute instead of information_schema.columns. That's less portable, but much faster and allows me to use the table-OID.

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

• Table names are NOT safe against SQLi when handled as strings like in building queries 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.

回答2:

Postgresql doesn't support updating multiple columns using the set (col1,col2) = select val1,val2 syntax.

To achieve the same in postgresql you'd use

update target_table
set col1 = d.val1,
    col2 = d.val2
from source_table d
where d.id = target_table.id

This is going to make the dynamic query a bit more complex to build as you'll need to iterate the column name list you're using into individual fields. I'd suggest you use array_agg instead of string_agg as an array is easier to process than splitting the string again.

Postgresql UPDATE syntax

documentation on array_agg function