PostgreSQL Oracle 兼容性之 - WM_SYS.WM_CONCAT

背景

先吐槽一下Oracle的wm_sys.wm_concat这个函数，为什么只能支持逗号分隔符呢？太老土了。

PostgreSQL的string_agg就做得比较只能，可以使用任意字符串作为分隔符。

Oracle行转列函数WMSYS.WM_CONCAT的使用实例demo

select *  from itlife365_course a  where name= '张三';  
name   课程  score  
张三   数学   99  
张三   语文   89  
张三   英语   93  

上面的场景可用WMSYS.WM_CONCAT(a.name)把二行中的[课程]字段的值用”,”连接起来

如：

select name, to_char(WMSYS.WM_CONCAT(a.课程))  
  from itlife365_course a  
 where name= '张三'  
 group by a.name;  

注意：因为用WMSYS.WM_CONCAT转出的类型是clob的，所以我这用了to_char转了一下。

使用wmsys.wm_concat多列合成一列遇到问题

ORA-22813: 操作数值超出系统的限制

官方文档解释是总长度超过30k

请使用其他方法替代。

PostgreSQL不存在问题，最长可以达到1GB。

PostgreSQL使用string_agg聚合函数即可达到同样的目的：  
select name, string_agg(a.课程, ',')  
  from itlife365_course a  
 where name= '张三'  
 group by a.name;  

如果用户不想改代码，可以尝试自行创建一个名为WM_CONCAT的聚合函数，例子如下：

create schema WMSYS;  
create or replace function WMSYS.sf_concat(text,text) returns text as $$  
  select case when $1 is not null then $1||','||$2 else $2 end;  
$$ language sql called on null input;  
create AGGREGATE WMSYS.wm_concat (text) (sfunc=WMSYS.sf_concat,stype=text);  

测试:

postgres=# select reltype,wmsys.wm_concat(relname) from pg_class group by reltype order by reltype;  
 reltype |                                                                                                                                                                                                                                                                                                                                                                                             wm_concat                                                               
                                                                                     
---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  
------  
       0 | pg_oid_16388_index,pg_toast_2619_index,pg_authid_rolname_index,pg_attribute_relid_attnam_index,pg_attribute_relid_attnum_index,pg_toast_1255_index,ha_health_check_pkey,pg_toast_2606_index,pg_am_name_index,pg_am_oid_index,pg_am  
op_fam_strat_index,pg_amop_opr_fam_index,pg_amop_oid_index,pg_amproc_fam_proc_index,pg_amproc_oid_index,pg_aggregate_fnoid_index,pg_toast_2618_index,pg_toast_2620_index,pg_toast_2609_index,pg_cast_oid_index,pg_cast_source_target_index,pg  
_toast_2615_index,pg_toast_2964_index,pg_auth_members_role_member_index,pg_auth_members_member_role_index,pg_toast_2396_index,pg_toast_3596_index,pg_collation_oid_index,pg_collation_name_enc_nsp_index,pg_toast_2893_index,pg_database_datn  
ame_index,pg_database_oid_index,pg_proc_oid_index,pg_proc_proname_args_nsp_index,pg_inherits_parent_index,pg_inherits_relid_seqno_index,pg_index_indrelid_index,pg_index_indexrelid_index,pg_operator_oid_index,pg_operator_oprname_l_r_n_ind  
ex,pg_opfamily_am_name_nsp_index,pg_opfamily_oid_index,pg_opclass_am_name_nsp_index,pg_opclass_oid_index,pg_language_name_index,pg_language_oid_index,pg_largeobject_metadata_oid_index,pg_rewrite_oid_index,pg_rewrite_rel_rulename_index,pg  
_trigger_tgconstraint_index,pg_trigger_tgrelid_tgname_index,pg_trigger_oid_index,pg_event_trigger_evtname_index,pg_event_trigger_oid_index,pg_description_o_c_o_index,pg_enum_oid_index,pg_enum_typid_label_index,pg_enum_typid_sortorder_ind  
ex,pg_namespace_nspname_index,pg_namespace_oid_index,pg_conversion_default_index,pg_conversion_name_nsp_index,pg_conversion_oid_index,pg_depend_depender_index,pg_depend_reference_index,pg_tablespace_oid_index,pg_tablespace_spcname_index,  
pg_pltemplate_name_index,pg_shdepend_depender_index,pg_shdepend_reference_index,pg_shdescription_o_c_index,pg_ts_config_cfgname_index,pg_ts_config_oid_index,pg_oid_16417_index,pg_type_oid_index,pg_user_mapping_oid_index,pg_user_mapping_u  
ser_server_index,pg_ts_config_map_index,pg_ts_dict_dictname_index,pg_ts_parser_prsname_index,pg_ts_parser_oid_index,pg_ts_template_tmplname_index,pg_ts_template_oid_index,pg_extension_oid_index,pg_extension_name_index,pg_foreign_data_wra  
pper_oid_index,pg_foreign_data_wrapper_name_index,pg_foreign_server_oid_index,pg_foreign_server_name_index,pg_foreign_table_relid_index,pg_default_acl_role_nsp_obj_index,pg_default_acl_oid_index,pg_seclabel_object_index,pg_shseclabel_obj  
ect_index,pg_range_rngtypid_index,pg_synonym_oid_index,pg_variable_oid_index,pg_variable_varname_pkg_index,edb_dir_oid_index,edb_dir_name_index,edb_policy_oid_index,edb_policy_object_name_index,edb_partdef_oid_index,edb_partdef_pdefrel_i  
ndex,edb_partition_oid_index,edb_partition_pdefid_index,pg_toast_16417_index,pg_oid_16431_index,pg_toast_16431_index,pg_toast_12506_index,pg_toast_12511_index,pg_toast_12516_index,pg_toast_12521_index,pg_toast_12526_index,pg_toast_12531_  
index,pg_toast_13390_index,pg_toast_13857_index,system_waits_pk,pg_toast_13864_index,session_waits_pk,pg_toast_13871_index,session_waits_hist_pk,edb$stat_idx_pk,edb$stat_tab_pk,edb$stat_db_pk,edb$statio_idx_pk,edb$statio_tab_pk,pg_authid  
_oid_index,pg_statistic_relid_att_inh_index,pg_type_typname_nsp_index,pg_largeobject_loid_pn_index,pg_class_oid_index,pg_class_relname_nsp_index,pg_toast_2604_index,pg_attrdef_adrelid_adnum_index,pg_attrdef_oid_index,pg_constraint_connam  
e_nsp_index,pg_constraint_conrelid_index,pg_constraint_contypid_index,pg_constraint_oid_index,pg_db_role_setting_databaseid_rol_index,pg_ts_dict_oid_index,pg_synonym_synname_nspoid_index,pg_toast_12501_index,edb_partition_partrelid_index  
,pg_toast_13397_index,pg_toast_13383_index,plsql_profiler_runs_pkey,pg_toast_13850_index,snap_pk  
...  

聚合函数的原理可参考:

https://yq.aliyun.com/articles/5060

digoal’s 大量PostgreSQL文章入口