PostgreSQL-Hierarchical-Query.sql

-- ----------------------------
-- Table structure for member
-- ----------------------------
DROP TABLE IF EXISTS "public"."member";
CREATE TABLE "public"."member" (
  "id" int4 NOT NULL,
  "no_urut" int4,
  "parent_id" int4,
  "name" varchar(255) COLLATE "pg_catalog"."default"
)
;
ALTER TABLE "public"."member" OWNER TO "postgres";

-- ----------------------------
-- Records of member
-- ----------------------------
BEGIN;
INSERT INTO "public"."member" VALUES (1, 1, null, 'Agung');
INSERT INTO "public"."member" VALUES (2, 2, null, 'Bambang');
INSERT INTO "public"."member" VALUES (3, 3, null, 'Joko');
INSERT INTO "public"."member" VALUES (4, 1, 1, 'Adi');
INSERT INTO "public"."member" VALUES (5, 2, 1, 'Bagus');
INSERT INTO "public"."member" VALUES (6, 3, 1, 'Rojak');
INSERT INTO "public"."member" VALUES (7, 1, 2, 'Tiyo');
INSERT INTO "public"."member" VALUES (8, 1, 3, 'Dadang');
INSERT INTO "public"."member" VALUES (9, 1, 4, 'Dimas');
INSERT INTO "public"."member" VALUES (10, 2, 4, 'Rohmen');
COMMIT;

-- ----------------------------
-- Primary Key structure for table member
-- ----------------------------
ALTER TABLE "public"."member" ADD CONSTRAINT "member_pkey" PRIMARY KEY ("id");


-- ----------------------------
-- ierarchical query for data above
-- ----------------------------
WITH RECURSIVE q AS (
    SELECT h.*::member                             AS h,
           1                                       AS level,
           ARRAY []::integer[] || h.id             AS id_breadcrumb,
           ARRAY []::integer[] || h.no_urut AS no_urut_breadcrumb,
           ARRAY []::character varying[] || h.name AS name_breadcrumb
    FROM member h
    WHERE h.parent_id IS NULL
    UNION ALL
    SELECT hi.*::member AS hi,
           q_1.level + 1  AS level,
           q_1.id_breadcrumb || hi.id,
           q_1.no_urut_breadcrumb || hi.no_urut,
           q_1.name_breadcrumb::character varying(255)[] || hi.name
    FROM q q_1
             JOIN member hi ON hi.parent_id = (q_1.h).id
)
SELECT (q.h).id                                        AS id,
       q.id_breadcrumb[1]                              AS id_level1,
       q.id_breadcrumb[2]                              AS id_level2,
       q.id_breadcrumb[3]                              AS id_level3,
       q.id_breadcrumb[4]                              AS id_level4,
       q.id_breadcrumb::character varying              AS id_path,
       (q.h).parent_id                                 AS parent_id,
       q.level,
       (q.h).no_urut                                   AS no_urut,
       (q.h).name                                      AS name,
       q.name_breadcrumb[1]                            AS name_level1,
       q.name_breadcrumb[2]                            AS name_level2,
       q.name_breadcrumb[3]                            AS name_level3,
       q.name_breadcrumb[4]                            AS name_level4,
       q.name_breadcrumb::character varying            AS name_path
FROM q
ORDER BY q.no_urut_breadcrumb;