From: codeWarrior <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Returning a set from an function
Date: Mon, 10 Apr 2006 08:10:47 -0700
Message-ID: <e1dsfa$2mqf$1@news.hub.org> (raw)
References: <45435c1a0604081410o12d5a456x1fb6d09ce8973cc6@mail.gmail.com>
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.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.comhttp://realopen.org
> http://www.kasamba.com/Keith-Hutchison
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: Returning a set from an function
In-Reply-To: <e1dsfa$2mqf$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox