Re: Path to top of tree

On Nov 13, 2007 10:54 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> 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

here is another way to write the function that might be a little bit faster:

create or replace function parent(foo) returns foo as
$$
select case
when ($1).parent_id is null then $1
else (select parent(foo) from foo where id = ($1).parent_id)
end;
$$ language sql;

merlin