Saving the output of a dynamic query that uses a prepare statement on a table
Continuing up to the previous case (solution by @Erwin Brandstetter) in which a dynamic SELECT query that uses the a 'prepare' statement was then executed by calling it like below:
- a function to execute the make 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;
- call:
BEGIN; -- optional
SELECT f_prep_query('dkj_p_k27ac'::regclass, 'enri'::text);
EXECUTE stmt_dyn;
I would like to ask the following: The desired result we get from the specified procedure is output to DataOutput. I would like to find a way to store data in a new table in the db.
source to share
Generally, if you just want to write to a table, don't use a prepared statement SELECT
(or cursor). This one is very ineffective for this purpose.
Write to spreadsheet as described in the previous answer:
Complete INSERT
can be prepared by the operator. But not CREATE TABLE AS
. In the documentation:
Any
SELECT
,INSERT
,UPDATE
,DELETE
orVALUES
.
source to share