Linked list with CTE

Поиск
Список
Период
Сортировка
От Mark Lubratt
Тема Linked list with CTE
Дата
Msg-id ECD5EC35-ACAD-4D12-9505-620D86081F8B@indeq.com
обсуждение исходный текст
Ответы Re: Linked list with CTE
Список pgsql-sql
Дерево обсуждения
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:


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

В списке pgsql-sql по дате отправления: