在触发功能中访问行类型的动态列名称 [英] Access dynamic column name of row type in trigger function
问题描述
我正在尝试创建一个动态函数以用于设置触发器.
I am trying to create a dynamic function to use for setting up triggers.
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()的执行.当前它抛出:
My issue is with the execution of the dynamic function validate_bid_modifiers_count(). Currently it throws:
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()返回带有参数的函数调用的正确字符串.如何解决并使其正常工作?
I can't really wrap my head around this. I understand that format() returns the correct string of function call with arguments. How do I fix this and make it work?
推荐答案
这应该做到:
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来解决.
The immediate cause for the error message was the outer SELECT. Without target, you need to replace it with PERFORM in plpgsql. But the inner PERFORM in the query string passed to EXECUTE was wrong, too. PERFORM is a plpgsql command, not valid in an SQL string passed to EXECUTE, which expects SQL code. You have to use SELECT there. Finally OLD and NEW are not visible inside EXECUTE and would each raise an exception of their own the way you had it. All issues are fixed by dropping EXECUTE.
从行类型OLD和NEW获取动态列名的简单快捷方法:强制转换为json，然后可以像这样对键名进行参数化演示.应该比动态SQL的替代方法更简单，更快捷-这也是可能的，例如:
A simple and fast way to get the value of a dynamic column name from the row types OLD and NEW: cast to json, then you can parameterize the key name like demonstrated. Should be a bit simpler and faster than the alternative with dynamic SQL - which is possible as well, like:
...
EXECUTE format('SELECT validate_bid_modifiers_count(table_name
, column_name
, ($1.%I)::bigint
, devices_count)', column_name)
USING OLD;
...相关:
- Get values from varying columns in a generic trigger
- Trigger with dynamic field name
在旁边:不确定为什么需要沉重的锁.
Aside: Not sure why you need the heavy locks.
除了2:考虑为每个触发器编写一个单独的触发器函数.嘈杂的DDL，但执行起来更简单，更快捷.
Aside 2: Consider writing a separate trigger function for each trigger instead. More noisy DDL, but simpler and faster to execute.
这篇关于在触发功能中访问行类型的动态列名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
