PL/pgSQL中的EXECUTE ... USING语句不适用于记录类型吗? [英] EXECUTE...USING statement in PL/pgSQL doesn't work with record type?
问题描述
我正在尝试在PL/PgSQL中编写一个函数,该函数必须与它作为参数接收的表一起使用.
I'm trying to write a function in PL/PgSQL that have to work with a table it receives as a parameter.
我在函数定义中使用EXECUTE..INTO..USING语句来构建动态查询(这是我知道的唯一方法),但是...我遇到了RECORD数据类型问题.
I use EXECUTE..INTO..USING statements within the function definition to build dynamic queries (it's the only way I know to do this) but ... I encountered a problem with RECORD data types.
让我们考虑以下示例(极其简化).
Let's consider the follow (extremely simplified) example.
-- A table with some values.
DROP TABLE IF EXISTS table1;
CREATE TABLE table1 (
code INT,
descr TEXT
);
INSERT INTO table1 VALUES ('1','a');
INSERT INTO table1 VALUES ('2','b');
-- The function code.
DROP FUNCTION IF EXISTS foo (TEXT);
CREATE FUNCTION foo (tbl_name TEXT) RETURNS VOID AS $$
DECLARE
r RECORD;
d TEXT;
BEGIN
FOR r IN
EXECUTE 'SELECT * FROM ' || tbl_name
LOOP
--SELECT r.descr INTO d; --IT WORK
EXECUTE 'SELECT ($1)' || '.descr' INTO d USING r; --IT DOES NOT WORK
RAISE NOTICE '%', d;
END LOOP;
END;
$$ LANGUAGE plpgsql STRICT;
-- Call foo function on table1
SELECT foo('table1');
它输出以下错误:
错误:无法在记录数据类型中识别列"descr"
ERROR: could not identify column "descr" in record data type
尽管我使用的语法对我来说似乎是有效的.我无法使用静态选择(在示例中进行了注释),因为我想从名称上引用列名.
although the syntax I used seems valid to me. I can't use the static select (commented in the example) because I want to dinamically refer the columns names.
所以..有人知道上面的代码有什么问题吗?
So..someone know what's wrong with the above code?
推荐答案
是的.您不能在PL/pgSQL空间之外使用类型记录.
It's true. You cannot to use type record outside PL/pgSQL space.
RECORD值仅在plpgsql中有效.
RECORD value is valid only in plpgsql.
可以做到
EXECUTE 'SELECT $1.descr' INTO d USING r::text::xx;
这篇关于PL/pgSQL中的EXECUTE ... USING语句不适用于记录类型吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!