Oracle国产化改造迁移openGauss时的问题: 自定义聚合函数wm_concat
在Oracle 11g升级到更高版本时，默认不再提供 wm_concat 函数，而是用 listagg 函数替代。然而，很多应用程序在12c或19c中可能自定义了类似 wm_concat 的函数，例如 my_wm_concat。这些函数被广泛使用在应用程序中。当这些应用程序的数据库迁移到国产数据库如OpenGauss或PostgreSQL时，如果希望数据库层面兼容而不修改应用代码，通常迁移工具只能做语句规则替换。
对于自定义函数的迁移，由于人工改写时对Oracle或OpenGauss/PostgreSQL不熟悉，这可能会浪费一些时间。 简单纪录一个案例。
Oracle的自定义聚合函数
在2011年时简单体验过oracle的自定义函数，并记录了笔记《ORACLE 对象类型 ODCIAggregate 自定义聚合函数》，不再过多描述， 需要创建自定义type使用ODCI相关的函数如ODCIAggregateInitialize，如果研究在PG/OG中相对应的Function可能会比较困难，所以这也是建议对于Oracle 到其它数据库迁移改造过程中，对于Plsql对象的改写，不只是SQL语法的替换，而是要熟悉语义功能，也需要在目标数据库已经有相同的功能实现。
这个问题是，在确认了oracle端实现的是类似wm_concat功能函数后，开始找PG/OG端的功能。
Postgresql/Opengauss数据库中的wm_concat函数
在POSTGRESQL中有string_agg函数，在EDB中支持了listagg，在opengauss系如MogDB中及支持string_agg,还有listagg，也自带了wm_concat，但是pg/og不支持像oracle一样的synonym给个别名， 还里需要创建函数。先看看OG中的相关函数，这里使用的是Mogdb.
MogDB=# \df+ wm_concat List of functions Schema | Name | Result data type | Argument data types | Type | Volatility | Owner | Language | Source code | Description | fencedmode | propackage | prokind ------------+-----------+------------------+---------------------+------+------------+-------+----------+-----------------+-------------+------------+------------+--------- pg_catalog | wm_concat | text | text | agg | immutable | omm | internal | aggregate_dummy | | f | f | f (1 row) MogDB=# \da+ wm_concat List of aggregate functions Schema | Name | Result data type | Argument data types | Description ------------+-----------+------------------+---------------------+------------- pg_catalog | wm_concat | text | text | (1 row) SELECT p.proname AS function_name, pg_catalog.pg_get_function_result(p.oid) AS result_type, pg_catalog.pg_get_function_arguments(p.oid) AS argument_types, CASE WHEN p.proisagg THEN 'agg' ELSE 'normal' END AS function_type ,a.aggtransfn,a.aggfinalfn FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_aggregate a ON a.aggfnoid = p.oid WHERE a.aggfnoid IS NOT NULL AND p.proname like 'wm_concat%'; function_name | result_type | argument_types | function_type | aggtransfn | aggfinalfn ---------------+-------------+----------------+---------------+----------------------+---------------------- wm_concat | text | text | agg | vm_concat_state_func | vm_concat_final_func SELECT p.proname AS function_name, pg_catalog.pg_get_function_result(p.oid) AS result_type, pg_catalog.pg_get_function_arguments(p.oid) AS argument_types, CASE WHEN p.proisagg THEN 'agg' ELSE 'normal' END AS function_type ,a.aggtransfn,a.aggfinalfn FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_aggregate a ON a.aggfnoid = p.oid WHERE a.aggfnoid IS NOT NULL AND p.proname like 'string_agg%'; function_name | result_type | argument_types | function_type | aggtransfn | aggfinalfn ---------------+-------------+----------------+---------------+--------------------------+-------------------------- string_agg | text | text, text | agg | string_agg_transfn | string_agg_finalfn string_agg | bytea | bytea, bytea | agg | bytea_string_agg_transfn | bytea_string_agg_finalfn (2 rows) SELECT p.proname AS function_name, pg_catalog.pg_get_function_result(p.oid) AS result_type, pg_catalog.pg_get_function_arguments(p.oid) AS argument_types, CASE WHEN p.proisagg THEN 'agg' ELSE 'normal' END AS function_type ,a.aggtransfn,a.aggfinalfn FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace LEFT JOIN pg_catalog.pg_aggregate a ON a.aggfnoid = p.oid WHERE a.aggfnoid IS NOT NULL AND p.proname like 'listagg%'; function_name | result_type | argument_types | function_type | aggtransfn | aggfinalfn ---------------+-------------+-----------------------------------+---------------+-------------------------------------+------------------ listagg | text | text, text | agg | list_agg_transfn | list_agg_finalfn listagg | text | text | agg | list_agg_noarg2_transfn | list_agg_finalfn listagg | text | smallint, text | agg | int2_list_agg_transfn | list_agg_finalfn listagg | text | smallint | agg | int2_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | integer, text | agg | int4_list_agg_transfn | list_agg_finalfn listagg | text | integer | agg | int4_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | bigint, text | agg | int8_list_agg_transfn | list_agg_finalfn listagg | text | bigint | agg | int8_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | real, text | agg | float4_list_agg_transfn | list_agg_finalfn listagg | text | real | agg | float4_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | double precision, text | agg | float8_list_agg_transfn | list_agg_finalfn listagg | text | double precision | agg | float8_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | numeric, text | agg | numeric_list_agg_transfn | list_agg_finalfn listagg | text | numeric | agg | numeric_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | date, text | agg | date_list_agg_transfn | list_agg_finalfn listagg | text | date | agg | date_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | timestamp without time zone, text | agg | timestamp_list_agg_transfn | list_agg_finalfn listagg | text | timestamp without time zone | agg | timestamp_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | timestamp with time zone, text | agg | timestamptz_list_agg_transfn | list_agg_finalfn listagg | text | timestamp with time zone | agg | timestamptz_list_agg_noarg2_transfn | list_agg_finalfn listagg | text | interval, text | agg | interval_list_agg_transfn | list_agg_finalfn listagg | text | interval | agg | interval_list_agg_noarg2_transfn | list_agg_finalfn (22 rows) MogDB=# \df string_agg List of functions Schema | Name | Result data type | Argument data types | Type | fencedmode | propackage | prokind ------------+------------+------------------+---------------------+------+------------+------------+--------- pg_catalog | string_agg | bytea | bytea, bytea | agg | f | f | f pg_catalog | string_agg | text | text, text | agg | f | f | f (2 rows) MogDB=# select wm_concat(name) from test2; wm_concat ----------- 1,2,3 (1 row) MogDB=# select string_agg(name,',') from test2; string_agg ------------ 1,2,3 (1 row) MogDB=# select string_agg(name,',' order by id desc ) from test2; select string_agg(name,',' order by id desc ) from test2; string_agg ------------ 3,2,1 (1 row) MogDB=# select listagg(name,',') within group(order by id desc ) from test2; select listagg(name,',') within group(order by id desc ) from test2; listagg --------- 3,2,1 (1 row)
Note:
wm_concat, string_agg, listagg都可以实现类似oracle wm_concat的功能。
Postgresql/Opengauss数据库中的wm_concat自定义函数
1, 函数传递(错误) CREATE OR REPLACE FUNCTION my_wm_concat(input_value text) RETURNS text AS $$ DECLARE result text; BEGIN -- 调用 wm_concat 作为参数传递 result = wm_concat(input_value); RETURN result; END; $$ LANGUAGE plpgsql; MogDB=# select my_wm_concat(name) from test2; select my_wm_concat(name) from test2; my_wm_concat -------------- 1 2 3 (3 rows) 这种函数对于聚合函数是不可行的。 2, 自定义聚合函数 在PG系创建自定义聚合函数通常需要三个步骤： *创建状态转移函数：用于处理每一行输入并更新聚合状态。 *创建最终函数（可选）：用于在聚合计算完成后生成最终结果。 *定义聚合函数：结合状态转移函数和最终函数。 CREATE OR REPLACE FUNCTION pg_catalog.my_wm_concat_state_func (results text, val text) RETURNS text LANGUAGE sql COST 50 IMMUTABLE AS $$ select results || ',' ||val; $$; CREATE OR REPLACE FUNCTION pg_catalog.my_wm_concat_final_func (results text) RETURNS text LANGUAGE sql COST 111 IMMUTABLE AS $$ select substr(results, 2); $$; CREATE AGGREGATE pg_catalog.my_wm_concat(text) ( sfunc = pg_catalog.my_wm_concat_state_func, stype = text, initcond = '', FINALFUNC = pg_catalog.my_wm_concat_final_func ); MogDB=# select my_wm_concat(name) from test2; select my_wm_concat(name) from test2; my_wm_concat -------------- 1,2,3 当然也可以复用之前的string_sgg或wm_concat的函数如 CREATE AGGREGATE pg_catalog.my_wm_concat(text) ( sfunc = pg_catalog.string_agg_transfn, stype = text, initcond = '', FINALFUNC = pg_catalog.string_agg_finalfn ); -- or -- CREATE AGGREGATE pg_catalog.my_wm_concat(text) ( sfunc = pg_catalog.vm_concat_state_func, stype = text, initcond = '', FINALFUNC = pg_catalog.vm_concat_final_func ); MogDB=# select my_wm_concat(name) from test2; select my_wm_concat(name) from test2; my_wm_concat -------------- 1,2,3 (1 row)
查询Oracle中的聚合函数
select owner,name,type from dba_dependencies
where (referenced_owner,referenced_name) in
(select owner,OBJECT_NAME from dba_procedures where PROCEDURE_NAME='ODCIAGGREGATEINITIALIZE')
and type='FUNCTION'
and owner NOT IN('MDSYS')
;
有时我们希望提前评估oracle中有哪些聚合函数,我会加到我的数据库画像中。
小结
通过简单的记录对于像wm_concat聚合函数在Oracel到其它库迁移中存储过程的改写， 提示在后期的异构迁移时，对于PLSQL对象并不是简单的SQL语法替换，如果对于非业务逻辑性函数熟悉其功能，也可能不需要完全按照源数据库的SQL一对一改写，目标数据库有可能自带的系统函数，对于PLSQL对象，因为源码对应用透明，建议在Plsql内部直接改用用目标库的本地函数。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。