I want to use PLpgSQL - EXECUTE runs dynamic query.
So I modified the code to look like:
DO $$ DECLARE query text; result record; BEGIN query := format('SELECT * FROM %I.%I CROSS JOIN LATERAL json_to_record(%I::json) AS rs(%s)', 'public', 'vehicles', 'column_a', array_to_string( (SELECT ARRAY(SELECT DISTINCT col FROM vehicles CROSS JOIN LATERAL json_object_keys( column_a ::json) AS t(col) ORDER BY col)), ' text , ' ) || ' text'); EXECUTE query INTO result; END;$$
However, I do not know how to pass the result from the generated Select statement into a table with undefined columns.
Any suggestions?
you can use CREATE TABLE xxx AS SELECT statement
You should know the basic rule of programming in Postgres. The structure of the result must be known before query (statement) execution. The query calculates just rows. Columns should be known from SQL. There is not any exception from this rule.
But you can store some unknown result to the new persistent or temporal table, and then you can work over this table.
EXECUTEFORMAT( $$ SELECT * FROM %I.%I CROSSJOINLATERAL json_to_record(%I::json) AS rs(%s); $$, 'public', 'vehicles', 'column_a', array_to_string( (SELECTARRAY(SELECTDISTINCTcolFROM vehicles CROSSJOINLATERAL json_object_keys(column_a::json) AS t(col) ORDERBYcol)), ' text , ' ) || ' text'
);
I got an error message when I run it in pgadmin: ERROR: prepared statement "format" does not exist SQL state: 26000
Any advice on how to fix it? Thanks
What you want to do?
If you want to run dynamic statement, then you should be in a PLpgSQL environment. If you want to run a prepared statement, then you should use PREPARE statement first.
Attention: Inside Postgres you can use two different EXECUTE statements - inside SQL - EXECUTE runs prepared statement, and inside PLpgSQL - EXECUTE runs dynamic query.