使用这个虚拟表进行工作
CREATE TEMP TABLE foo (id int, my_num numeric);
INSERT INTO foo VALUES (1, 12.34)
首先,我精简并清理了您的示例:
去除了与问题无关的一些干扰。
RETURNS SETOF void
几乎没有意义。我使用 RETURNS void
替代。
为了简单起见,我使用 text
代替了 character varying
。
使用动态 SQL 时,您必须防范 SQL 注入攻击,我在这种情况下使用了 format()
和 %I
。还有其他方法。
基本问题是 SQL 的类型和标识符非常严格。您正在操作 动态表名 和 记录的动态字段名,原始示例中的一个匿名记录。Pl/pgSQL 不擅长处理此类情况。Postgres 不知道匿名记录的内容。只有将记录分配给已知类型后,才能引用单个字段。
这里有一个相关的问题,试图使用动态名称设置记录的字段:
如何使用动态SQL设置复合变量字段的值
基本函数
CREATE OR REPLACE FUNCTION getrowdata1(table_name text, id int)
RETURNS void AS
$func$
DECLARE
srowdata record;
reqfield text := 'my_num';
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;
END
$func$ LANGUAGE plpgsql;
呼叫:
SELECT * from getrowdata1('foo', 1);
以下是翻译的结果:
注释部分会引发异常:
无法在记录数据类型中识别“my_num”列: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);
多态类型
无需安装其他模块即可使用替代方法。
由于您将整行选择到记录变量中,因此根据定义,它具有明确定义的类型。 使用它。 关键字是多态类型。
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
USING _tbl
INTO value;
RAISE NOTICE 'value: %', value;
END
$func$ LANGUAGE plpgsql;
呼叫:
SELECT * from getrowdata3(NULL::foo, 1);
-> SQLfiddle