为 PostgreSQL 中的记录变量动态传递列名

Passing column names dynamically for a record variable in PostgreSQL

提问by user2664380

Using PostgreSQL, column values from a table for 1st record are stored in a record variable. for ex: let the variable be: recordvar

使用 PostgreSQL，第一条记录的表中的列值存储在记录变量中。例如：让变量为：recordvar

recordvar.columnname

gives the value of the column name specified. I will define the columnamein a variable:

给出指定列名的值。我将columname在一个变量中定义：

var := columnname

In place of columnnameif I replace with the variable i.e. recordvar.var, it is not working.

代替columnname如果我用变量 ie 替换recordvar.var，它不起作用。

Please let me know how to proceed in this situation. Following is the sample code:

请让我知道在这种情况下如何进行。以下是示例代码：

CREATE OR REPLACE FUNCTION getrowdata(id numeric, table_name character varying)
RETURNS SETOF void AS
$BODY$ 
DECLARE

srowdata record;
reqfield character varying;
value numeric;


BEGIN

RAISE NOTICE 'id: %',id; 
reqfield:= 'columnname';

EXECUTE 'select * from datas.'||table_name||' WHERE id = '||id into srowdata;

RAISE NOTICE 'srowdata: %',srowdata; 

RAISE NOTICE 'srowdatadata.columnname: %',srowdata.columnname;

value:= srowdata.reqfield;

RAISE NOTICE 'value: %',value;


END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100
ROWS 1000;

回答by Erwin Brandstetter

Working with this dummy table

使用这个虚拟表

CREATE TEMP TABLE foo (id int, my_num numeric);
INSERT INTO foo VALUES (1, 12.34)

First, I simplified and sanitized your example:

首先，我简化并清理了您的示例：

• Removed some noise that is irrelevant to the question.

• RETURNS SETOF voidhardly makes sense. I use RETURNS voidinstead.

• I use textinstead of character varying, just for the sake of simplicity.

• When using dynamic SQL, you haveto safeguard against SQL injection, I use format()with %Iin this case. There are other ways.

• 删除了一些与问题无关的噪音。

• RETURNS SETOF void几乎没有意义。我用RETURNS void代替。

• 我使用text代替character varying，只是为了简单起见。

• 使用动态 SQL 时，您必须防止 SQL 注入，我在这种情况下使用format()with %I。还有其他方法。

The basic problem is that SQL is very rigid with types and identifiers. You are operating with dynamic tablename as well as with dynamic field name of a record- an anonymousrecordin your original example. Pl/pgSQL is not well equipped to deal with this. Postgres does not know what's insidean anonymous record. Only after you assign the record to a well known typecan you reference individual fields.
Here is a closely related question, trying to seta field of a record with dynamic name:
How to set value of composite variable field using dynamic SQL

基本问题是 SQL 对类型和标识符非常严格。您正在使用动态表名以及记录的动态字段名进行操作-在原始示例中为匿名记录。Pl/pgSQL 不能很好地处理这个问题。Postgres的不知道什么是内部匿名记录。只有在将记录分配给已知类型后，您才能引用单个字段。
这里是一个密切相关的问题，试图建立与动态名称记录的字段：
使用动态SQL如何变化的复合场的设定值

Basic function

基本功能

CREATE OR REPLACE FUNCTION getrowdata1(table_name text, id int)
  RETURNS void AS
$func$ 
DECLARE
   srowdata record;
   reqfield text := 'my_num';   -- assigning at declaration time for convenience
   value    numeric;
BEGIN

RAISE NOTICE 'id: %', id; 

EXECUTE format('SELECT * FROM %I WHERE id = ', table_name)
USING  id
INTO   srowdata;

RAISE NOTICE 'srowdata: %', srowdata;

RAISE NOTICE 'srowdatadata.my_num: %', srowdata.my_num;

/* This does not work, even with dynamic SQL
EXECUTE format('SELECT ().%I', reqfield)
USING srowdata
INTO value;

RAISE NOTICE 'value: %', value;
*/

END
$func$ LANGUAGE plpgsql;

Call:

称呼：

SELECT * from getrowdata1('foo', 1);

The commented part would raise an exception:

注释部分将引发异常：

could not identify column "my_num" in record data type: SELECT * from getrowdata(1,'foo')

无法识别记录数据类型中的列“my_num”：SELECT * from getrowdata(1,'foo')

hstore

hstore

You need to install the additional module hstorefor this. Once per database with:

您需要为此安装附加模块hstore。每个数据库一次：

CREATE EXTENSION hstore;

Then all could work like this:

然后一切都可以这样工作：

CREATE OR REPLACE FUNCTION getrowdata2(table_name text, id int)
  RETURNS void AS
$func$ 
DECLARE
   hstoredata hstore;
   reqfield   text := 'my_num';
   value      numeric;
BEGIN

RAISE NOTICE 'id: %', id; 

EXECUTE format('SELECT hstore(t) FROM %I t WHERE id = ', table_name)
USING  id
INTO   hstoredata;

RAISE NOTICE 'hstoredata: %', hstoredata;

RAISE NOTICE 'hstoredata.my_num: %', hstoredata -> 'my_num';

value := hstoredata -> reqfield;

RAISE NOTICE 'value: %', value;

END
$func$ LANGUAGE plpgsql;

Call:

称呼：

SELECT * from getrowdata2('foo', 1);

Polymorphic type

多态型

Alternative without installing additional modules.

无需安装额外模块的替代方案。

Since you select a whole row into your record variable, there is a well defined typefor it per definition. Use it. The key word is polymorphic types.

由于您在记录变量中选择了一整行，因此每个定义都有一个明确定义的类型。用它。关键词是多态类型。

CREATE OR REPLACE FUNCTION getrowdata3(_tbl anyelement, id int)
  RETURNS void AS
$func$ 
DECLARE
   reqfield text := 'my_num';
   value    numeric;
BEGIN

RAISE NOTICE 'id: %', id; 

EXECUTE format('SELECT * FROM %s WHERE id = ', pg_typeof(_tbl))
USING  id
INTO   _tbl;

RAISE NOTICE '_tbl: %', _tbl;

RAISE NOTICE '_tbl.my_num: %', _tbl.my_num;

EXECUTE 'SELECT ().' || reqfield   -- requfield must be SQLi-safe or escape
USING _tbl
INTO  value;

RAISE NOTICE 'value: %', value;

END
$func$ LANGUAGE plpgsql;

Call:

称呼：

SELECT * from getrowdata3(NULL::foo, 1);

-> SQLfiddle

-> SQLfiddle

• I (ab-)use the input parameter _tblfor threepurposes here:

  • Provides the well defined typeof the record
  • Provides the nameof the table, automatically schema-qualified
  • Serves as variable.

• More explanation in this related answer (last chapter):
  Refactor a PL/pgSQL function to return the output of various SELECT queries

• 我 (ab-) 在这里将输入参数_tbl用于三个目的：

  • 提供明确定义的记录类型
  • 提供表的名称，自动模式限定
  • 作为变量。

• 此相关答案（最后一章）中的更多解释：
  重构 PL/pgSQL 函数以返回各种 SELECT 查询的输出