postgresql 递归查询上下级节点树

1、表结构如下

CREATE TABLE "public"."city" (
  "id" int4 NOT NULL DEFAULT nextval('city_id_seq'::regclass),
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "parent_id" int4
)
;

ALTER TABLE "public"."city" OWNER TO "postgres";

COMMENT ON COLUMN "public"."city"."id" IS '主键';

COMMENT ON COLUMN "public"."city"."name" IS '地区';

COMMENT ON COLUMN "public"."city"."parent_id" IS '主键';

2、插入数据

INSERT INTO "public"."city" VALUES (1, '中国', NULL);
INSERT INTO "public"."city" VALUES (2, '重庆', 1);
INSERT INTO "public"."city" VALUES (3, '四川', 1);
INSERT INTO "public"."city" VALUES (4, '北京', 1);
INSERT INTO "public"."city" VALUES (5, '渝北区', 2);
INSERT INTO "public"."city" VALUES (6, '九龙坡区', 2);
INSERT INTO "public"."city" VALUES (7, '大渡口区', 2);
INSERT INTO "public"."city" VALUES (8, '广安', 3);
INSERT INTO "public"."city" VALUES (9, '成都', 3);
INSERT INTO "public"."city" VALUES (10, '德阳', 3);
INSERT INTO "public"."city" VALUES (11, '邻水县', 8);
INSERT INTO "public"."city" VALUES (12, '九龙镇', 11);
INSERT INTO "public"."city" VALUES (13, '御临镇', 11);
INSERT INTO "public"."city" VALUES (14, '昌平区', 4);
INSERT INTO "public"."city" VALUES (15, '怀柔区', 4);

3、想下递归查询

WITH RECURSIVE r AS (
       SELECT * FROM city WHERE id = 3
     union   ALL
       SELECT city.* FROM city, r WHERE city.parent_id = r.id
     )
SELECT * FROM r ORDER BY id;

结果如下：

4、向上递归查询

WITH RECURSIVE r AS (
       SELECT * FROM city WHERE id = 8
     union   ALL
       SELECT city.* FROM city, r WHERE city.id = r.parent_id
     )
SELECT * FROM r ORDER BY id;

结果如下：