Saving the output of a dynamic query using refcursor in a table
Continuing with the previous case , in which a dynamic SELECT query was created that uses a refcursor and then executed - I would like to set the following: The desired result we got from the specified procedure was output to DataOutput. I would like to find a way to store data in a new table in the db.
Instead of a direct command:
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 script 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
source to share
This is easier to solve than your previous question because we are not dealing with dynamic return types here. You just need to concatenate the query string correctly before passing it to . EXECUTE
For a new table:
DO
$$
BEGIN
EXECUTE 'CREATE TABLE mydaughtertable AS ' || myresult('dkj_p_k27ac','enri');
END
$$;
Where myresult(...)
returns the text for a valid statement SELECT
.
To add to an existing table:
...
EXECUTE 'INSERT INTO TABLE mydaughtertable(<colum list>) '
|| myresult('dkj_p_k27ac','enri');
...
If you know the query result type is the same as a table, you can omit the target column list.
source to share