Re: Path to top of tree

On Nov 13, 2007 10:54 PM, Merlin Moncure <mmoncure@gmail.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