Create a function that returns multiple rows?

Top  Previous  Next

CREATE or REPLACE FUNCTION public.test_func2(out dname varchar,out dba_id integer)

AS

$BODY$

DECLARE

arow record;

BEGIN

       FOR arow IN select datname,datdba from pg_database

       LOOP

               dname = arow.dataname;

               dba_id = arow.datadba;

                   RETURN NEXT;

          END LOOP;

END;

$BODY$

LANGUAGE 'plpgsql' VOLATILE;

 

Call from the query editor like this:

 

select * from public.test_func2();

 

NOTE: this will only work in this form with servers version 8.1 and up.

 

For 8.1 and earlier you must use a composite type to define the return parameters.