Re: cursors and function question

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