Re: How to fix Execute format error?

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