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

From: "Willy-Bas Loos" <willybas(at)gmail(dot)com>
To: "MuraliPD(at)GMail" <murali(dot)pd(at)gmail(dot)com>
Cc: "Sathish Duraiswamy" <sathish(at)leatherlink(dot)net>, pgsql-general(at)postgresql(dot)org
Subject: Re: Need help returning record set from a dynamic sql query
Date: 2008-08-12 15:57:11
Message-ID: 1dd6057e0808120857p6e5438edu25aa48afe5dc39d9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message William Garrison 2008-08-12 16:00:33 ftell error during pg_dump
Previous Message Tom Lane 2008-08-12 15:53:58 Re: automatic REINDEX-ing