GreenPlum 函数实现多规则替换

应用场景

1. 对一个字符串，想做如下替换：a替换成1，b替换成2，c替换成3等等，有多少个这样的替换规则，我们就需要用replace嵌套多少层；
2. 把字符串中的替代变量替换成指定的值；

函数实现

二维数组的初始化

由于plpgsql中的数组不是在声明的时候分配内存，而是在第一次初始化之后分配内存并确定了数组的大小，所以写了一个函数来初始化指定text类型的二维数组；

create or replace function array2_text_init(array_1_dim integer, array_2_dim integer)
 returns text[]
 language plpgsql
as $function$
/*
 * 作者：v-yuzhenc
 * 功能：text类型二维数组初始化（分配内存）
 * array_1_dim：维度1
 * array_2_dim：维度2
 * */
declare 
	p_result text[][];
	v_sql text := ''; 
	model text;
	num int := 0;
begin 
	if array_1_dim <= 0 or array_2_dim <= 0 then 
		raise notice '参数值必须大于0！';
	end if;
	
	for i in 1 .. array_2_dim loop 
		num := num + 1;
		if num = 1 then 
			v_sql := v_sql||'''''::text';
		else 
			v_sql := v_sql||',''''::text';
		end if;
	end loop;
	v_sql := 'array['||v_sql||']';
	model := v_sql;
	num := 0;  --num初始化
	for i in 1 .. array_1_dim loop 
		num := num + 1;
		if num = 1 then 
			v_sql := v_sql;
		else 
			v_sql := v_sql||','||model;
		end if;
	end loop;
	v_sql := 'array['||v_sql||']';
	execute 'select '||v_sql into p_result;
	return p_result;
end;
$function$
;

实现批量替换

create or replace function replace_varstr(
	 replace_text text
	,var_val_sep text default ':'::text
	,var_flag boolean default false
	,variadic replace_rule text[] default null::text[]
)
 returns text
 language plpgsql
as $function$
/*
 * 作者：v-yuzhenc
 * 功能：指定文本按一定的规则进行替换或者变量替换，变量支持${}和psql的:方式
 * replace_text：指定文本
 * var_val_sep：替换前的值（或变量）与替代的值的分隔符
 * var_flag：是否为替换变量
 * replace_rule：替换规则
 * 例如：select 'ab${C}d${E}fg:H',replace_varstr('ab${C}d${E}fg:H','|',true,'C|1','E|2','H|3');
 * */
declare 
	p_replace_text text := replace_text;  --接收参数值replace_text
	p_var_val_sep text := var_val_sep;  --接收参数值var_val_sep
	p_replace_rule text[] := replace_rule;  --接收参数值replace_rule
	p_result text := p_replace_text;  --最后的返回结果
	p_replace_rule_len int := array_length(p_replace_rule,1);
	i record;  --用于隐式游标
	varval text[][];  --二维数组存放解析的替换规则
begin 
	--如果替换规则为空，则直接返回原字符串
	if p_replace_rule is null then 
		return p_result;
	end if;
	--初始化二维数组
	varval := array2_text_init(p_replace_rule_len,2);
	--如果替换规则不为空，则检测替换规则的规范
	for i in 1 .. p_replace_rule_len loop 
		if instr(p_replace_rule[i],p_var_val_sep,1,2) <> 0 then 
			raise exception '替换规则的替换前的值（或变量）与替换后的值必须按指定分隔符分隔，并且替换前的值（或变量）与替换后的值中不应存在该指定分隔符！';
		end if;
		--替换规则存放到二维数组
		--varval[i] := string_to_array(p_replace_rule[i],p_var_val_sep);
		varval[i][1] := split_part(p_replace_rule[i],p_var_val_sep,1);
		varval[i][2] := split_part(p_replace_rule[i],p_var_val_sep,2);
	end loop;
	--规范都检测通过后，开始进行替换
	--变量支持2种方式${变量名}或者:变量名
	if var_flag then 
		for i in 1 .. array_length(varval,1) loop 
			--raise notice '%,%',varval[i][1],varval[i][2];
			p_result := replace(replace(p_result,'${'||varval[i][1]||'}',varval[i][2]),':'||varval[i][1],varval[i][2]);
		end loop;
	else 
		for i in 1 .. array_length(varval,1) loop 
			--raise notice '%,%',varval[i][1],varval[i][2];
			p_result := replace(p_result,varval[i][1],varval[i][2]);
		end loop;
	end if;
	return p_result;
end;
$function$
;

测试

测试用例1

select 'abcdefgh',replace_varstr('abcdefgh',':',false,'a:1','b:2','c:3','d:4');

测试用例2

select 'ab${C}d${E}fg:H',replace_varstr('ab${C}d${E}fg:H','|',true,'C|1','E|2','H|3');