如何在触发器过程中使用动态sql设置复合类型列

提问于 2024-05-02T17:00:53+08:00

浏览次

2

我有一个触发器函数，当更新COLUMN A时由几个表调用，以便可以根据来自不同函数的值更新COLUMN B. （解释比实际更复杂） . 触发器函数接受col_a和col_b，因为它们对于不同的表是不同的 .

IF needs_updated THEN
    sql = format('($1).%2$s = dbo.foo(($1).%1$s); ', col_a, col_b);

    EXECUTE sql USING NEW;
END IF;

当我尝试运行上面的时，格式产生这个sql：

($1).NameText = dbo.foo(($1).Name);

当我使用USING执行SQL时，我期待这样的事情发生（在没有动态sql的情况下直接执行时有效）：

NEW.NameText = dbo.foo(NEW.Name);

相反，我得到：

[42601]错误：语法错误在“$ 1”或附近

如何动态更新记录/复合类型NEW上的列？

2 回答

0
这不起作用，因为 NEW.NameText = dbo.foo(NEW.Name); 不是正确的SQL查询 . 我想不出你可以动态更新NEW的变量属性的方式 . 我的建议是明确定义每个表的行为：
```
IF TG_TABLE_SCHEMA = 'my_schema' THEN
    IF TG_TABLE_NAME = 'my_table_1' THEN
        NEW.a1 = foo(NEW.b1);
    ELSE IF TG_TABLE_NAME = 'my_table_2' THEN
        NEW.a2 = foo(NEW.b2);
    ... etc ...
    END IF;
END IF;
```
回复于 2024-05-02T17:00:53+08:00
0
第一：这是plpgsql的巨大痛苦 . 因此，我最好的建议是在其他PL中执行此操作，例如plpythonu或plperl . 在其中任何一个中执行此操作都是微不足道的 . 即使您不想在另一个PL中执行整个触发器，您仍然可以执行以下操作：
```
v_new RECORD;
BEGIN
v_new := plperl_function(NEW, column_a...)
```
在plpgsql中执行此操作的关键是创建一个具有所需内容的CTE：
```
c_new_old CONSTANT text := format(
  'WITH
    NEW AS (SELECT (r).* FROM (SELECT ($1)::%1$s r) s)
    , OLD AS (SELECT (r).* FROM (SELECT ($2)::%1$s r) s
    '
  , TG_RELID::regclass
);
```
您还需要定义一个普通记录的v_new . 然后你可以这样做：
```
-- Replace 2nd field in NEW with a new value
sql := c_new_old || $$SELECT row(NEW.a, $3, NEW.c) FROM NEW$$
EXECUTE sql INTO v_new USING NEW, OLD, new_value;
```
回复于 2024-05-02T17:00:53+08:00

相关问题