访问动态列名称触发功能中的行类型

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
                                         , (row_to_json(OLD) ->> column_name)::bigint
                                         , devices_count);
   ELSE
      PERFORM validate_bid_modifiers_count(table_name
                                         , column_name
                                         , (row_to_json(NEW) ->> column_name)::bigint
                                         , devices_count);
   END IF;
   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

错误消息的直接原因是外部SELECT。没有目标，您需要在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
                                                    , ($1.%I)::bigint
                                                    , devices_count)', column_name)
  USING OLD;
  ...

相关：

• 从通用触发器中的不同列中获取值
• 带有动态字段名称的触发器

旁边：不确定为什么需要重锁。

旁边2：考虑为每个触发器编写单独的触发功能。更多嘈杂的DDL，但执行更简单，更快。

Answer 2

正如我在给Erwin Brandstetter的评论中指出的那样，我最初有一个几乎相同的解决方案。

但是问题是我正在遇到错误

ERROR: record "new" has no field "column_name"
CONTEXT: SQL statement "SELECT validate_bid_modifiers_count(table_name, column_name, NEW.column_name, devices_count)"
PL/pgSQL function device_bid_modifiers_count_per() line 15 at PERFORM

这就是为什么我认为我需要一种动态评估事物的方法。

目前与以下有关的工作仍然很丑陋，请寻找我的解决方案（丑陋，因为我不喜欢2个IF语句，我希望它是超级动态的，但也许我要求太多）：

CREATE OR REPLACE FUNCTION device_bid_modifiers_count_per()
  RETURNS TRIGGER AS
$func$
  DECLARE
    row           RECORD;
    table_name    regclass := TG_ARGV[0];
    column_name   text := TG_ARGV[1];
    devices_count INTEGER;
  BEGIN
    LOCK TABLE device_types IN EXCLUSIVE MODE;
    EXECUTE format('LOCK TABLE %s IN EXCLUSIVE MODE', table_name);
    devices_count := device_types_count();
    IF TG_OP = 'DELETE' THEN
      row := OLD;
    ELSE
      row := NEW;
    END IF;
    IF column_name = 'campaign_id' THEN
      PERFORM validate_bid_modifiers_count(table_name, column_name, row.campaign_id, devices_count);
    ELSIF column_name = 'adgroup_id' THEN
      PERFORM validate_bid_modifiers_count(table_name, column_name, row.adgroup_id, devices_count);
    ELSE
      RAISE EXCEPTION 'invalid_column_name %', column_name;
    END IF;
    RETURN NEW;
  END;
$func$ LANGUAGE plpgsql;

我对更强大的解决方案建议开放。

基本上，第二种条件几乎打败了拥有单个功能的目的，此时我可以将其分为两个函数。因为目标是使用此功能定义多个（2）触发器（向其提供参数）。