postgresql PL/pgSQL 中的 EXECUTE...USING 语句不适用于记录类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2104811/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
EXECUTE...USING statement in PL/pgSQL doesn't work with record type?
提问by Hobbes
I'm trying to write a function in PL/PgSQL that have to work with a table it receives as a parameter.
我正在尝试在 PL/PgSQL 中编写一个函数，该函数必须使用它作为参数接收的表。
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.
我在函数定义中使用 EXECUTE..INTO..USING 语句来构建动态查询（这是我知道的唯一方法）但是......我遇到了 RECORD 数据类型的问题。
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 ()' || '.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');
It output the following error:
它输出以下错误：
ERROR: could not identify column "descr" in record data type
错误：无法识别记录数据类型中的列“descr”
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?
所以..有人知道上面的代码有什么问题吗？
回答by Pavel Stehule
It's true. You cannot to use type record outside PL/pgSQL space.
这是真的。您不能在 PL/pgSQL 空间之外使用类型记录。
RECORD value is valid only in plpgsql.
RECORD 值仅在 plpgsql 中有效。
you can do
你可以做
EXECUTE 'SELECT .descr' INTO d USING r::text::xx;
回答by Chinmay R Ullur
$1should be inside the ||,like || $1 ||and give spaces properly then it will work.
$1应该在||,like里面|| $1 ||并适当地给空间然后它会工作。
BEGIN
EXECUTE ' delete from ' || quote_ident() || ' where condition ';
END;
