Re: How to fix Execute format error?

pá 5. 2. 2021 v 18:41 odesílatel Joe How <huijoehow(at)gmail(dot)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.

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