1.建表

--城市表createtable country(Parent_id varchar(100),Parent_name varchar(500),Child_id varchar(100),Child_name varchar(500));/*数据来源网络https://baike.baidu.com/item/%E6%9D%AD%E5%B7%9E/147639#reference-[172]-18593808-wrap*/INSERTINTO COUNTRY VALUES('100','浙江省','1001','杭州市');INSERTINTO COUNTRY VALUES('100','浙江省','1002','温州市');INSERTINTO COUNTRY VALUES('100','浙江省','1003','嘉兴市');INSERTINTO COUNTRY VALUES('100','浙江省','1004','绍兴区');INSERTINTO COUNTRY VALUES('1001','杭州市','10011','上城区');INSERTINTO COUNTRY VALUES('1001','杭州市','10012','拱墅区');INSERTINTO COUNTRY VALUES('1001','杭州市','10013','西湖区');INSERTINTO COUNTRY VALUES('1001','杭州市','10014','滨江区');INSERTINTO COUNTRY VALUES('1001','杭州市','10015','萧山区');INSERTINTO COUNTRY VALUES('1001','杭州市','10016','余杭区');INSERTINTO COUNTRY VALUES('1001','杭州市','10017','临平区');INSERTINTO COUNTRY VALUES('1001','杭州市','10018','钱塘区');INSERTINTO COUNTRY VALUES('1001','杭州市','10019','富阳区');INSERTINTO COUNTRY VALUES('1001','杭州市','10020','临安区');INSERTINTO COUNTRY VALUES('1001','杭州市','10021','建德市');INSERTINTO COUNTRY VALUES('1001','杭州市','10022','桐庐县');INSERTINTO COUNTRY VALUES('1001','杭州市','10023','淳安县');INSERTINTO COUNTRY VALUES('10011','上城区','100111','望江街道');INSERTINTO COUNTRY VALUES('10012','拱墅区','100121','拱宸桥街道');INSERTINTO COUNTRY VALUES('10013','西湖区','100131','西湖街道');INSERTINTO COUNTRY VALUES('10014','滨江区','100141','西兴街道');INSERTINTO COUNTRY VALUES('10015','萧山区','100151','北干街道');INSERTINTO COUNTRY VALUES('10016','余杭区','100161','仓前街道');INSERTINTO COUNTRY VALUES('10017','临平区','100171','临平街道');INSERTINTO COUNTRY VALUES('10018','钱塘区','100181','河庄街道');INSERTINTO COUNTRY VALUES('10019','富阳区','100191','富春街道');INSERTINTO COUNTRY VALUES('10020','临安区','100201','锦城街道');INSERTINTO COUNTRY VALUES('10021','建德市','100211','新安江街道');INSERTINTO COUNTRY VALUES('10022','桐庐县','100221','城南街道');INSERTINTO COUNTRY VALUES('10023','淳安县','100231','千岛湖镇');--人员表createtable person(street_id varchar(100),street_name varchar(500),person_id varchar(100),person_name varchar(500));insertinto person values('100131','西湖街道','1001311','张三');insertinto person values('100141','西兴街道','1001411','李四');

2.创建函数

CREATEORREPLACEFUNCTION getInfluencedPerson(src_name varchar(100))RETURNS SETOF person AS $$
BEGIN/*创建临时表*/CREATE TEMP TABLEIFNOTEXISTS street(  Street_Id varchar(100),  Street_name varchar(500))ONCOMMIT PRESERVE rows;/*同一session可能调用多次，删除历史无效数据*/DELETEFROM street;/*获取受影响的街道*/INSERTINTO street
WITH RECURSIVE city AS(SELECTParent_id,Parent_name,Child_id,Child_name
FROM COUNTRY
WHERE Parent_name = src_name
UNIONALLSELECTchild.Parent_id,child.Parent_name,child.Child_id,child.Child_name
FROM COUNTRY child
INNER JOIN city AS parent ONchild.Parent_id = parent.Child_id
)SELECT Child_id
, Child_name
FROM city
GROUPBY Child_id, Child_name
;/*返回受影响的人*/RETURN QUERY 
SELECT S.Street_Id
, S.street_name
, S.Person_Id
, S.person_name
FROM street T
INNER JOIN person S ONT.Street_Id = S.Street_Id;END;$$ LANGUAGE PLPGSQL;

3.测试用例

SELECT*FROM getInfluencedPerson('浙江省');

SELECT*FROM getInfluencedPerson('杭州市');

SELECT*FROM getInfluencedPerson('温州市');

SELECT*FROM getInfluencedPerson('滨江区');

SELECT*FROM getInfluencedPerson('西湖区');

SELECT*FROM getInfluencedPerson('萧山区');

4.结束