How to run get the result from a query built from SELECT FORMAT in Postgresql at pgadmin?

Question

I have a command to run in pgadmin which looks like below:

SELECT 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')

It prints a string starting with SELECT statement.

How do I get the result from the query straight from the string returned by the FORMAT?

I have tried something like:

DO
$$

WITH str as( SELECT 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'))

BEGIN EXECUTE str;

END
$$

However, I got an error message saying:

ERROR: syntax error at or near "WITH"

~~What have I missed here? Please advise!!~~

Updated answer

After combining answers from the experts below, here is the updated version for future reference:

do $$
DECLARE
   query text;
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 format('create or replace temp view tmp_view_vehicles as %s', query);
end $$;

select * from tmp_view_vehicles;

Thank you everyone & your patience!

score 4 · Accepted Answer · answered Feb 05 '21 at 15:52

4

If you don't want to create the stored function but want to get the result using anonymous do block then you could to use temporary view:

do $$
begin
    execute format('create or replace temp view tmp_view_123 as select ...', ...);
end $$;

select * from tmp_view_123;

Created view is visible for the current session only.

demo

answered Feb 05 '21 at 15:52

Abelisto

14,826
2
33
41

Very very useful suggestion. – Stefanov.sm Feb 06 '21 at 18:56

Laurenz Albe · Answer 2 · 2021-02-05T14:07:23.770

2

You are mixing up SQL and PL/pgSQL syntax, and not in a very consistent fashion.

Define a PL/pgSQL variable:

DO
$$DECLARE
   query text;
   result record;
BEGIN
   query := format(...);
   EXECUTE query INTO result;
END;$$;

edited Feb 05 '21 at 14:07

answered Feb 05 '21 at 12:57

Laurenz Albe

209,280
17
206
263

how do I get the result from the `EXECUTE query`? – hunterex Feb 05 '21 at 13:52
`EXECUTE ... INTO`. But that is not connected to your question, is it? – Laurenz Albe Feb 05 '21 at 13:54
Sorry if I have not made my question clear. I want to get the result built from the string query. In other words, I want to get the result from the `format(...)`, which is a `Select` statement. How do I use `EXECUTE ... INTO` to get the result from the `Select`? – hunterex Feb 05 '21 at 13:57
Do you want to get the `SELECT` statement or the result from the `SELECT` statement? The former is in `query`, the latter can be had with `EXECUTE query INTO var`. – Laurenz Albe Feb 05 '21 at 14:01
I have updated my question. I want to get the result from the `SELECT` statement. Can you update the answer with `EXECUTE query INTO var`? How do I declare the `var` type above? – hunterex Feb 05 '21 at 14:06
Ok, done. But that's the end of the tutorial session. Read the documentation. – Laurenz Albe Feb 05 '21 at 14:07

Stefanov.sm · Answer 3 · 2021-02-05T15:27:30.883

The plain answer is

do language plpgsql
$$
begin
 EXECUTE format('SELECT ....' <your code here>);
end;
$$;

However anonymous blocks do not return anything. Maybe you'll have to shape the block as a table-returning function.

Edit

I do not think that there is a straightforward way to do this - change the return table structure of a function dynamically. But you can return a single json column with key-value pairs inside.
Here is such a function:

create or replace function query_to_jsonset(qr text) returns setof json as
$$
begin
    return query execute 'SELECT row_to_json(dyntbl) FROM ('||qr||') AS dyntbl';
end;
$$ language plpgsql;

and then your query will look simple as that:

select js from query_to_jsonset(format(....)) js;

Please note that query_to_jsonset is unsafe.

how do I return a table of undefined columns? – hunterex Feb 05 '21 at 13:59 — hunterex, Feb 05 '21 at 13:59

How to run get the result from a query built from SELECT FORMAT in Postgresql at pgadmin?

Updated answer

3 Answers3