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

问题描述

继续以前的案例，其中创建了一个使用refcursor 的动态SELECT查询，然后执行了该查询-我想问以下问题: 我们从指示的过程中获得的期望输出被输出到DataOutput中. 我想找到一种将数据存储到db中的新表中的方法.

In continuation to a previous case, in which a dynamic SELECT query that uses refcursor was created and then was executed - I would like to ask the following: The desired output that we got from the indicated procedure was output into the DataOutput. I would like to find a way to store the data into a new table in the db.

代替简单的命令:

CREATE TABLE mydaughtertable AS
SELECT enrich_d_dkj_p_k27ac,enrich_lr_dkj_p_k27ac,enrich_r_dkj_p_k27ac
FROM dkj_p_k27ac

这个想法是运行类似的东西:

The idea is to run something like:

CREATE TABLE mydaughtertable AS myresult('dkj_p_k27ac','enri') 

但是此脚本不正确，并出现以下错误:

But this scripting is incorrect and gives the following error:

ERROR:  syntax error at or near "myresult"
LINE 1: CREATE TABLE mydaughtertable AS myresult('dkj_p_k27ac','enri...
                                        ^
********** Error **********

ERROR: syntax error at or near "myresult"
SQL state: 42601
Character: 33

推荐答案

这比您之前的问题更容易解决，因为我们在这里不会遇到动态返回类型的麻烦.您只需要正确地连接查询字符串，然后再将其传递给 EXECUTE .

This is solved more easily than your previous question, because we don't get in trouble with dynamic return types here. You just need to concatenate the query string correctly before passing it to EXECUTE.

对于新表:

DO
$$
BEGIN
EXECUTE 'CREATE TABLE mydaughtertable AS ' || myresult('dkj_p_k27ac','enri');
END
$$;

myresult(...)返回有效SELECT语句的文本的地方.

Where myresult(...) returns the text for a valid SELECT statement.

要添加到现有表中:

...
EXECUTE 'INSERT INTO TABLE mydaughtertable(<colum list>) '
      || myresult('dkj_p_k27ac','enri');
...

如果知道查询的结果类型与表匹配，则可以省略目标列的列表.

If you know the result type of the query matches the table, you can omit the list of target columns.

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