从函数执行字符串查询

Question

我有一个名为rezultz的表，其中一行只包含有效的SQL查询，例如:创建触发器.我试图用一个名为get_all_rezultz()的函数来执行它们，但是它似乎不起作用，为什么呢？

       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

Answer 1

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

因此，您需要在execute语句中使用列名。假设表中的列名为sql_statement，则需要使用：

RETURN QUERY EXECUTE r.sql_statement;

但是，这仍然不能工作，因为存储在该表中的select语句肯定不会返回一个结果，即如果查询是SETOF rezultz，则返回一个SETOF mytable。

您需要指定RETURNS SETOF record，但是在调用函数时需要指定结果的列名和结构。

即使这样，这也不起作用，因为函数仍然只返回单个结果，而不是多个查询的多个结果--如果rezultz包含多个行，就会发生这种情况。

如果您的SQL语句实际上不是您所声称的SELECT语句，那么您将需要使用EXECUTE --您不能对CREATE TRIGGER语句使用RETURN QUERY。