CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpgadmin
hunterex
hunterex

Reputation: 595

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

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!

Upvotes: 6

Views: 2433

Answers (3)

Abelisto
Abelisto

Reputation: 15624

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

Upvotes: 5

Stefanov.sm
Stefanov.sm

Reputation: 13049

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.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 247235

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

Upvotes: 2

Related Questions