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

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.

Just in case someone else want's an answer to this tread... and despite
the fact that the postgresql documentation is excellent and has plenty
of examples, WITH RECURSIVE is still a bad solution...

I already needed an independent get_children function:

                                              CREATE OR REPLACE
FUNCTION get_children (
  lookup_id INT
)                                             RETURNS
int[]                                         AS
$$
  SELECT array_agg( id )
  FROM (
    SELECT id
      FROM thingy
      WHERE parent_id = $1
      ORDER BY id
  ) t;
$$                                            LANGUAGE
'sql';

And I just used that in a view to get what I wanted:

CREATE OR REPLACE VIEW thingy_view AS
SELECT *,get_children(id) AS children FROM thingy;

I then updated all of my other get_ accessor postgresql functions to use
the view instead of the base table.

FTW

Wes