Would recursive queries be the trick to doing things like unwinding a
linked-list to either the head or tail, with:
create table list ( id int primary key, parent int references list(id));
insert into list values (1, null); -- head of chain in listinsert into list values (2, 1); -- 1st childinsert into
listvalues (3, 2); -- second child
Given a reference to id=3, would a recursive query be the trick to
unrolling the list to discover id=1 as the head using a SQL one-liner?
Is discovery possible in straight SQL w/o resorting to stored
procedures (or modifying the table schema to directly point)? And,
finally, would any potential recursive query implementation be
noticably more efficient that a straightforward implementation in
plpgsql, such as:
create or replace function find_head(int) returns int as 'DECLARE cur_par INT; prev_par INT;BEGIN prev_par :=
$1; cur_par := parent from list where id = $1; WHILE cur_par is not null LOOP prev_par := cur_par;
cur_par:= parent from list where id = prev_par; END LOOP; return prev_par;END;
' language 'plpgsql';
----
James Robinson
Socialserve.com
