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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Wes Cravens
Sent: Thursday, November 10, 2011 11:54 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Returning a row from a function with an appended
array field

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

--------------------------------------------------

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.

David J.