使用功能的文本输出作为新查询 [英] Use text output from a function as new query

问题描述

从

In continuing from a previous case that was assisted by @Erwin Brandstetter and @Craig Ringer, I have fixed my code to become as follows. Note, that my function myresult() outputs now text, and not a table (as indeed, as was pointed out in the former case, there is no point in outputting a table object, since we would need to define all its columns ahead, which basically defies the entire purpose):

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

   RAISE NOTICE 'My additional text: %', myoneliner;
   RETURN myoneliner;
END
$func$ LANGUAGE plpgsql;

致电:

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

运行上述过程后，我得到一个文本字符串，该字符串基本上是一个查询.为了简单起见，我接下来将其称为单一输出".
"oneline-output"如下所示(我只是从我在这里输入的一个输出单元格中复制/粘贴了它):

Upon running the above procedure I get a text string, which is basically a query. I'll refer to it up next as 'oneliner-output', just for simplicity.
The 'oneline-output' looks as follows (I just copy/paste it from the one output cell that I've got into here):

"SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac"

• 请注意，语句两边的双引号是myresult()输出的一部分.我没有添加它们.
• Note that the double quotes from both sides of the statement were part of the myresult() output. I didn't add them.

我现在更好地理解了构造一个单一的功能的想法，这个功能既可以创建单一输出"又可以执行它.我可以将单行输出"复制/粘贴到新的Postgres查询窗口中，然后像正常查询一样执行它，就可以在数据输出"窗口中接收所需的列和行.
但是，我想自动执行此步骤，以避免复制/粘贴步骤. Postgres中是否有一种方法可以使用我从myresult()函数接收的text输出("oneliner输出")并执行它?是否可以创建第二个函数来接收myresult()的输出并将其用于执行查询?

I understand much better now the problematic idea of thinking to construct a single function that would both create the 'oneliner-output' AND execute it. I am able to copy/paste the 'oneliner-output' into a new Postgres query window and execute it as a normal query just fine, receiving the desired columns and rows in my Data Output window.
I would like, however, to automate this step, so to avoid the copy/paste step. Is there a way in Postgres to use the text output (the 'oneliner-output') that I receive from myresult() function, and execute it? Can a second function be created that would receive the output of myresult() and use it for executing a query?

沿着这些行，尽管我知道以下脚本(在下面)有效，并且实际上输出的是所需的列和行:

Along these lines, while I know that the following scripting (here below) works and actually outputs exactly the desired columns and rows:

-- DEALLOCATE stmt1; -- use this line after the first time 'stmt1' was created
prepare stmt1 as SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;
execute stmt1;

• 在考虑正确的调整之后，我在想以下脚本可能会起作用?不确定如何.

  • I was thinking maybe something like the following scripting could potentially work, after doing the correct tweaking? Not sure how though.

    prepare stmt1 as THE_OUTPUT_OF_myresult();
    execute stmt1;
    

  CREATE OR REPLACE FUNCTION show_mytable(ref refcursor) RETURNS refcursor AS $$
  BEGIN
     OPEN ref FOR SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac FROM dkj_p_k27ac;   -- Open a cursor 
     RETURN ref;    -- Return the cursor to the caller
  END;
  $$ LANGUAGE plpgsql;
  

  致电:

  BEGIN;
  SELECT show_mytable('roy');
  FETCH ALL IN "roy"; 
  

  此过程实际上可以正常工作，并吐出所需的列和行，但再次，我必须提供确切的SELECT语句.

  This procedure, actually works and spits out the desired columns and rows, and yet again, I have to provide the exact SELECT statement.

  我基本上希望能够提供它，而不是将其作为我的myresult()函数的输出.像这样:

  I basically would like to be able and provide it instead as the output of my myresult() function. Something like this:

  CREATE OR REPLACE FUNCTION show_mytable(ref refcursor) RETURNS refcursor AS $$
  BEGIN
     OPEN ref FOR myresult();   -- Open a cursor 
     RETURN ref;    -- Return the cursor to the caller
  END;
  $$ LANGUAGE plpgsql;
  

  致电:

  BEGIN;
  SELECT show_mytable('roy');
  FETCH ALL IN "roy"; 
  

  推荐答案

  使用要将数据转换为可执行的 code ，您需要使用动态SQL，即在plpgsql函数或DO语句中使用EXECUTE.只要返回类型不依赖于第一个函数myresult()的结果，此方法就不会出现问题.否则，您又回到了我先前的回答中概述的22:

  To convert data into executable code you need to use dynamic SQL, i.e. EXECUTE in a plpgsql function or DO statement. This works without problem as long as the return type does not depend on the outcome of the first function myresult(). Else you are back to catch 22 as outlined in my previous answer:

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

  关键是要以某种方式声明返回类型(在这种情况下为行类型).您可以为此创建一个TABLE，TEMP TABLE或TYPE.或者，您可以使用准备好的语句或引用器.

  The crucial part is to declare the return type (row type in this case) somehow. You can create a TABLE, TEMP TABLE or TYPE for the purpose. Or you can use a prepared statement or a refcursor.

  您已经非常亲密.难题中缺少的部分是使用动态SQL 准备生成的查询.

  You have been very close. The missing piece of the puzzle is to prepare the generated query with dynamic SQL.

  一次 创建此功能.这是您的函数myresult()的优化和安全版本:

  Create this function once. It's a optimized and safe version of your function myresult():

  CREATE OR REPLACE FUNCTION f_prep_query (_tbl regclass, _prefix text)
    RETURNS void AS 
  $func$
  BEGIN
     IF EXISTS (SELECT 1 FROM pg_prepared_statements WHERE name = 'stmt_dyn') THEN
        DEALLOCATE stmt_dyn;
     END IF;                 -- you my or may not need this safety check 
  
     EXECUTE (
       SELECT 'PREPARE stmt_dyn AS SELECT '
           || string_agg(quote_ident(attname), ',' ORDER BY attname)
           || ' FROM ' || _tbl
        FROM   pg_catalog.pg_attribute
        WHERE  attrelid = _tbl
        AND    attname LIKE _prefix || '%'
        AND    attnum > 0
        AND    NOT attisdropped
       );
  END
  $func$  LANGUAGE plpgsql;
  

  我将regclass用作表名参数_tbl，以使其明确且安全，防止 SQLi .详细信息:

  I use regclass for the table name parameter _tbl to make it unambiguous and safe against SQLi. Details:

  • 表名作为PostgreSQL函数参数

  信息模式不包括系统目录的oid列，因此我切换到了pg_catalog.pg_attribute而不是information_schema.columns.那也更快.有优点和缺点:

  The information schema does not include the oid column of system catalogs, so I switched to pg_catalog.pg_attribute instead of information_schema.columns. That's faster, too. There are pros and cons for this:

  • 如何检查如果表存在于给定的模式中

  如果已经存在名称为stmt_dyn的准备好的语句，则PREPARE将引发异常.如果可以接受，请删除系统视图上的检查 pg_prepared_statements 和以下DEALLOCATE.
  _{更复杂的算法可以在每个会话中管理多个准备好的语句，或者将准备好的语句的名称用作附加参数，甚至可以使用查询字符串的MD5哈希作为名称，但这不在此问题的范围内.}

  If a prepared statement with the name stmt_dyn already existed, PREPARE would raise an exception. If that is acceptable, remove the check on the system view pg_prepared_statements and the following DEALLOCATE.
  _{More sophisticated algorithms are possible to manage multiple prepared statements per session, or take the name of the prepared statement as additional parameter, or even use an MD5 hash of the query string as name, but that's beyond the scope of this question.}

  请注意，PREPARE在事务的范围之外运行，一旦PREPARE成功，则在会话的整个生命周期中都存在准备好的语句.如果包装事务中止，PREPARE不受影响. ROLLBACK 无法删除准备好的语句.

  Be aware that PREPARE operates outside the scope of transactions, once PREPARE succeeds, the prepared statement exists for the lifetime of the session. If the wrapping transaction is aborted, PREPARE is unaffected. ROLLBACK cannot remove prepared statements.

  两个查询，但仅一个调用服务器.而且非常有效.

  Two queries, but only one call to the server. And very efficient, too.

  SELECT f_prep_query('tbl'::regclass, 'pre'::text);
  EXECUTE stmt_dyn;
  

  在大多数简单用例中，比创建临时表或游标并从中选择/获取(这是其他选择)更简单，效率更高.

  Simpler and much more efficient for most simple use cases than creating a temp table or a cursor and selecting / fetching from that (which would be other options).

  SQL小提琴.

  这篇关于使用功能的文本输出作为新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！