database PostgreSQL - SQL 状态：42601 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16291944/
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
PostgreSQL - SQL state: 42601 syntax error
提问by Leandro
I would like to know how to use a dynamic query inside a function. I've tried lots of ways, however, when I try to compile my function a message SQL 42601 is displayed.
我想知道如何在函数内使用动态查询。我尝试了很多方法，但是，当我尝试编译我的函数时，会显示一条消息 SQL 42601。
The code that I use:
我使用的代码：
CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
WITH v_tb_person AS (return query execute sql)
select name, count(*) from v_tb_person where nome like '%a%' group by name
union
select name, count(*) from v_tb_person where gender = 1 group by name;
END
$$ LANGUAGE plpgsql;
Error message I receive:
我收到的错误消息：
ERROR: syntax error at or near "return"
LINE 5: WITH v_tb_person AS (return query execute sql)
I tried using:
我尝试使用：
WITH v_tb_person AS (execute sql)
WITH v_tb_person AS (query execute)
WITH v_tb_person AS (return query execute)
What is wrong? How can I solve this problem?
怎么了？我怎么解决这个问题？
Its a question related to PostgreSQL equivalent of Oracle “bulk collect”
这是一个与PostgreSQL 等效的 Oracle “批量收集”相关的问题
采纳答案by Erwin Brandstetter
Your function would work like this:
你的函数会像这样工作：
CREATE OR REPLACE FUNCTION prc_tst_bulk(sql text)
RETURNS TABLE (name text, rowcount integer) AS
$$
BEGIN
RETURN QUERY EXECUTE '
WITH v_tb_person AS (' || sql || $x$)
SELECT name, count(*)::int FROM v_tb_person WHERE nome LIKE '%a%' GROUP BY name
UNION
SELECT name, count(*)::int FROM v_tb_person WHERE gender = 1 GROUP BY name$x$;
END
$$ LANGUAGE plpgsql;
Call:
称呼：
SELECT * FROM prc_tst_bulk($$SELECT a AS name, b AS nome, c AS gender FROM tbl$$)
You cannot mix plain and dynamic SQL the way you tried to do it. The whole statement is either all dynamic or all plain SQL. So I am building one dynamic statement to make this work. You may be interested in the chapter about executing dynamic commands in the manual.
The aggregate function
count()returnsbigint, but you hadrowcountdefined asinteger, so you need an explicit cast::intto make this workI use dollar quotingto avoid quoting hell.
您不能按照您尝试的方式混合使用普通 SQL 和动态 SQL。整个语句要么全部是动态的，要么全部是纯 SQL。所以我正在构建一个动态声明来完成这项工作。您可能对手册中有关执行动态命令的章节感兴趣。
聚合函数
count()返回bigint，但您已将rowcount其定义为integer，因此您需要显式强制转换::int才能使此工作我使用美元引用来避免引用地狱。
However, is this supposed to be a honeypot for SQL injectionattacks or are you seriously going to use it? For your very private and secure use, it might be ok-ish - though I wouldn't even trust myself with a function like that. If there is any possible access for untrusted users, such a function is a loaded footgun. It's impossible to make this secure.
但是，这应该是SQL 注入攻击的蜜罐还是您真的要使用它？对于您非常私密和安全的使用，它可能没问题-尽管我什至不相信自己有这样的功能。如果不受信任的用户有任何可能的访问权限，那么这样的功能就是上膛的枪。不可能做到这一点。
Craig (a sworn enemy of SQL injection!) might get a light stroke, when he sees what you forged from his piece of code in the answer to your preceding question. :)
克雷格（SQL 注入的死敌！）当他看到您在前面问题的答案中从他的一段代码中伪造的内容时，他可能会轻描淡写。:)
The query itself seems rather odd, btw. But that's beside the point here.
查询本身似乎很奇怪，顺便说一句。但这不是重点。
