sql - 在触发器函数中访问行类型的动态列名

Question

我正在尝试创建一个用于设置触发器的动态函数。

CREATE OR REPLACE FUNCTION device_bid_modifiers_count_per()
  RETURNS TRIGGER AS
$$
  DECLARE
    devices_count INTEGER;
    table_name    regclass := TG_ARGV[0];
    column_name   VARCHAR  := TG_ARGV[1];
  BEGIN
    LOCK TABLE device_types IN EXCLUSIVE MODE;
    EXECUTE format('LOCK TABLE %s IN EXCLUSIVE MODE', table_name);

    SELECT INTO devices_count device_types_count();

    IF TG_OP = 'DELETE' THEN
      SELECT format(
        'PERFORM validate_bid_modifiers_count(%s, %s, OLD.%s, %s)',
        table_name,
        column_name,
        column_name,
        devices_count
      );
    ELSE
      SELECT format(
        'PERFORM validate_bid_modifiers_count(%s, %s, NEW.%s, %s)',
        table_name,
        column_name,
        column_name,
        devices_count
      );
    END IF;
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

我的问题是动态函数 validate_bid_modifiers_count() 的执行。目前它抛出:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function device_bid_modifiers_count_per() line 21 at SQL statement

我实在想不通。我知道 format() 返回带参数的函数调用的正确字符串。我该如何解决这个问题并让它发挥作用？

Answer 1

应该这样做:

CREATE OR REPLACE FUNCTION device_bid_modifiers_count_per()
  RETURNS TRIGGER AS
$func$
DECLARE
   devices_count int      := device_types_count();
   table_name    regclass := TG_ARGV[0];
   column_name   text     := TG_ARGV[1];
BEGIN
   LOCK TABLE device_types IN EXCLUSIVE MODE;
   EXECUTE format('LOCK TABLE %s IN EXCLUSIVE MODE', table_name);

   IF TG_OP = 'DELETE' THEN
      PERFORM validate_bid_modifiers_count(table_name
                                         , column_name
                                         , <b>(row_to_json(OLD) ->> column_name)::bigint</b>
                                         , devices_count);
   ELSE
      PERFORM validate_bid_modifiers_count(table_name
                                         , column_name
                                         , <b>(row_to_json(NEW) ->> column_name)::bigint</b>
                                         , devices_count);
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

错误消息的直接原因是外部 SELECT。没有target，需要在plpgsql中用PERFORM替换。但是传递给 EXECUTE 的查询字符串中的内部 PERFORM 也是错误的。 PERFORM 是一个 plpgsql 命令，在传递给 EXECUTE 的 SQL 字符串中无效，它需要 SQL 代码。你必须在那里使用 SELECT。最后，OLD 和 NEW 在 EXECUTE 中是不可见的，并且每个都会按照您的方式引发它们自己的异常。所有问题都通过删除 EXECUTE 来解决。

从行类型OLD 和NEW 中获取动态列名 值的一种简单快速的方法:转换为json，然后你可以像演示的那样参数化键名。应该比使用动态 SQL 的替代方案更简单、更快——这也是可能的，例如:

  ...
  EXECUTE format('SELECT validate_bid_modifiers_count(table_name
                                                    , column_name
                                                    , <b>($1.%I)::bigint</b>
                                                    , devices_count)', <b>column_name</b>)
  <b>USING OLD</b>;
  ...

相关:

• Get values from varying columns in a generic trigger
• Trigger with dynamic field name

旁白:不确定为什么需要重锁。

旁白 2:考虑为每个触发器编写一个单独的触发器函数。更多嘈杂的 DDL，但执行起来更简单、更快。