X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EA9229FB1C6; Sun, 30 Apr 2006 22:42:42 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 83317-04; Sun, 30 Apr 2006 22:42:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- X-Greylist: from auto-whitelisted by SQLgrey- Received: from loki.globexplorer.com (loki.globexplorer.com [12.25.176.46]) by postgresql.org (Postfix) with ESMTP id B8E849FB1C1; Sun, 30 Apr 2006 22:42:27 -0300 (ADT) X-MimeOLE: Produced By Microsoft Exchange V6.0.6603.0 Content-class: urn:content-classes:message Subject: Re: Linked List MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Date: Sun, 30 Apr 2006 18:47:47 -0700 Message-ID: <71E37EF6B7DCC1499CEA0316A2568328024BC3D4@loki.wc.globexplorer.net> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL]Linked List thread-index: AcZsv56C5w4kcQDyS8+IwnKOzjN75QAAOa2i References: From: "Gregory S. Williamson" To: "Ben K." , "Ray Madigan" Cc: "Pgsql-Sql-Owner" , "Marc G. Fournier" , X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/222 X-Sequence-Number: 25031 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=3Dx)''; 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 :=3D $1; while x is not null loop select n into x from linkedlist where p =3D x; insert into tmplist (select * from links where p=3Dx); -- 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=20 want to traverse) I'd appreciate any insight if there's a better way but somehow it was = not=20 possible to return setof int from within while loop whereas it was=20 possible from within a for loop. I didn't find a way to deliver the=20 templist table name as argument. (Somehow there seemed to be a bug(?)=20 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!