[SQL] Utility of recursive queries?

[James Robinson]

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 list
        insert into list values (2, 1); -- 1st child
        insert into list values (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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])