动态访问记录中的列值 [英] Dynamically access column value in record

问题描述

是否可以通过记录的名称动态访问记录中的列值?

Is it possible to dynamically access a column value from a record by its name?

我正在编写一个执行动态SQL命令的触发器函数，我想通过列名从NEW记录中动态提取列值.

I'm writing a trigger function that executes a dynamic SQL command and I would like to dynamically extract a column value from a NEW record by column name.

这是我要执行的操作的简化示例:

Here's a simplified example of what I'm trying to do:

$$
DECLARE
   command text := 'UPDATE $1 SET $2 = $3';
   myColumn := 'votes'
BEGIN
   EXECUTE command using 'anotherTable', myColumn, NEW.myColumn;
END
$$

推荐答案

这是可能，但是EXECUTE的USING子句只能传递 值 .
在执行命令之前，必须将 标识符 (如表名和列名)进行串联(请警惕SQL注入！).使用 format() > (Postgres 9.1+)，它可以像这样工作:

That's possible, but the USING clause of EXECUTE can only pass values.
While identifiers like table and column names must be concatenated (be wary of SQL injection!) before executing the command. Using format() (Postgres 9.1+) it could work like this:

$$
DECLARE
   _command text := 'UPDATE %I SET %I = $1 WHERE ....'; -- add WHERE condition
   _col text := 'votes';
BEGIN
   EXECUTE format(_command, 'anotherTable', _col)
   USING  NEW.myColumn;
END
$$;

解决了传递中的一些小问题.

Fixed a couple of minor problems in passing.

必须指出的是，NEW仅在触发功能中可用.

It must be mentioned that NEW is only available in trigger functions.

请注意，此处'anotherTable'是 区分大小写 的(正在转换并从 string 转义)，而NEW.myColumn不是(作为标识符处理).在Postgres中使用合法的，小写的，未加引号的标识符可以使您的生活更轻松.

Be aware that 'anotherTable' is case sensitive here (being converted and escaped from a string), while NEW.myColumn is not (processed as identifier). Use legal, lower case, unquoted identifiers in Postgres to make your life easier.

相关答案以及更多说明和链接:

Related answers with more explanation and links:

• PL/pgSQL:更新N的一般方法触发器中的列?
• 在触发函数中插入具有动态表名称的
• 表名作为PostgreSQL函数参数
• PostgreSQL列名是否区分大小写?

• PL/pgSQL: General Way to Update N Columns in Trigger?
• INSERT with dynamic table name in trigger function
• Table name as a PostgreSQL function parameter
• Are PostgreSQL column names case-sensitive?

要通过列名称从NEW记录中动态提取列值.

To dynamically extract a column value from a NEW record by column name.

......，您可以使用 hstore #= 运算符:

... you can use the hstore #= operator:

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

或者您也可以使其与动态SQL的标准功能一起使用:

Or you can make it work with standard features of dynamic SQL as well:

$$
DECLARE
   _col text := 'votes';
   _new_col text := 'column_name_in_new';  -- enter column name here, case sensitive
BEGIN
   EXECUTE format(
       'UPDATE %I SET %I = $1.%I WHERE ... '  -- add WHERE condition
     , 'anotherTable', _col, _new_col)
   USING  NEW;  -- pass whole row
END
$$;

相关:

• 为孩子创建触发器表插入返回令人困惑的错误

这篇关于动态访问记录中的列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！