On Nov 13, 2007 3:35 PM, Steve Crawford <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote: > Given a table which includes tree-type information consisting of an id > and a parent_id, is there an already existing function that will return > the path to the top of the tree for a given record? > > The connectby function from the contrib tablefuncs does what I want for > a whole table, but I haven't found a way to execute it in an efficient > way to get the information for a single record. A query in the form of > "select connectby(.....) where ..." will return the answer I want, but > it builds the tree on the whole table and then filters to get the record > I want which, for 5000 records, is taking about half a minute. > > Before I start writing my own function, have I overlooked something > already available or some better way to invoke connectby? maybe or maybe not, but here is one way to do it: create or replace function parent(foo) returns foo as $$ select parent(foo) from foo where id = ($1).parent_id union all select $1 limit 1; $$ language sql; create table foo(id int, parent_id int); insert into foo values(1, null); insert into foo values(2, 1); insert into foo values(3, 2); select (parent(foo)).* from foo where id = 3; id | parent_id ----+----------- 1 | (1 row) if you want another general tactic that works pretty well for trees in a lot of workloads check out my array approach here: http://merlinmoncure.blogspot.com/2007/09/one-of-my-favorite-problems-in.html merlin ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
