> On Feb 13, 2018, at 1:22 PM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > > On 02/13/2018 11:17 AM, armand pirvu wrote: >>> On Feb 13, 2018, at 12:54 PM, Adrian Klaver <adrian.kla...@aklaver.com >>> <mailto:adrian.kla...@aklaver.com><mailto:adrian.kla...@aklaver.com >>> <mailto:adrian.kla...@aklaver.com>>> 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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> >>> <mailto:adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>> >> 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.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com>
Not a trigger , but the idea is we will do some batch processing from said table let’s name it testtbl 1 - we get the records using select for update with a limit 100 for example 2 - update each record using using cursor 3 - print the cursor content so that way I have an idea what was updated I was thinking that if I can put a unique constraint on the table, I can generate a global table in the function , update main table from global table and return select from global table I can see the developer desire to use cursors to minimize some effort on his side Thanks Armand
