pgsql-递归查询行政区划表，只保留某一地区范围内的数据

需求：有一张行政区划表，想只保留中国区域的，中国之外的全部清除 

1、表结构 

2、查询出所有【中国区域】的行政区划（递归查询）

WITH RECURSIVE r AS (
SELECT * FROM tbl_nation_area WHERE id = '185'
union   ALL
SELECT na.* FROM tbl_nation_area na, r WHERE na.pid = r.id 
)
SELECT id FROM r ORDER BY id

3、删除除【中国区域】以外的其它地区

DELETE FROM cm_nation_area WHERE id not in(WITH RECURSIVE r AS (
SELECT * FROM cm_nation_area WHERE id = '185'
union   ALL
SELECT cna.* FROM cm_nation_area cna, r WHERE cna.pid = r.id 
)
SELECT id FROM r ORDER BY id);