Re: How to fix Execute format error?

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(dot)stehule(at)gmail(dot)com> wrote:

> Hi
>
> pá 5. 2. 2021 v 14:46 odesílatel Joe How <huijoehow(at)gmail(dot)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: 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.
>
> Regards
>
> Pavel
>
>
>
>
>>
>> --
>> Best Regards,
>> Joe
>>
>>

--
Best Regards,
Joe How