Dynamic Query Creation and Execution PostgreSQL

Question

I have below two tables and two pl/pgsql functions.

CREATE TABLE tt1(id int, name text);
CREATE TABLE tt2(id int, name text);

INSERT INTO tt1 VALUES (1,'name1');
INSERT INTO tt1 VALUES (2,'name2');
INSERT INTO tt1 VALUES (3,'name3');
INSERT INTO tt2 VALUES (4,'name4');
INSERT INTO tt2 VALUES (5,'name5');
INSERT INTO tt2 VALUES (6,'name6');


CREATE OR REPLACE FUNCTION query_string() RETURNS TEXT  AS
$BODY$
DECLARE
query1 TEXT:='';
BEGIN
    query1 := 'SELECT * FROM tt1 UNION ALL SELECT * FROM tt2';      
    RETURN query1;
END;
$BODY$
LANGUAGE PLPGSQL;



CREATE OR REPLACE FUNCTION use_generated_string() RETURNS VOID     AS
$BODY$
DECLARE
BEGIN
       -- Need to modify here to get the result same as below query by 
       -- calling above function.
       -- "SELECT * FROM tt1 UNION ALL SELECT * FROM tt2"
END;
$BODY$
LANGUAGE PLPGSQL;

query_string function returns the query string. How can i modify the "use_generated_string" function so that I can get the result of the below query by calling the use_generated_string function.

SELECT * FROM tt1 UNION ALL SELECT * FROM tt2;

Can anyone help ?

score 2 · Answer 1 · edited May 23 '17 at 10:28

If you declare the return type (and keep the called SQL fixed to that type) you can do:

CREATE OR REPLACE FUNCTION use_generated_string() RETURNS TABLE( c1 INT, c2 TEXT ) AS
$BODY$
DECLARE
BEGIN
    RETURN QUERY EXECUTE query_string();
END;
$BODY$
LANGUAGE PLPGSQL;

If the return type should remain dynamic, this is a delicate problem and I suggest to start with reading Erwin Brandstetter's excellent answers.

klin's answer avoids the whole problem by using RAISE NOTICE, which is quite clever, but I'm unsure how one would go about using the results of such a call, except for manual text parsing.

score 1 · Accepted Answer · answered Oct 27 '15 at 13:36

Use EXECUTE:

CREATE OR REPLACE FUNCTION use_generated_string() RETURNS VOID     AS
$BODY$
DECLARE
    rec record;
BEGIN
    FOR rec IN EXECUTE(query_string()) LOOP
        RAISE NOTICE '%', rec.name;
    END LOOP;
END;
$BODY$
LANGUAGE PLPGSQL;

SELECT use_generated_string();

NOTICE:  name1
NOTICE:  name2
NOTICE:  name3
NOTICE:  name4
NOTICE:  name5
NOTICE:  name6
 use_generated_string 
----------------------

(1 row)

Dynamic Query Creation and Execution PostgreSQL

2 Answers2