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