Re: cursors and function question

[armand pirvu <armand.pirvu@xxxxxxxxx>]

On Feb 13, 2018, at 12:26 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Tuesday, February 13, 2018, armand pirvu <armand.pirvu@xxxxxxxxx> 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