Re: cursors and function question

[armand pirvu <armand.pirvu@xxxxxxxxx>]

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

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