Re: Need help returning record set from a dynamic sql query

["Willy-Bas Loos" <willybas@xxxxxxxxx>]

so use EXECUTE:


CREATE TABLE test (textcol varchar(10), intcol int);
INSERT INTO test VALUES ('a', 1);
INSERT INTO test VALUES ('a', 2);
INSERT INTO test VALUES ('b', 5);
INSERT INTO test VALUES ('b', 6);

CREATE OR REPLACE FUNCTION ReturnNexting(pText Text) RETURNS SETOF test AS $$
DECLARE
rec RECORD;
vQuery text := 'SELECT * FROM test WHERE textcol = '''||pText||'''';
BEGIN
FOR rec IN EXECUTE vQuery LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$$
LANGUAGE plpgsql;

SELECT * FROM ReturnNexting('a');

hth
WBL