Re: Returning a row from a function with an appended array field - Mailing list pgsql-general

On Nov 9, 2011, at 20:19, Wes Cravens <wcravens@cortex-it.com> 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.
>
> Something like this pseudo code:
>
> FOR row IN SELECT * FROM thingy
> LOOP
>  RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = id]
> END LOOP,
> RETURN
>
> Any help much appreciated,
>
> Wes
>

Use "WITH RECURSIVE" instead of a function.

David J.