在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
如果我用变量recordvar.var代替columnname，它将无法正常工作.
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 varying.
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);
被注释的部分将引发异常:
The commented part would raise an exception:
无法在记录数据类型中标识"my_num"列:SELECT * from getrowdata(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);
-
I(ab-)在这里将输入参数
_tbl用于三个用途:
- 提供记录的明确定义的类型
- 提供表的名称，并自动进行模式限定
- 用作变量.
- Provides the well defined type of the record
- Provides the name of the table, automatically schema-qualified
- Serves as variable.
此相关答案的更多解释(上一章):
重构a 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屋！
