PostgreSQL变量列名

Question

我正在创建一个触发器，它为列使用动态名称

NEW.name:=2222; -- works fine !

但是

dynamic_column:='name';
EXECUTE '$1.'||dynamic_column||':=2222 ' USING NEW; -- raises error

给出错误：

错误："$1"行1:$1处或附近的语法错误。名称：=2222

Answer 1

我在这里找到了信息：通过Postgres触发器中的键分配给NEW

如果我们通过以下方式启用模块：

CREATE EXTENSION hstore;

我们可以做到：

dynamic_column:='name';    
temp_sql_string:='"'||dynamic_column||'"=>"2222"';
NEW := NEW #= temp_sql_string::hstore;

并且RECORD NEW.name现在被设置为值2222。

感谢你为寻找解决方案付出的努力@Laurenz Albe

Answer 2

问题是这不是一个有效的SQL语句。

您可以使用动态SQL访问new中的列，如下所示：

EXECUTE 'SELECT $1.id' INTO v_id USING NEW;

对于更改NEW中的各个列，没有像这样舒适的方法。

您可以使用TG_RELID来获取表的OID，查询pg_attribute中的列，组成一个由NEW中的值和新值组成的行文字字符串，将其强制转换为表类型并将结果分配给NEW。相当麻烦。

下面是这样做的示例代码(我测试过了，但可能还剩下一些错误)：

CREATE OR REPLACE FUNCTION dyntrig() RETURNS trigger
LANGUAGE plpgsql AS
$$DECLARE
colname text;
colval text;
newrow text := '';
fieldsep text := 'ROW(';
BEGIN
/* loop through the columns of the table */
FOR colname IN
SELECT attname
FROM pg_catalog.pg_attribute
WHERE attrelid = TG_RELID
AND attnum > 0
AND NOT attisdropped
ORDER BY attnum
LOOP
IF colname = 'name' THEN
colval = '2222';
ELSE
/* all other columns than 'name' retain their value */
EXECUTE 'SELECT CAST($1.' || quote_ident(colname) || ' AS text)'
INTO colval USING NEW;
END IF;
/* compose a string that represents the new table row */
IF colval IS NULL THEN
newrow := newrow || fieldsep || 'NULL';
ELSE
newrow := newrow || fieldsep || '''' || colval || '''';
END IF;
fieldsep := ',';
END LOOP;
newrow := newrow || ')';
/* assign the new table row to NEW */
EXECUTE 'SELECT (CAST(' || newrow || ' AS '
|| quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME)
|| ')).*'
INTO NEW;
RETURN NEW;
END;$$;

Answer 3

您已经找到我的答案，建议在dba上使用hstore运算符#=。SE。您可能也对SO:上的相应参考答案感兴趣

• 如何使用动态SQL设置复合变量字段的值

由于从变量构造辅助hstore值，我建议使用简单的函数hstore():

CREATE OR REPLACE FUNCTION dyn_trigger_func()
RETURNS TRIGGER AS
$func$
DECLARE
dyn_col_name text := 'name';
dyn_col_val  text := '2222';
BEGIN
NEW := NEW #= hstore(dyn_col_name, dyn_col_val);
RETURN NEW;
END
$func$  LANGUAGE plpgsql;

用这种方式更快/更简单/更清晰/更安全。

或者，由于它显然是一个触发器函数，可能希望在CREATE TRIGGER语句中传递列名和值：

CREATE OR REPLACE FUNCTION dyn_trigger_func()
RETURNS TRIGGER AS
$func$
BEGIN
NEW := NEW #= hstore(TG_ARGV[0], TG_ARGV[1]);
RETURN NEW;
END
$func$  LANGUAGE plpgsql;

和：

CREATE TRIGGER ins_bef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE dyn_trigger_func('name', '2222');

提供不带引号且区分大小写的列名。

相关：

• 从泛型触发器中的不同列中获取值
• 具有动态字段名称的触发器