Re: How to fix Execute format error?

Поиск
Список
Период
Сортировка
От Joe How
Тема Re: How to fix Execute format error?
Дата
Msg-id CAPM0uuVoZWgWq_12JXAs7Ofs=qggDNY1bsaby_KKHEBfPKc_WA@mail.gmail.com
обсуждение исходный текст
Ответ на Re: How to fix Execute format error?  (Pavel Stehule <pavel.stehule@gmail.com>)
Список pgsql-sql
Дерево обсуждения
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:
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: 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

В списке pgsql-sql по дате отправления: