Re: cursors and function question

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

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