From: Mark Lubratt <mark.lubratt@indeq.com>
To: pgsql-sql@postgresql.org
Subject: Linked list with CTE
Date: Sun, 14 Mar 2010 18:51:53 -0500
Message-ID: <ECD5EC35-ACAD-4D12-9505-620D86081F8B@indeq.com> (raw)
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 = 'A'
UNION ALL
-- recursive term
SELECT d.*
FROM
department AS d
JOIN
subdepartment AS sd
ON (d.parent_department = 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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mark.lubratt@indeq.com
Subject: Re: Linked list with CTE
In-Reply-To: <ECD5EC35-ACAD-4D12-9505-620D86081F8B@indeq.com>
* 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