On 02/13/2018 10:22 AM, armand pirvu wrote:
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;
CREATE OR REPLACE FUNCTION public.foofunc()
RETURNS SETOF testtbl
LANGUAGE sql
AS $function$
SELECT * FROM testtbl;
$function$
test=> select * from foofunc();
col1 | col2 | col3
------+------+------
E1 | CAT1 | 0
E1 | CAT2 | 0
E1 | CAT3 | 0
E4 | CAT1 | 0
E5 | CAT1 | 0
E6 | CAT1 | 0
E7 | CAT1 | 0
(7 rows)
select foofunc();
foofunc
-------------------------------
("E1 ","CAT1 ",0)
But I am looking to get
foofunc
-------------------------------
("E1 ","CAT1 ",0)
("E1 ","CATs ",0)
etc
Many thanks
— Armand
--
Adrian Klaver
adrian.kla...@aklaver.com