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

发布时间：2020-12-13 16:36:11 所属栏目：百科来源：网络整理

导读：使用PostgreSQL，第一条记录的表中的列值存储在记录变量中。例如：让变量为：recordvar recordvar.columnname 给出指定列名的值。我将在变量中定义列名： var := columnname 如果我用变量即recordvar.var替换代替列名，则它不起作用。请让我知道如何在这种

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

recordvar.columnname

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

var := columnname

如果我用变量即recordvar.var替换代替列名，则它不起作用。

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

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)

首先，我简化和清理了你的例子：

删除了一些与问题无关的噪音。
返回设置无效几乎没有意义。我使用RETURNS void。
>我使用文字而不是字符变化，只是为了简单起见。
>当使用动态SQL时，必须防止SQL注入，在这种情况下，我使用format()与％I。 There are other ways。

基本的问题是SQL与类型和标识符非常僵化。您正在使用动态表名称以及记录的动态字段名称 – 您的原始示例中的匿名记录。 Pl / pgSQL没有办法处理这个。 Postgres不知道匿名记录里面是什么。只有在将记录分配给众所周知的类型后，才能引用单个字段。
这是一个密切相关的问题，尝试用动态名称设置一个记录的字段：
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);

评论的部分将引发例外：

could not identify column “my_num” in record data type: SELECT * from
getrowdata(1,’foo’)

hstore

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

CREATE EXTENSION hstore;

那么所有这些都可以这样工作：

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 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三个目的：

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

>这个相关答案的更多解释(最后一章)：
Refactor a PL/pgSQL function to return the output of various SELECT queries

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!