pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Gregory S. Williamson <gsw@globexplorer.com>
To: Ben K. <bkim@coe.tamu.edu>
To: Ray Madigan <ray@madigans.org>
Cc: Pgsql-Sql-Owner <pgsql-sql-owner@postgresql.org>
Cc: Marc G. Fournier <scrappy@hub.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: Linked List
Date: Sun, 30 Apr 2006 18:47:47 -0700
Message-ID: <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net> (raw)
References: <IEELLOAPJFACBDBGELGHKEJPEDAA.ray@madigans.org>
	<Pine.GSO.4.64.0604302014590.22531@coe.tamu.edu>

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!







view thread (4+ messages)  latest in thread

Message-ID: <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>
Permalink:  ../71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net/
Also on:    postgresql.org/message-id/71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gsw@globexplorer.com, bkim@coe.tamu.edu, ray@madigans.org, pgsql-sql-owner@postgresql.org, scrappy@hub.org
  Subject: Re: Linked List
  In-Reply-To: <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>
  References: <IEELLOAPJFACBDBGELGHKEJPEDAA.ray@madigans.org> <Pine.GSO.4.64.0604302014590.22531@coe.tamu.edu> <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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