gpt4 book ai didi

postgresql - 从函数执行字符串查询

转载 作者:行者123 更新时间:2023-11-29 13:22:25 24 4
gpt4 key购买 nike

我有一个名为 rezultz 的表,其中 1 行仅包含有效的 SQL 查询,例如:CREATE TRIGGER ... ?

       CREATE OR REPLACE FUNCTION get_all_rezultz() RETURNS SETOF rezultz AS
$BODY$
DECLARE
r rezultz%rowtype;
BEGIN
FOR r IN
SELECT * FROM rezultz
LOOP
-- can do some processing here
RETURN QUERY EXECUTE r; -- return current row of SELECT
END LOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_rezultz();

这是我得到的错误:

  NOTICE:  identifier "CREATE TRIGGER userman_if_modified_trg AFTER    INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); " will be truncated to "CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE O"
CONTEXT: SQL statement "("CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ")"

PL/pgSQL 函数 get_all_rezultz() 第 10 行返回查询

    ERROR:  syntax error at or near ""CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ""
LINE 1: ("CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPD...
^
QUERY: ("CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ")
CONTEXT: PL/pgSQL function get_all_rezultz() line 10 at RETURN QUERY

********** 错误 **********

   ERROR: syntax error at or near ""CREATE TRIGGER userman_if_modified_trg AFTER INSERT OR UPDATE OR DELETE ON userman          FOR EACH ROW EXECUTE PROCEDURE audit.if_modified_func(); ""
SQL state: 42601
Context: PL/pgSQL function get_all_rezultz() line 10 at RETURN QUERY

最佳答案

变量r 是一个包含多列的记录。它不是标量值(例如字符串)。

所以需要在execute语句中使用列名。假设表中的列名为 sql_statement,您需要使用:

RETURN QUERY EXECUTE r.sql_statement;

但是,这仍然不起作用,因为存储在该表中的 select 语句绝对不会返回 SETOF rezultz 的结果,如果查询是 select * from mytable

您需要指定RETURNS SETOF 记录,但随后您需要在调用 函数时指定结果的列名和结构。

即便如此,这也行不通,因为一个函数仍然只返回一个结果,而不是来自多个查询的多个结果 - 如果 rezultz 包含不止一行,就会发生这种情况。

如果您的 SQL 语句实际上不是您声称的 SELECT 语句,您将需要使用 EXECUTE 而不能使用 RETURN QUERY 用于 CREATE TRIGGER 语句。

关于postgresql - 从函数执行字符串查询,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39244294/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com