Re: cursors and function question - Mailing list pgsql-general

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