Re: cursors and function question

> On Feb 13, 2018, at 12:26 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> On Tuesday, February 13, 2018, armand pirvu <armand(dot)pirvu(at)gmail(dot)com <mailto:armand(dot)pirvu(at)gmail(dot)com>> wrote:
>
> CREATE OR REPLACE FUNCTION foofunc()
> RETURNS text AS $$
>
> select foofunc();
> foofunc
> -------------------------------
> ("E1 ","CAT1 ",0)
>
> But I am looking to get
>
> foofunc
> -------------------------------
> ("E1 ","CAT1 ",0)
> ("E1 ","CATs ",0)
>
>
> You need to specify SETOF
>
> CREATE FUNCTION foofunc() RETURNS SETOF text AS
>
> David J.

Thank you but

CREATE OR REPLACE FUNCTION foofunc()
RETURNS setof text AS $$
DECLARE
var2 RECORD;
cur CURSOR FOR SELECT * from testtbl;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO var2;
return var2;
END LOOP;
CLOSE cur;
END; $$
LANGUAGE plpgsql;

ERROR: RETURN cannot have a parameter in function returning set
LINE 10: return var2;
HINT: Use RETURN NEXT or RETURN QUERY.

so I employed next

CREATE OR REPLACE FUNCTION foofunc()
RETURNS setof text AS $$
DECLARE
var2 text;
cur CURSOR FOR SELECT col1 from testtbl;
BEGIN
OPEN cur;
LOOP
FETCH cur INTO var2;
return next var2;
END LOOP;
CLOSE cur;
END; $$
LANGUAGE plpgsql;

and it just sits there

Any hints ?

Thank you
— Armand