Re: Linked List - Mailing list pgsql-sql

From Ben K.
Subject Re: Linked List
Date
Msg-id Pine.GSO.4.64.0604302014590.22531@coe.tamu.edu
Whole thread Raw
In response to Linked List  ("Ray Madigan" <ray@madigans.org>)
List pgsql-sql
> 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.  The
> lists are simple with a item and a link to the history item so it goes kind
> of like:
>
> 1, 0
> 3, 1
> 7, 3
> 9, 7
> ...

I missed "The table contains many linked lists", so wanted to do another 
try. I guess there should be a better way, but what if you do this?

1)

Assuming your table has two columns (n int, p int), do

create table tmplist (n int, p int);

2)

drop function traverse(integer);
create or replace function traverse (integer)
returns integer as
$$  declare    x int;  begin    x := $1;    while x is not null loop      select n into x from linkedlist where p = x;
   insert into tmplist (select * from links where p=x);
 
-- or do any processing    end loop;    return 1 ;  end;
$$
language plpgsql;

3)

select traverse(0);
select * from tmplist;
0 - 1 - 4 - 8 - 12 ...


delete from tmplist;
select traverse(2);
select * from tmplist;
2 - 3 - 5 - 6 - ...

(where 0 or 2 is the heads of the linked lists in the table, which you 
want to traverse)

I'd appreciate any insight if there's a better way but somehow it was not 
possible to return setof int from within while loop whereas it was 
possible from within a for loop. I didn't find a way to deliver the 
templist table name as argument. (Somehow there seemed to be a bug(?) 
there)



Regards,

Ben K.
Developer
http://benix.tamu.edu


pgsql-sql by date:

Previous
From: "Tornroth, Phill"
Date:
Subject: Re: Multi-Column Constraints and Null Values
Next
From: "Gregory S. Williamson"
Date:
Subject: Re: Linked List