On 02/13/2018 11:17 AM, armand pirvu wrote:
On Feb 13, 2018, at 12:54 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx
<mailto:adrian.klaver@xxxxxxxxxxx>> wrote:
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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
Thanks Adrian
That one I figured it out as well. The idea is that said table has some
records which I need to loop and do some processing using cursors
similar with
DECLARE
cur CURSOR FOR SELECT *
FROM testtbl FOR UPDATE;
BEGIN
FOR row IN cur LOOP
UPDATE testtbl
SET col3=1
WHERE CURRENT OF cur;
END LOOP;
return cur;
END
For a row update the goal is to return the cursor value be it
before/after the update, hence my question and test
Not following, are you looking to do this in an UPDATE trigger or
somewhere else?
Another way to ask is why do you want to use a cursor?
I found some code which seems to do what I need but it involves two
functions
CREATE or replace FUNCTION reffunc(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT col FROM test;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
And this is what beats me , aka can I put all in one / how ?
Thanks
Armand
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
