Re: cursors and function question

[armand pirvu]

> On Feb 13, 2018, at 12:26 PM, David G. Johnston <david.g.johns...@gmail.com> 
> wrote:
> 
> On Tuesday, February 13, 2018, armand pirvu <armand.pi...@gmail.com 
> <mailto:armand.pi...@gmail.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