Re: cursors and function question

> On Feb 13, 2018, at 1:22 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> wrote:
>
> On 02/13/2018 11:17 AM, armand pirvu wrote:
>>> On Feb 13, 2018, at 12:54 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com><mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com> <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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