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

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', 'fname01.lname01@hotmail.com'),
    ('fname02', 'lname02', 'fname02.lname02@hotmail.com'),
    ('fname03', 'lname03', 'fname03.lname03@hotmail.com'),
    ('fname04', 'lname04', 'fname04.lname04@hotmail.com'),
    ('fname05', 'lname05', 'fname05.lname05@hotmail.com');
--
--
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?

Answer

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

select * from sql_gen() gexec

Advertisement

Answer