postgresql - 如何在postgres中执行存储过程的字符串结果

Question

我创建了以下存储过程，它基本上接收表名和前缀。该函数然后查找共享此前缀的所有列，并作为输出返回一个“选择”查询命令(“myoneliner”)。如下:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS text AS $myoneliner$
declare
    myoneliner text;
BEGIN
   SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable 
   INTO myoneliner  
     FROM (
        SELECT array(
           SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = mytable
           AND    column_name LIKE myprefix||'%'
           order by quote_ident             
      )::text cols 
     ) sub;
   RETURN myoneliner;
END;
$myoneliner$ LANGUAGE plpgsql;

调用:

select mytext('dkj_p_k27ac','enri');

作为运行此存储过程及其后的“选择”的结果，我在“数据输出”窗口中得到以下输出(全部在一个单元格中，名为“mytext 文本”):

'SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
 FROM dkj_p_k27ac'

我希望基本上能够将收到的输出命令行作为输出并执行它。换句话说，我希望能够并执行我的存储过程的输出。我该怎么做？

我尝试了以下方法:

CREATE OR REPLACE FUNCTION mytext (mytable text, myprefix text)
RETURNS SETOF RECORD AS $$
declare
        smalltext text;
    myoneliner text;
BEGIN
   SELECT 'SELECT ' || substr(cols,2,length(cols)-2) ||' FROM '||mytable 
   INTO myoneliner  
     FROM (
        SELECT array(
           SELECT DISTINCT quote_ident(column_name::text)
           FROM   information_schema.columns
           WHERE  table_name = mytable
           AND    column_name LIKE myprefix||'%'
           order by quote_ident             
      )::text cols 
     ) sub;

   smalltext=lower(myoneliner);
   raise notice '%','my additional text '||smalltext;
   RETURN QUERY EXECUTE smalltext;
END;
$$ LANGUAGE plpgsql;

调用函数:

SELECT * from mytext('dkj_p_k27ac','enri');

但我收到以下错误消息，请问我应该更改什么才能执行它？:

ERROR:  a column definition list is required for functions returning "record"
LINE 26: SELECT * from mytext('dkj_p_k27ac','enri');

********** Error **********

ERROR: a column definition list is required for functions returning "record"
SQL state: 42601
Character: 728

Answer 1

您的第一个问题已通过使用带有 EXECUTE 的动态 SQL 解决，就像 Craig 所建议的那样。但兔子洞更深:

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
  RETURNS SETOF RECORD AS
$func$
DECLARE
   <strike>smalltext  text;</strike>
   myoneliner text;
BEGIN
   SELECT INTO myoneliner  
          'SELECT '
        || string_agg(quote_ident(column_name::text), ',' ORDER BY column_name)
        || ' FROM ' || quote_ident(mytable)
   FROM   information_schema.columns
   WHERE  table_name = mytable
   AND    column_name LIKE myprefix||'%'
   AND    table_schema = 'public';  -- schema name; might be another param

   <strike>smalltext := lower(myoneliner);</strike>  -- nonsense
   RAISE NOTICE 'My additional text: %', myoneliner;

   RETURN QUERY EXECUTE myoneliner;
END
$func$ LANGUAGE plpgsql;

要点

• 不要将整个语句转换为小写。列名可能用大写字母双引号引起来，在这种情况下区分大小写(没有双关语意)。

• 在 information_schema.columns 的查询中不需要 DISTINCT。列名每个表是唯一的。

• 不过，您确实需要指定架构(或使用另一种方式来挑出一个架构)，或者您可能会在多个模式中混合来自多个同名表的列名，从而导致无意义。

• 您必须清理动态代码中的所有 标识符 - 包括表名:quote_ident(mytable)。请注意，函数的文本参数区分大小写! information_schema.columns 上的查询也需要这样做。

• 我解开了您的整个构造，以使用 string_agg() 而不是数组构造函数来构建列名列表。相关回答:

  • Update multiple columns that start with a specific string

• The assignment operator in plpgsql is :=.

• RAISE NOTICE 的简化语法.

无法解决的核心问题

所有这些仍然没有解决您的主要问题:SQL 要求定义要返回的列。您可以通过像您尝试的那样返回匿名记录来规避这种情况。但这只是推迟了不可避免的事情。现在您必须在调用时提供一个列定义列表，就像您的错误消息告诉您的那样。但是您只是不知道要返回哪些列。第 22 条。

您的调用将像这样工作:

SELECT *
FROM   myresult('dkj_p_k27ac','enri') AS f (
  enrich_d_dkj_p_k27ac text  -- replace with actual column types
, enrich_lr_dkj_p_k27ac text
, enrich_r_dkj_p_k27ac text);

但是您不知道返回列的编号、名称(可选)和数据类型，在创建函数时不知道，甚至在调用时也不知道。在单个 调用中完全不可能做到这一点。您需要对数据库两个单独的查询。

您可以使用多态类型的函数动态返回任何给定表的所有列，因为有是整个表的定义明确的类型。此相关答案的最后一章:

• Refactor a PL/pgSQL function to return the output of various SELECT queries