> On Feb 13, 2018, at 12:26 PM, David G. Johnston <[email protected]>
> wrote:
>
> On Tuesday, February 13, 2018, armand pirvu <[email protected]
> <mailto:[email protected]>> 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
