PostgreSQL recursive query for tree structure query and postgresql Recursion

Source: Internet
Author: User

PostgreSQL recursive query for tree structure query and postgresql Recursion

A very interesting feature found during Postgresql's use is that recursive queries can be used for results that require a tree-like structure. For example, the data structure of common company departments is similar to the following SQL statement when we design the table structure. If parent_id is NULL, it indicates the top-level node. Otherwise, it indicates the ID of the parent node.

CREATE TABLE DEPARTMENT ( ID INTEGER PRIMARY KEY, NAME VARCHAR(32), PARENT_ID INTEGER REFERENCES DEPARTMENT(ID));

Below we create several pieces of test data

INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(1, 'DEPARTMENT_1', NULL);INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(11, 'DEPARTMENT_11', 1);INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(12, 'DEPARTMENT_12', 1);INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(111, 'DEPARTMENT_111', 11);INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(121, 'DEPARTMENT_121', 12);INSERT INTO DEPARTMENT(ID, NAME, PARENT_ID) VALUES(122, 'DEPARTMENT_122', 12);

Where
-DEPARTMENT_1 is a top-level node with two subnodes: DEPARTMENT_11 and DEPARTMENT_12.
-The DEPARTMENT_11 node has another subnode, DEPARTMENT_111.
-The DEPARTMENT_12 node has two subnodes: DEPARTMENT_121 and DEPARTMENT_122.

The following statements are used to generate a tree structure for Recursive queries:

WITH RECURSIVE T (ID, NAME, PARENT_ID, PATH, DEPTH)  AS (    SELECT ID, NAME, PARENT_ID, ARRAY[ID] AS PATH, 1 AS DEPTH    FROM DEPARTMENT    WHERE PARENT_ID IS NULL    UNION ALL    SELECT  D.ID, D.NAME, D.PARENT_ID, T.PATH || D.ID, T.DEPTH + 1 AS DEPTH    FROM DEPARTMENT D    JOIN T ON D.PARENT_ID = T.ID    )    SELECT ID, NAME, PARENT_ID, PATH, DEPTH FROM TORDER BY PATH;
ID  NAME            PARENT_ID   PATH      DEPTH1   DEPARTMENT_1                1         111  DEPARTMENT_11   1           1,11      2111 DEPARTMENT_111  11          1,11,111  312  DEPARTMENT_12   1           1,12      2121 DEPARTMENT_121  12          1,12,121  3122 DEPARTMENT_122  12          1,12,122  3

Reprinted please mark the address of this article as a link
Address: http://blog.csdn.net/kongxx/article/details/47035491

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.