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?
Advertisement
Answer
And answer specific for psql is to change your semicolon to gexec.
select * from sql_gen() gexec