How to run a string containing a static SQL query in PostgreSQL?

Question

I have a function which generates a static SQL query and returns it as a string. What I need to do is to run the returned string as if I was typing it directly in psql. Here is a very simplified test case (the true mechanism is far more complicated but the following test case at least shows the idea)

drop table if exists person;
create table person(
    first_name  varchar(30),
    last_name   varchar(50),
    email       varchar(100)
);
insert into person(first_name, last_name, email) values 
    ('fname01', 'lname01', '[email protected]'),
    ('fname02', 'lname02', '[email protected]'),
    ('fname03', 'lname03', '[email protected]'),
    ('fname04', 'lname04', '[email protected]'),
    ('fname05', 'lname05', '[email protected]');
--
--
drop function if exists sql_gen;
create function sql_gen() returns text as
$fun$
begin
    return 'select * from person';
end;
$fun$
language plpgsql;
--
--

Now if I run the following:

select * from sql_gen();
       sql_gen
----------------------
 select * from person
(1 ligne)

This gives me the sql query. But what I want to do is to run the query from the returned string as if I was writing manually select * from person in psql and hitting Enter in order to obtain the result of the query.

I've checked PREPARE, EXECUTE on the online documentation but so far I've not been able to achieve this. Could you kindly make some clarification?

Show the true objective you are trying to achieve as update to your question. If all you want to do is return a table then look at Return table — Adrian Klaver
– Adrian Klaver, Commented Jul 16, 2021 at 15:08

jjanes · Accepted Answer · 2021-07-16 18:53:03Z

1

And answer specific for psql is to change your semicolon to \gexec.

select * from sql_gen() \gexec

answered Jul 16, 2021 at 18:53

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user17911 Over a year ago

Thanks a lot for your help. Can the output be redirected to another query? For example redirect the above select into a CREATE VIEW AS SELECT . . . statement so that I might be able to query the view later?

user17911 Over a year ago

Found it! select format('create view mytmp_v as %s', t1.colval) from (select sql_gen as colval from sql_gen()) as t1 \gexec

Collectives™ on Stack Overflow

How to run a string containing a static SQL query in PostgreSQL?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related