sql - 在 plpgsql 的触发函数中更新多列

Question

给定以下架构:

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;

我尝试在 plpgsql 中创建一个通用触发器函数，它可以更新 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();

我的一次失败尝试是:

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-only 解决方案感兴趣。
有什么想法吗？

更新

正如@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;

Answer 1

同时 @Gary's answer在技​​术上是正确的，它没有提到 PostgreSQL 确实支持这种形式:

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

阅读the manual on UPDATE .

用动态 SQL 完成这件事仍然很棘手。我将假设一个简单的情况，其中 View 由与其基础表相同的列组成。

CREATE VIEW tbl_view AS SELECT * FROM tbl;

问题

特殊记录 NEW 在 EXECUTE 中不可见.我将 NEW 作为单个参数与 EXECUTE 的 USING 子句一起传递。

如前所述，列表形式的UPDATE需要单独的值。我使用子选择将记录拆分为单独的列:

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

($1 周围的括号不是可选的。)这让我可以简单地使用来自目录表的 string_agg() 构建的两个列列表:一个有，一个没有表格资格。

不可能将行值作为一个整体分配给各个列。 The manual:

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

INSERT 的实现更简单。如果 View 和表的结构相同，我们可以省略列定义列表。 (可以改进，见下文。)

解决方案

我对您的方法进行了一些更新以使其大放异彩。

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 instead of EXECUTE FUNCTION - 这仍然有效。

db<> fiddle here - 演示 INSERT 和 UPDATE
<子>旧sqlfiddle

要点

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

• 查询 pg_catalog.pg_attribute 而不是 information_schema.columns。便携性较差，但速度更快，并且允许使用表 OID。

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

• 表名对于 SQLi 是不安全的 当连接为动态 SQL 的字符串时。使用 quote_ident() or format() 转义或者使用 object-identifer type .这包括 special trigger function variables TG_TABLE_SCHEMA and TG_TABLE_NAME !

• 转换到 object identifier type regclass断言表名有效并获取用于目录查找的 OID。

• 可选择使用 format()安全地构建动态查询字符串。

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

• 除非您知道自己在做什么，否则在这些触发器函数中使用RETURN NEW 而不是RETURN NULL。 (NULL 将取消当前行的 INSERT。)

• 这个简单版本假定每个表(和 View )都有一个名为 id 的唯一列。更复杂的版本可能会动态使用主键。

• UPDATE 函数允许 View 和表的列任何顺序，只要集合相同即可。INSERT 函数要求 View 和表的列相同的顺序。如果要允许任意顺序，请将列定义列表添加到 INSERT 命令，就像使用 UPDATE 一样。

• 更新版本还包括通过额外使用 OLD 对 id 列的更改。