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

How to execute a string result of a stored procedure in postgres

提问by Roy

I have created the following stored procedure, which basically receives a name of table, and a prefix. The function then finds all columns that share this prefix and returns as an output a 'select' query command ('myoneliner'). as follows:

我创建了以下存储过程，它基本上接收一个表名和一个前缀。然后，该函数查找共享此前缀的所有列，并将“选择”查询命令（“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;

Call:

称呼：

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

As a result of running this stored procedure and the 'select' that is following it, I get the following output at the Data Output window (all within one cell, named "mytext text"):

作为运行此存储过程和跟随它的“选择”的结果，我在“数据输出”窗口中得到以下输出（全部在一个单元格中，名为“mytext text”）：

'SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
 FROM dkj_p_k27ac'

I would like to basically be able to take the output command line that I received as an output and execute it. In other words, I would like to be able and execute the output of my stored procedure. How can I do so?

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

I tried the following:

我尝试了以下方法：

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;

Call function:

调用函数：

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

But I'm getting the following error message, could you please advise what should I change in order for it to execute?:

但是我收到以下错误消息，请您告知我应该更改什么才能执行它？：

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

回答by Erwin Brandstetter

Your first problem was solved by using dynamic SQL with EXECUTElike Craig advised. But the rabbit hole goes deeper:

您的第一个问题是通过使用EXECUTECraig 建议的动态 SQL 解决的。但兔子洞更深：

CREATE OR REPLACE FUNCTION myresult(mytable text, myprefix text)
  RETURNS SETOF RECORD AS
$func$
DECLARE
   smalltext  text;
   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

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

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

Major points

要点

• Don'tcast the whole statement to lower case. Column names might be double-quoted with upper case letters, which are case-sensitive in this case (no pun intended).

• You don't need DISTINCTin the query on information_schema.columns. Column names are unique per table.

• You doneed to specify the schema, though (or use another way to single out oneschema), or you might be mixing column names from multiple tables of the same name in multiple schemas, resulting in nonsense.

• You must sanitize allidentifiers in dynamic code - including table names: quote_ident(mytable). Be aware that your text parameter to the function is case sensitive! The query on information_schema.columnsrequires that, too.

• I untangled your whole construct to build the list of column names with string_agg()instead of the array constructor. Related answer:

  • Update multiple columns that start with a specific string

• The assignment operator in plpgsql is :=.

• Simplified syntax of RAISE NOTICE.

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

• 您不需要DISTINCT在information_schema.columns. 每个表的列名都是唯一的。

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

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

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

  • 更新以特定字符串开头的多个列

• plpgsql 中的赋值运算符是:=.

• 的简化语法RAISE NOTICE。

Core problem impossible to solve

无法解决的核心问题

All of this still doesn't solve your main problem: SQL demands a definition of the columns to be returned. You can circumvent this by returning anonymous records like you tried. But that's just postponing the inevitable. Now you have to provide a column definition list at call time, just like your error message tells you. But you just don't know which columns are going to be returned. Catch 22.

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

Your call wouldwork like this:

你的电话会像这样工作：

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);

But you don't know number, names (optional) and data types of returned columns, not at creation time of the function and not even at call time. It's impossibleto do exactly that in a singlecall. You need two separate queriesto the database.

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

You couldreturn allcolumns of any given table dynamically with a function using polymorphic types, because there is a well defined type for the whole table. Last chapter of this related answer:

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

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

• 重构一个 PL/pgSQL 函数以返回各种 SELECT 查询的输出