Postgres Pro
Downloads
Home   >   mailing lists

Re: cursors and function question - Mailing list pgsql-general

From armand pirvu
Subject Re: cursors and function question
Date
Msg-id 4EC32AB4-3BCF-47F1-AEF2-9478B6F02C51@gmail.com
Whole thread Raw
In response to Re: cursors and function question  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: cursors and function question  ("David G. Johnston" <david.g.johnston@gmail.com>)
List pgsql-general
Tree view

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

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

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Multiple postmasters running from same directory
Next
From: "David G. Johnston"
Date:
Subject: Re: cursors and function question