【openGauss/MogDB】一种对数据库的列表型参数进行设置的方式-DA-技术分享

背景

在openGauss/MogDB数据库中，有些数据库参数是列表形式（比如behavior_compat_options、sql_beta_feature、rewrite_rule、plsql_compile_check_options等），如果想对这个列表增加删除值时，需要先查出当前值，然后修改当前值的字符串，再set上去，操作比较麻烦。
本文通过创建一组自定义函数，来对这样的列表型参数进行更简单的设置

函数定义

create function current_setting_as_table (text) returns table(value text)
as $$
select unnest(string_to_array( current_setting($1) ,','));
$$language sql;

create or replace function set_config_add_value (text, text, bool)  returns setOF TEXT
as $$
declare
begin
if not $2=any(string_to_array( current_setting($1) ,',')) then
set_config($1,current_setting($1)||','||$2,$3);
end if;
return query SELECT unnest(string_to_array( current_setting($1) ,','));
end;
$$language plpgsql;

create or replace function set_config_del_value (text, text, bool) returns setOF TEXT
as $$
declare
begin
if  $2=any(string_to_array( current_setting($1) ,',')) then
set_config($1,array_to_string(array_remove(string_to_array( current_setting($1) ,','),$2),','),$3);
end if;
return query SELECT unnest(string_to_array( current_setting($1) ,','));
end;
$$language plpgsql;

使用示例

1.以表格方式查看指定参数的列表值

MogDB=> select * from current_setting_as_table('behavior_compat_options');
              value
---------------------------------
 convert_string_digit_to_numeric
 char_coerce_compat
 proc_outparam_override
 proc_implicit_for_loop_variable
 plpgsql_dependency
 allow_procedure_compile_check
 plstmt_implicit_savepoint
 compat_cursor
 aformat_null_test
 plsql_security_definer
 skip_insert_gs_source
 truncate_numeric_tail_zero
(12 rows)

MogDB=>

2.在指定参数的列表中新增一个值

MogDB=> select set_config_add_value('behavior_compat_options','end_month_calculate',false);
      set_config_add_value
---------------------------------
 convert_string_digit_to_numeric
 char_coerce_compat
 proc_outparam_override
 proc_implicit_for_loop_variable
 plpgsql_dependency
 allow_procedure_compile_check
 plstmt_implicit_savepoint
 compat_cursor
 aformat_null_test
 plsql_security_definer
 skip_insert_gs_source
 truncate_numeric_tail_zero
 end_month_calculate
(13 rows)

MogDB=>

3.在指定参数的列表中移除一个值

MogDB=> select set_config_del_value('behavior_compat_options','end_month_calculate',false);
      set_config_del_value
---------------------------------
 convert_string_digit_to_numeric
 char_coerce_compat
 proc_outparam_override
 proc_implicit_for_loop_variable
 plpgsql_dependency
 allow_procedure_compile_check
 plstmt_implicit_savepoint
 compat_cursor
 aformat_null_test
 plsql_security_definer
 skip_insert_gs_source
 truncate_numeric_tail_zero
(12 rows)

MogDB=>

用到的一些语法或者内置函数说明

returns table/setof 返回表、集合
language sql/plpgsql 函数语言使用sql还是plpgsql，如果是sql语言就只需要select，如果是plpgsql则需要有begin end;
string_to_array函数，将字符串转换成数组类型，第一个参数为原始字符串，第二个参数为分隔符
unnest函数，将一个数组展开成一个表（多行数据）
current_setting函数，指定参数名称获取参数值，和show的结果一样，但更加适用于在sql/plsql中使用
set_config函数，设置某个参数的值。当第三个参数为true时，仅作用于当前事务；当第三个参数为false时，和set作用一样。也是适用于在sql/plsql中使用
text=any(array) ，当某个值存在于数组中，返回true，否则返回false
return query,当返回setof时，可以使用return next 返回单行，或者使用return query 返回一个查询的结果集（多行）