cursors and function question

From: armand pirvu <armand(dot)pirvu(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: cursors and function question
Date: 2018-02-13 18:22:19
Message-ID: 149EBC11-E33E-4DBA-9130-8C93BE4B1B43@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

Is there any elegant way not a two steps way I can output the cursor value at each step?

testtbl table has this content

col1 | col2 | col3
------------+------------+------
E1 | CAT1 | 0
E1 | CAT2 | 0
E1 | CAT3 | 0
E4 | CAT1 | 0
E5 | CAT1 | 0
E6 | CAT1 | 0
E7 | CAT1 | 0

This works
BEGIN WORK;
DECLARE fooc CURSOR FOR SELECT * FROM testtbl;
FETCH ALL FROM fooc;
CLOSE fooc;
COMMIT WORK;

col1 | col2 | col3
------------+------------+------
E1 | CAT1 | 0
E1 | CAT2 | 0
E1 | CAT3 | 0
E4 | CAT1 | 0
E5 | CAT1 | 0
E6 | CAT1 | 0
E7 | CAT1 | 0

But
CREATE OR REPLACE FUNCTION foofunc()
RETURNS 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;

select foofunc();
foofunc
-------------------------------
("E1 ","CAT1 ",0)

But I am looking to get

foofunc
-------------------------------
("E1 ","CAT1 ",0)
("E1 ","CATs ",0)
etc

Many thanks
— Armand

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2018-02-13 18:26:45 Re: cursors and function question
Previous Message Tom Lane 2018-02-13 17:46:50 Re: Multiple postmasters running from same directory