Saving the output of a dynamic query that uses prepare statement into a table

Question

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:

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

--the calling:

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

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.

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

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:

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

The complete INSERT could be the prepared statement. But not CREATE TABLE AS. Per documentation:

Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement.

edited May 23 '17 at 11:50

Community

1
1

answered Jan 09 '15 at 18:13

Erwin Brandstetter

605,456
145
1,078
1,228

I see, so the 'prepared' solution seems to be rather limited in this respect, while the 'refcursor' solution does enable us to create a new table (which is part of the original plan). It is a little odd that the 'prepare' is limited to only INSERT, UPDATE and DELETE, as it reduce its power in a way. The entire idea behind the task we were originally after is to generate a daughter table with only selected number of columns in it, which we DONT want to pre-define explicitly, but rater rely on their shared prefix. – Roy Jan 09 '15 at 23:20
We have been using here the 'prepared' select statement because it was one of the solution for how to execute a dynamic SELECT statement. The entire procedure seems to work fine as we articulated here: stackoverflow.com/questions/27831796/… and here:http://stackoverflow.com/questions/27824725/use-text-output-from-a-function-as-new-query/27830985#27830985 – Roy Jan 09 '15 at 23:25
The broken link leads to the right solution for the use case: http://stackoverflow.com/questions/27831796/saving-the-output-of-a-dynamic-query-that-uses-refcursor-into-a-table To ***create*** a table dynamically, neither a cursor nor a prepared statement would be very good tools. – Erwin Brandstetter Jan 10 '15 at 16:02

Saving the output of a dynamic query that uses prepare statement into a table

1 Answers1

Linked