将使用prepare语句的动态查询的输出保存到表中 [英] Saving the output of a dynamic query that uses prepare statement into a table

问题描述

继续以前的案例（@ Erwin Brandstetter提供的解决方案），其中创建了一个使用'prepare'语句的动态SELECT查询，然后通过调用它来执行，如下所示：

In continuation to a previous case (a solution by @Erwin Brandstetter), in which a dynamic SELECT query that uses a 'prepare' statement was created and then was executed by calling it, as below:

-编写prepare语句的功能：

--the function for making the prepare statement:

CREATE OR REPLACE FUNCTION f_prep_query (_tbl regclass, _prefix text)
  RETURNS void AS 
$func$
DECLARE
  _prep_qry text := (
     SELECT 'PREPARE stmt_dyn AS SELECT '
         || string_agg(quote_ident(attname), ',' ORDER BY attname)
         || ' FROM ' || _tbl
      FROM   pg_attribute
      WHERE  attrelid = _tbl
      AND    attname LIKE _prefix || '%'
      AND    attnum > 0
      AND    NOT attisdropped
     );
BEGIN
   EXECUTE _prep_qry;
EXCEPTION WHEN duplicate_prepared_statement THEN
   DEALLOCATE stmt_dyn;
   EXECUTE _prep_qry;
END
$func$  LANGUAGE plpgsql;

-呼叫者：

BEGIN; -- optional
SELECT f_prep_query('dkj_p_k27ac'::regclass, 'enri'::text);
EXECUTE stmt_dyn;

我想问以下问题：
我们从指示中获得的期望输出过程被输出到DataOutput中。
我想找到一种将数据存储到数据库中新表中的方法。

I would like to ask the following: The desired output that we get from the indicated procedure is outputted into the DataOutput. I would like to find a way to store the data into a new table in the db.

推荐答案

通常，如果只想写表，请不要使用准备好的 SELECT 语句（或游标）。

Generally, if you just want to write to a table, don't use a prepared SELECT statement (or a cursor). That's very inefficient for the purpose.

直接写入表格，就像上一个答案中所解释的那样：

Write to the table directly like explained in the previous answer:

• 将使用refcursor的动态查询的输出保存到表中

• Saving the output of a dynamic query that uses refcursor into a table

完整的 INSERT 可以 为 。但不是 CREATE TABLE AS 。 每个文档：

任何 SELECT ， INSERT ， UPDATE ， DELETE 或 VALUES 语句。

这篇关于将使用prepare语句的动态查询的输出保存到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！