-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Wes Cravens Sent: Thursday, November 10, 2011 11:54 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
