Обсуждение: How to fix Execute format error?
Dear all,
I have a query in Postgres like below:
EXECUTE 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'
);I got an error message when I run it in pgadmin:
ERROR: prepared statement "format" does not exist SQL state: 26000
ERROR: prepared statement "format" does not exist SQL state: 26000
Any advice on how to fix it? Thanks
Best Regards,
Joe
Hi
pá 5. 2. 2021 v 14:46 odesílatel Joe How <huijoehow@gmail.com> napsal:
Dear all,I have a query in Postgres like below:EXECUTE 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' );I got an error message when I run it in pgadmin:
ERROR: prepared statement "format" does not exist SQL state: 26000Any 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.
Regards
Pavel
--Best Regards,Joe
Hi Pavel,
Thank you for the response.
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?
Thanks
Joe
On Fri, 5 Feb 2021 at 15:02, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hipá 5. 2. 2021 v 14:46 odesílatel Joe How <huijoehow@gmail.com> napsal:Dear all,I have a query in Postgres like below:EXECUTE 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' );I got an error message when I run it in pgadmin:
ERROR: prepared statement "format" does not exist SQL state: 26000Any advice on how to fix it? ThanksWhat 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.RegardsPavel--Best Regards,Joe
Best Regards,
Joe How
pá 5. 2. 2021 v 18:41 odesílatel Joe How <huijoehow@gmail.com> napsal:
Hi Pavel,Thank you for the response.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.
ThanksJoeOn Fri, 5 Feb 2021 at 15:02, Pavel Stehule <pavel.stehule@gmail.com> wrote:Hipá 5. 2. 2021 v 14:46 odesílatel Joe How <huijoehow@gmail.com> napsal:Dear all,I have a query in Postgres like below:EXECUTE 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' );I got an error message when I run it in pgadmin:
ERROR: prepared statement "format" does not exist SQL state: 26000Any advice on how to fix it? ThanksWhat 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.RegardsPavel--Best Regards,Joe--Best Regards,Joe How