On 11/9/2011 7:34 PM, David Johnston wrote: > On Nov 9, 2011, at 20:19, Wes Cravens <wcravens@xxxxxxxxxxxxx> 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. > I apologize but I don't know how that would work. An example would help. Also... my pseudo code above was a little flawed: RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = id] should be... RETURN NEXT row,[SELECT id FROM thingy WHERE parent_id = row.id] -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
