首页 » ORACLE 9i-23ai, PostgreSQL/GaussDB » Oracle国产化改造迁移openGauss时的问题: 自定义聚合函数wm_concat

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内部直接改用用目标库的本地函数。

打赏

目前这篇文章还没有评论(Rss)

我要评论