Re: cursors and function question

[Adrian Klaver <adrian.klaver@xxxxxxxxxxx>]

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