PostgreSQL函数中嵌套正则表达式替换的替代方案

Question

现在，我看到了一个视图，其中包含一堆常见的、有条件的字符串替换和对打开文本字段的替换——在本例中是区域分类。

(请忽略地理的准确性，我只是在处理历史标准作业。此外，我知道我可以通过REPLACE来加快速度，甚至只是清理RegEx语句以进行回顾-我只是在这里询问变量/嵌套。(

CREATE OR REPLACE FUNCTION public.region_cleanup(record_region text)
RETURNS text
LANGUAGE sql
STRICT
AS $function$
SELECT  REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(
REGEXP_REPLACE(record_region,'(NORTH AMERICAs-sUSAs-sUSA)','USA')
,'RestsofsthesWorlds-s','')
,'NORTHsAMERICAs-sCANADA','NORTH AMERICA - Canada')
,'&','&')
,'Georgias-sGeorgia','MIDDLE EAST - Georgia')
,'EUROPE - Turkey','MIDDLE EAST - Turkey')

在我的数据集中，使用此函数的样本输出看起来像这样，提取出受影响的记录(有些已经是正确的格式(：

东-格鲁吉亚

record_region_input
北美-美国-东北部-马萨诸塞州-波士顿地铁		北美-美国-美国-中大西洋-弗吉尼亚	美国-中太平洋-弗吉尼亚
世界其他地区-亚洲-泰国
世界其他地区-欧洲-葡萄牙
世界其他地区-亚洲-中国-上海地铁
格鲁吉亚-格鲁吉亚

如果您将一系列数据拆分为其他区域，那么替换区域对您来说可能很容易。例如：

with tb as (
select 1 as id, 'NORTH AMERICA - USA - USA - NORTHEAST - Massachusetts - Boston Metro' as record_region_input
union all 
select 2 as id, 'NORTH AMERICA - USA - USA - MIDATLANTIC - Virginia'
union all 
select 3 as id, 'Rest of the World - ASIA - China - Shanghai Metro' 
)
select * from (
select distinct tb.id, unnest(string_to_array(record_region_input, ' - ')) as region from tb 
order by tb.id 
) a1 where a1.region not in ('NORTH AMERICA', 'Rest of the World');
-- Result: 
1   Boston Metro
1   Massachusetts
1   NORTHEAST
1   USA
2   MIDATLANTIC
2   USA
2   Virginia
3   ASIA
3   China
3   Shanghai Metro

之后，例如，对于复制区域，可以使用distinct，对于不必要的区域，可以在中使用NOT，并且可以使用"%ASIA%"来获取包含ASIA等的所有区域。在所有过程之后，可以再次合并更正的字符串。示例：

with tb as (
select 1 as id, 'NORTH AMERICA - USA - USA - NORTHEAST - Massachusetts - Boston Metro' as record_region_input
union all 
select 2 as id, 'NORTH AMERICA - USA - USA - MIDATLANTIC - Virginia'
union all 
select 3 as id, 'Rest of the World - ASIA - China - Shanghai Metro' 
)
select a1.id, string_agg(a1.region, ' - ')  from (
select distinct tb.id, unnest(string_to_array(record_region_input, ' - ')) as region from tb 
order by tb.id 
) a1 where a1.region not in ('NORTH AMERICA', 'Rest of the World')
group by a1.id 
-- Return: 
1   Boston Metro - Massachusetts - NORTHEAST - USA
2   MIDATLANTIC - USA - Virginia
3   ASIA - China - Shanghai Metro

这是一个简单的想法，也许这个想法可以帮助你替换区域。