Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C256A63EA3F for ; Sun, 14 Mar 2010 20:58:48 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 82330-04 for ; Sun, 14 Mar 2010 23:58:37 +0000 (UTC) X-Greylist: delayed 00:06:38.790479 by SQLgrey-1.7.6 Received: from smtpauth17.prod.mesa1.secureserver.net (smtpauth17.prod.mesa1.secureserver.net [64.202.165.29]) by mail.postgresql.org (Postfix) with SMTP id C25A163E84A for ; Sun, 14 Mar 2010 20:58:37 -0300 (ADT) Received: (qmail 26530 invoked from network); 14 Mar 2010 23:51:55 -0000 Received: from unknown (69.51.126.142) by smtpauth17.prod.mesa1.secureserver.net (64.202.165.29) with ESMTP; 14 Mar 2010 23:51:55 -0000 From: Mark Lubratt Content-Type: multipart/alternative; boundary=Apple-Mail-1-393221579 Subject: Linked list with CTE Date: Sun, 14 Mar 2010 18:51:53 -0500 Message-Id: To: pgsql-sql@postgresql.org Mime-Version: 1.0 (Apple Message framework v1077) X-Mailer: Apple Mail (2.1077) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/72 X-Sequence-Number: 33779 --Apple-Mail-1-393221579 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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/CTEReadme I'll repeat the gist of it here: CREATE TABLE department ( id INTEGER PRIMARY KEY, -- department ID parent_department INTEGER REFERENCES department, -- upper department = ID name TEXT -- department name ); INSERT INTO department (id, parent_department, "name") VALUES (0, NULL, 'ROOT'), (1, 0, 'A'), (2, 1, 'B'), (3, 2, 'C'), (4, 2, 'D'), (5, 0, 'E'), (6, 4, 'F'), (7, 5, 'G'); -- department structure represented here is as follows: -- -- ROOT-+->A-+->B-+->C -- | | -- | +->D-+->F -- +->E-+->G To extract all departments under A, you can use the following recursive = query: WITH RECURSIVE subdepartment AS ( -- non-recursive term SELECT * FROM department WHERE name =3D 'A' UNION ALL -- recursive term SELECT d.* FROM department AS d JOIN subdepartment AS sd ON (d.parent_department =3D sd.id) ) SELECT * FROM subdepartment ORDER BY name; My database contains multiple, independent structures like the one given = above. So, I can modify the above with: insert into department (id, parent_department, name) values (8, NULL, = 'Z'), (9, 8, 'Y'); I need a bidirectional query and since I'm quite new to CTE, I'm not = sure how to modify the query to get parent departments as well as = subdepartments... Thus, if I give the query any node in a linked list, = I'd like the entire tree returned. e.g. If I give the query 'A', I'd like it to return the ROOT, A, B, C, = D, E, F, G tree. If I give the query 'Y', I'd like it to return the Z, = Y tree. I hope I made sense... Thanks! Mark --Apple-Mail-1-393221579 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii http://wiki.postgresql.= org/wiki/CTEReadme

I'll repeat the gist of = it here:

CREATE TABLE = department ( id INTEGER PRIMARY KEY, -- department ID parent_department INTEGER REFERENCES department, -- upper department = ID name TEXT -- department name ); INSERT INTO department (id, parent_department, "name") VALUES (0, NULL, 'ROOT'), (1, 0, 'A'), (2, 1, 'B'), (3, 2, 'C'), (4, 2, 'D'), (5, 0, 'E'), (6, 4, 'F'), (7, 5, 'G'); -- department structure represented here is as follows: -- -- ROOT-+->A-+->B-+->C -- | | -- | +->D-+->F -- +->E-+->G

To extract all = departments under A, you can use the following recursive = query:

WITH RECURSIVE subdepartment AS
(
    -- non-recursive term
    SELECT * FROM department WHERE name =3D 'A'

    UNION ALL

    -- recursive term
    SELECT d.*
    FROM
        department AS d
    JOIN
        subdepartment AS sd
        ON (d.parent_department =3D sd.id)
)
SELECT *
FROM subdepartment
ORDER BY name;

My database = contains multiple, independent structures like the one given above. = So, I can modify the above with:

insert = into department (id, parent_department, name) values (8, NULL, 'Z'), (9, = 8, 'Y');

I need a bidirectional query and since = I'm quite new to CTE, I'm not sure how to modify the query to get parent = departments as well as subdepartments... Thus, if I give the query = any node in a linked list, I'd like the entire tree = returned.

e.g. If I give the query 'A', = I'd like it to return the ROOT, A, B, C, D, E, F, G tree. If I = give the query 'Y', I'd like it to return the Z, Y = tree.

I hope I made = sense...

Thanks!

Mark

= --Apple-Mail-1-393221579--