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
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 |