On 10 Nov 2011, at 19:51, Wes Cravens wrote: > On 11/10/2011 12:05 PM, David Johnston wrote: >> On 11/9/2011 7:19 PM, Wes Cravens wrote: >>> I have an adjacency list kind of table >>> >>> CREATE TABLE thingy ( >>> id int, >>> parent int >>> ); >>> >>> I'd like to be able to write a procedural function that returns a row >>> or rows from this table with an appended field that represents the children. >> >> If you only care about one level of hierarchy then, yes, WITH RECURSIVE is >> overkill. You want to use WITH RECURSIVE in those situations where the >> depth of the hierarchy is unknown. > > Yes agreed... WITH RECURSIVE would be handy for something like > get_ancestors or get_descendents.
If you only need one level of recursion, you can just use a self-join. SELECT parent.id AS parent_id, child.id as child_id FROM thingy AS parent LEFT OUTER JOIN thingy AS child ON (child.parent_id = parent.id) Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
