不得了!这个国产数据库居然实现了Oracle的聚合函数wm_concat

2024年 7月 4日 49.9k 0

不得了!这个国产数据库居然实现了Oracle的聚合函数wm_concat-1

熟悉 Oracle 的朋友一定听说过或使用过这个聚合函数vm_concat。说到这个函数,含泪的讲,给我们带来了很多故障,当然都是因客户开发商的人员不了解 Oracle 版本变化带来的问题。

不得了!这个国产数据库居然实现了Oracle的聚合函数wm_concat-2

是的,实际上 Oracle 11g 之后就不再推荐使用这个函数了,因为它在处理varchar2的时候会导致temp使用暴涨。

然而,很多应用程序在12c或19c中可能自定义了类似wm_concat的函数,例如 my_wm_concat。这些函数被广泛使用在应用程序中,当这些应用程序的数据库迁移到国产数据库如 MogDB 时,如果希望数据库层面兼容而不修改应用代码,我们就需要实现函数兼容。

在开源 PostgreSQL 版本中有string_agg函数实现类型功能,MogDB 中除了支持string_agg之外,同时还支持listagg(是的,你没有看错,还兼容 Oracle 12c的listagg函数),也自带了wm_concat。但需要注意的是,MogDB 中vm_concat函数不支持像 Oracle 一样的synonym给个别名,需要创建函数。

这里给大家简单分享一下:

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)

MogDB 中的wm_concat、string_agg、listagg都可以实现类似 Oracle 中的wm_concat函数功能。

MogDB=# CREATE OR REPLACE FUNCTION pg_catalog.my_wm_concat_state_func (results text, val text)
MogDB-#  RETURNS text
MogDB-#  LANGUAGE sql COST 50 IMMUTABLE
MogDB-#  AS $$ select results || ',' ||val; $$;
CREATE FUNCTION
MogDB=# 
MogDB=#  CREATE OR REPLACE FUNCTION pg_catalog.my_wm_concat_final_func (results text)
MogDB-#  RETURNS text
MogDB-#  LANGUAGE sql COST 111 IMMUTABLE
MogDB-#  AS $$ select substr(results, 2); $$;
CREATE FUNCTION
MogDB=# 
MogDB=# CREATE AGGREGATE pg_catalog.my_wm_concat(text)
MogDB-#  (
MogDB(#    sfunc = pg_catalog.my_wm_concat_state_func,
MogDB(#    stype = text,
MogDB(#    initcond = '',
MogDB(#    FINALFUNC = pg_catalog.my_wm_concat_final_func
MogDB(#  );
CREATE AGGREGATE
MogDB=# 
MogDB=# 
MogDB=# select my_wm_concat(name) from test2;
  my_wm_concat   
-----------------
 aaa,bbb,ccc,ddd
(1 row)

MogDB=# 

欢迎大家多多体验 MogDB,并发现其中的一些乐趣!

关于作者

张维照,云和恩墨 数据库专家,有超过10年的金融、保险、政府、地税、运营商等业务关键型系统的运维经验,目前担任云和恩墨服务交付总监一职。

END

MogDB 是云和恩墨基于 openGauss 开源内核进行增强提升,推出的一款安稳易用的企业级关系型数据库。其具备金融级高可用和全密态计算的极致安全、面向多核处理器的极致性能、AI自诊断调优的极致智能能力,能够满足从核心交易到复杂计算的企业级业务需求。

访问官网了解更多:www.mogdb.io

产品兼容适配申请:partner@enmotech.com

进入交流群:Roger_database

不得了!这个国产数据库居然实现了Oracle的聚合函数wm_concat-3

不得了!这个国产数据库居然实现了Oracle的聚合函数wm_concat-4

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论