Re: Returning a set from an function

Keith: This is the general approach I use over and over and over -- This is
a PLPGSQL function that returns a SETOF

tablename%ROWTYPE

If you need the full schema and table and everything that goes with this --
let me know ---

CREATE OR REPLACE FUNCTION sys_aclsubmenu(int4) RETURNS SETOF
sys_tree_components AS
$BODY$

DECLARE MNU ALIAS FOR $1;
DECLARE OUT sys_tree_components%ROWTYPE;
DECLARE CHILD sys_tree_components%ROWTYPE;
BEGIN

RAISE NOTICE 'sys_aclsubmenu(integer, varchar) called for item: %', $1;
FOR OUT IN SELECT * FROM sys_tree_components WHERE parent = $1 AND
active_flag
LOOP

IF (OUT.id != OUT.parent) THEN

FOR CHILD IN SELECT * FROM sys_aclsubmenu(OUT.id) LOOP

RETURN NEXT CHILD;

END LOOP;

END IF;
RETURN NEXT OUT;

END LOOP;
RETURN;

END;

$BODY$ LANGUAGE 'plpgsql' VOLATILE;

SELECT * FROM sys_aclsubmenu(1) ORDER BY parent, id ;

""Keith Hutchison"" <keith(dot)kjtl(dot)hutchison(at)gmail(dot)com> wrote in message
news:45435c1a0604081410o12d5a456x1fb6d09ce8973cc6(at)mail(dot)gmail(dot)com(dot)(dot)(dot)
> G'day,
>
> Looking for an example showing how to return a set from either a sql
> function or a plpsqq function.
>
> Thanks
>
> --
> Keith Hutchison
> http://balance-infosystems.com http://realopen.org
> http://www.kasamba.com/Keith-Hutchison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>