Re: cursors and function question

[armand pirvu]

> On Feb 13, 2018, at 12:54 PM, Adrian Klaver <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>


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

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