在 plpgsql 中更新触发器函数中的多列 [英] Update multiple columns in a trigger function in 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;

我尝试在 plpgsql 中创建一个通用触发器函数，它可以更新视图:

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;

问题在于 update 语句.我无法想出一种可以在这里工作的语法.我已经在 PL/Perl 中成功实现了这一点，但对仅 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?

更新

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

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;

推荐答案

虽然 @Gary 的回答在技术上是正确的，但他没有提到 PostgreSQL 确实支持这种形式:

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

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

阅读关于UPDATE的手册 再一次.

Read the manual on UPDATE once more.

用动态 SQL 完成他的工作仍然很棘手.由于您没有指定，我假设一个简单的情况，其中视图包含与其基础表相同的列.

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;

问题

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

  Problems

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

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

    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
    

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

    (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:

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

    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 实现起来更简单.假设视图和表的结构相同，我省略了列定义列表.(可以改进，见下文.)

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

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

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

    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;
    

    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;
    

    触发器:

    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 演示 INSERT 和 UPDATE.

    • 包括模式名称以使表引用明确.同一个数据库中的多个schema中可以有多个同一个表名的实例！

    • 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!

    查询 pg_attribute 而不是 information_schema.columns.这不太便携，但快得多，并且允许我使用 table-OID.

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

    • 如何检查如果给定模式中存在表

    表名对 SQLi 来说是不安全的，当像在为动态 SQL 构建查询一样作为字符串处理.使用 quote_ident() 或 格式转义() 或带有 object-identifer 类型.这包括 特殊触发函数变量 TG_TABLE_SCHEMA 和 <代码>TG_TABLE_NAME！

    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!

    转换为对象标识符类型regclass 断言表名有效并获取用于目录查找的 OID.

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

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

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

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

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

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

    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.)

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

    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.

    UPDATE 的函数允许视图和表的列以任意顺序，只要集合相同即可.INSERT 的函数要求视图和表的列具有相同的顺序.如果要允许任意顺序，请在 INSERT 命令中添加列定义列表，就像使用 UPDATE 一样.

    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.

    更新版本还通过额外使用 OLD 来涵盖对 id 列的更改.

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

    这篇关于在 plpgsql 中更新触发器函数中的多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！