在plpgsql中的触发器函数中更新多个列 [英] Update multiple columns in a trigger function in plpgsql

查看：105
本文介绍了在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在

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

      • 包括架构名称以使表引用明确.同一数据库中的多个模式中可能存在相同表名的多个实例！

      • 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.这种方法的可移植性较差，但是很多更快，并且允许我使用表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()或format() 或对象标识符类型.这包括特殊触发函数变量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.

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

      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屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆