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(dot)klaver(at)aklaver(dot)com