为 PostgreSQL 中的记录变量动态传递列名 [英] Passing column names dynamically for a record variable in PostgreSQL

问题描述

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

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

recordvar.columnname

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

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

var := columnname

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

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

请告诉我在这种情况下如何进行.以下是示例代码:

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;

推荐答案

使用这个虚拟表

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 void 几乎没有意义.我使用 RETURNS void 代替.

RETURNS SETOF void hardly makes sense. I use RETURNS void instead.

为了简单起见，我使用 text 而不是 character variables.

I use text instead of character varying, just for the sake of simplicity.

当使用动态 SQL 时，你必须来防止 SQL 注入，我在这里使用 format() 和 %I案件.还有其他方法.

When using dynamic SQL, you have to safeguard against SQL injection, I use format() with %I in this case. There are other ways.

基本问题是 SQL 对类型和标识符非常严格.您正在使用动态表名称以及记录的动态字段名称 - 一个匿名记录你原来的例子.Pl/pgSQL 不能很好地处理这个问题.Postgres 不知道匿名记录里面是什么.只有在将记录分配给众所周知的类型之后，您才能引用单个字段.
这是一个密切相关的问题，试图设置一个具有动态名称的记录字段:
如何使用动态设置复合变量字段的值SQL

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

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 = $1', 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 ($1).%I', reqfield)
USING srowdata
INTO value;

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

END
$func$ LANGUAGE plpgsql;

调用:

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

注释部分会引发异常:

无法识别记录数据类型中的列my_num":SELECT * fromgetrowdata(1,'foo')

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

hstore

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

hstore

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

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 = $1', 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;

调用:

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 type for 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 = $1', pg_typeof(_tbl))
USING  id
INTO   _tbl;

RAISE NOTICE '_tbl: %', _tbl;

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

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

RAISE NOTICE 'value: %', value;

END
$func$ LANGUAGE plpgsql;

调用:

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

-> SQLfiddle

• 我(ab-)将输入参数 _tbl 用于 三个 目的:

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

在这个相关答案中有更多解释(最后一章):
重构一个PL/pgSQL 函数返回各种 SELECT 查询的输出

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

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