Thread: Returning a set from an function

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

"Keith Hutchison" <keith.kjtl.hutchison@gmail.com> writes:
> Looking for an example showing how to return a set from either a sql
> function or a plpsqq function.

Try "SQL Functions Returning Sets" here:
http://www.postgresql.org/docs/8.1/static/xfunc-sql.html

In plpgsql you just "RETURN NEXT" each value (typically this would
be inside a loop, though it doesn't have to be) and then either
RETURN with no argument or fall off the end of the function (I think
the latter is only allowed as of PG 8.1).  There don't seem to be
any very compelling examples in the manual, but I bet you can find
some at techdocs.postgresql.org.
        regards, tom lane

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
WHEREparent = $1 AND 
 
active_flagLOOP
 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.kjtl.hutchison@gmail.com> wrote in message 
news:45435c1a0604081410o12d5a456x1fb6d09ce8973cc6@mail.gmail.com...
> 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
>