CODEWITHSUNDEEP
sqlpostgresqlplpgsql
user17911
user17911

Reputation: 1331

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', '[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?

Upvotes: 0

Views: 609

Answers (1)

jjanes
jjanes

Reputation: 44137

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

select * from sql_gen() \gexec

Upvotes: 1

Related Questions