更新plpgsql中触发器函数中的多列

给出以下架构：

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;

我尝试创建一个genericplpgsql 中的触发器函数，可以更新视图：

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();

我的一个失败的尝试是：

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;

问题是update陈述。我无法想出在这里适用的语法。我已经在 PL/Perl 中成功实现了这一点，但对仅 plpgsql 的解决方案感兴趣。
有任何想法吗？

Update

正如 @Erwin Brandstetter 所建议的，这里是我的 PL/Perl 解决方案的代码。我采纳了他的一些建议。

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;

While @Gary的回答 https://stackoverflow.com/a/15343288/939860从技术上讲是正确的，但它没有提到 PostgreSQLdoes支持这种形式：

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

Read 手册上UPDATE https://www.postgresql.org/docs/current/sql-update.html.

使用动态 SQL 来完成此任务仍然很棘手。我将假设一个简单的情况，其中视图由与其基础表相同的列组成。

CREATE VIEW tbl_view AS SELECT * FROM tbl;

Problems

特别记录NEW里面不可见EXECUTE https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN。我通过NEW作为单个参数USING的条款EXECUTE.

正如所讨论的，UPDATE以列表形式需要个人values。我使用子选择将记录拆分为单独的列：

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

（括号内$1不是可选的。）这使我可以简单地使用使用以下命令构建的两个列列表string_agg()从目录表来看：一种具有表资格，一种不具有表资格。

无法将行值作为一个整体分配给各个列。手册： https://www.postgresql.org/docs/current/sql-update.html

根据标准，括号中的源值 目标列名称的子列表可以是任何行值表达式 产生正确的列数。 PostgreSQL 只允许 源值是行构造函数或子构造函数SELECT.

INSERT实现起来更简单。如果视图和表的结构相同，我们可以省略列定义列表。 （可以改进，见下文。）

Solution

我对你的方法做了一些更新，以使其更加出色。

触发功能为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$;

触发功能为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$;

触发器：

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();

在 Postgres 11 之前，语法（奇怪）是EXECUTE PROCEDURE代替EXECUTE FUNCTION https://dba.stackexchange.com/a/307500/3684- 这也仍然有效。

db<>fiddle - demonstrating INSERT and UPDATE
_{Old sqlfiddle http://www.sqlfiddle.com/#!17/4a3a6/1}

主要观点

• 包含架构名称以使表引用明确。一个数据库中可以有多个具有多个模式的同名表！

• Query pg_catalog.pg_attribute代替information_schema.columns。可移植性较差，但速度更快，并且允许使用表 OID。

  • 如何检查给定模式中是否存在表 https://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema/24089729#24089729

• 表名不安全SQLi https://en.wikipedia.org/wiki/Sql_injection当连接为动态 SQL 的字符串时。逃脱与quote_ident() or format() https://www.postgresql.org/docs/current/functions-string.html或与对象标识符类型 https://www.postgresql.org/docs/current/datatype-oid.html。这包括特殊触发函数变量TG_TABLE_SCHEMA and TG_TABLE_NAME https://www.postgresql.org/docs/current/plpgsql-trigger.html!

• 投射到对象标识符类型regclass https://www.postgresql.org/docs/current/datatype-oid.html断言表名有效并获取用于目录查找的 OID。

• 可选择使用format() https://www.postgresql.org/docs/current/functions-string.html#FUNCTIONS-STRING-FORMAT安全地构建动态查询字符串。

• 对目录表的第一个查询不需要动态 SQL。更快、更简单。

• Use RETURN NEW代替RETURN NULL在这些触发函数中，除非您知道自己在做什么。 (NULL将取消INSERT对于当前行。）

• 这个简单的版本假设每个表（和视图）都有一个名为id。更复杂的版本可能会动态使用主键。

• 该函数为UPDATE允许视图和表的列位于任何订单，只要集合是相同的。 该函数为INSERT期望视图和表的列位于相同的订单。如果要允许任意顺序，请将列定义列表添加到INSERT命令，就像UPDATE.

• 更新版本还涵盖了以下更改id列通过使用OLD此外。