All you need is a function that traverses the tree upwards and returns the
root's id.
create or replace function get_root_name(dept text) returns text as $$
declare
parent text := '';
prev_parent text;
begin
while( parent is not null ) loop
prev_parent := parent;
sele
Hello,
I have a table in my database with multiple, independent linked lists. I would
like to have a query that returns an entire linked list given a node (the node
could be anywhere within the list).
I found on the web an example of how to use CTEs to do this:
http://wiki.postgresql.org/wiki
The pgsql function is compiled and wouldn't know how to handle a table
name as a variable.
If you rewrite the SQL to use the 'EXECUTE' statement I think you could
do this, something along the lines of (untested):
EXECUTE ''INSERT INTO '' || quote_ident(tmplist) || '' (SELECT * FROM
links WHE
tmplist) || '' (SELECT * FROM links
WHERE p=x)'';
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-Original Message-
From: [EMAIL PROTECTED] on behalf of Ben K.
Sent: Sun 4/30/2006 6:29 PM
To: Ray Madigan
Cc: Pgsql-Sql-Owner; Marc G. Fournier; pgsql-sql@postgr
I have a table that I created that implements a linked list. I am not an
expert SQL developer and was wondering if there are known ways to traverse
the linked lists. The table contains many linked lists based upon the head
of the list and I need to extract all of the nodes that make up a list.
I have a table that I created that implements a linked list. I am not an
expert SQL developer and was wondering if there are known ways to traverse
the linked lists. The table contains many linked lists based upon the head
of the list and I need to extract all of the nodes that make up a list. T
