pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedReturning a set from an function
3+ messages / 3 participants
[nested] [flat]
* Returning a set from an function
@ 2006-04-08 21:10 Keith Hutchison <keith.kjtl.hutchison@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Keith Hutchison @ 2006-04-08 21:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning a set from an function
@ 2006-04-08 21:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Keith Hutchison <keith.kjtl.hutchison@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-04-08 21:46 UTC (permalink / raw)
To: Keith Hutchison <keith.kjtl.hutchison@gmail.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Returning a set from an function
@ 2006-04-10 15:10 codeWarrior <gpatnude@hotmail.com>
parent: Keith Hutchison <keith.kjtl.hutchison@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: codeWarrior @ 2006-04-10 15:10 UTC (permalink / raw)
To: pgsql-sql
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.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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-10 15:10 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-08 21:10 Returning a set from an function Keith Hutchison <keith.kjtl.hutchison@gmail.com>
2006-04-08 21:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-10 15:10 ` codeWarrior <gpatnude@hotmail.com>
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