Re: Returning a row from a function with an appended array field

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.