pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedLinked List
4+ messages / 3 participants
[nested] [flat]
* Linked List
@ 2006-04-26 02:50 Ray Madigan <ray@madigans.org>
2006-05-01 01:29 ` Re: Linked List Ben K. <bkim@coe.tamu.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Ray Madigan @ 2006-04-26 02:50 UTC (permalink / raw)
To: Pgsql-Sql-Owner <pgsql-sql-owner@postgresql.org>
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
...
Any suggestions would be helpful, or I will have to implement the table
differently.
Thanks
Ray Madigan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Linked List
2006-04-26 02:50 Linked List Ray Madigan <ray@madigans.org>
@ 2006-05-01 01:29 ` Ben K. <bkim@coe.tamu.edu>
2006-05-01 01:47 ` Re: Linked List Gregory S. Williamson <gsw@globexplorer.com>
0 siblings, 1 reply; 4+ messages in thread
From: Ben K. @ 2006-05-01 01:29 UTC (permalink / raw)
To: Ray Madigan <ray@madigans.org>; +Cc: Pgsql-Sql-Owner <pgsql-sql-owner@postgresql.org>; Marc G. Fournier <scrappy@hub.org>; 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Linked List
2006-04-26 02:50 Linked List Ray Madigan <ray@madigans.org>
2006-05-01 01:29 ` Re: Linked List Ben K. <bkim@coe.tamu.edu>
@ 2006-05-01 01:47 ` Gregory S. Williamson <gsw@globexplorer.com>
2006-05-01 04:49 ` Re: Linked List Ben K. <bkim@coe.tamu.edu>
0 siblings, 1 reply; 4+ messages in thread
From: Gregory S. Williamson @ 2006-05-01 01:47 UTC (permalink / raw)
To: Ben K. <bkim@coe.tamu.edu>; Ray Madigan <ray@madigans.org>; +Cc: Pgsql-Sql-Owner <pgsql-sql-owner@postgresql.org>; Marc G. Fournier <scrappy@hub.org>; pgsql-sql
Ben,
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 WHERE p=x)'';
HTH,
Greg Williamson
DBA
GlobeXplorer LLC
-----Original Message-----
From: pgsql-sql-owner@postgresql.org 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@postgresql.org
Subject: Re: [SQL]Linked 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. 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
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
!DSPAM:445564c2225761179214242!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Linked List
2006-04-26 02:50 Linked List Ray Madigan <ray@madigans.org>
2006-05-01 01:29 ` Re: Linked List Ben K. <bkim@coe.tamu.edu>
2006-05-01 01:47 ` Re: Linked List Gregory S. Williamson <gsw@globexplorer.com>
@ 2006-05-01 04:49 ` Ben K. <bkim@coe.tamu.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Ben K. @ 2006-05-01 04:49 UTC (permalink / raw)
To: Gregory S. Williamson <gsw@globexplorer.com>; +Cc: pgsql-sql
> 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 WHERE p=x)'';
Thanks. Yet, if I give the table name as the argument, I get the same
error.
drop function traverse(integer, text);
create or replace function traverse (integer, text)
returns integer as
$$
declare
x int;
tname alias for $2;
begin
x := $1;
while x is not null loop
select n into x from links where p = x;
insert into tmplink (select * from links where p=x);
EXECUTE ''INSERT INTO '' || quote_ident(tname) || '' (SELECT * FROM
links WHERE p=x)'';
end loop;
return 1 ;
end;
$$
language plpgsql;
The above gives the following error. Please note that the first and
second args are mixed up now. I tested it only on 8.0 (FC 4) and didn't on
8.1 and wonder whether it's a bug or I may be doing something wrong.
Using tname or $2 doesn't change the result.
========================================================================
# select traverse(0, 'links2');
ERROR: syntax error at or near "INSERT" at character 11
QUERY: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT * FROM
links WHERE p= $2 )''
CONTEXT: PL/pgSQL function "traverse" line 10 at execute statement
LINE 1: SELECT ''INSERT INTO '' || quote_ident( $1 ) || '' (SELECT ...
========================================================================
Regards,
Ben K.
Developer
http://benix.tamu.edu
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-05-01 04:49 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-26 02:50 Linked List Ray Madigan <ray@madigans.org>
2006-05-01 01:29 ` Ben K. <bkim@coe.tamu.edu>
2006-05-01 01:47 ` Gregory S. Williamson <gsw@globexplorer.com>
2006-05-01 04:49 ` Ben K. <bkim@coe.tamu.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox