informix 自定义聚集函数

/******************************************************************************************/
informix 自定义聚集函数 
/******************************************************************************************/
如何自定义聚集函数,本文章以 strsum 函数举例.
1 功能说明：
strsum函数的功能：使用 '-'连接符号将多个字符连接在一起。
举例：
create table test_str(t1 integer,t2 varchar(255));
表test_str的数据如下
insert into test_str values (1,'A');
insert into test_str values (1,'B');
insert into test_str values (1,'C');
insert into test_str values (2,'A');
insert into test_str values (2,'B');
insert into test_str values (3,'A');
insert into test_str values (4,'A');
 
select t1,strsum(t2) t2_sum from test_str
where 1=1
group by t1 order by t1;
t1 t2_sum
1     A-B-C-
2     A-B-
3     A-
4     A-
 
2 若需要去掉最后一个'-'字符，只需要修改为如下SQL
select t1,rtrim(strsum(t2),'-') t2_sum from test_str
where 1=1
group by t1 order by t1;
t1 t2_sum
1     A-B-C
2     A-B
3     A
4     A
 
3 在某些情况，我们需要采用不同的连接符，我们只需要修改查询SQL语句，采用replace函数替换'-'符号。
select t1,replace(rtrim(strsum(t2),'-'),'-','||') t2_sum from test_str
where 1=1
group by t1 order by t1;
t1 t2_sum
1     A||B||C
2     A||B
3     A
4     A
 
 
4 如何定义聚集函数
如下我们将说明如何自定义该聚集函数strsum；
首先定义聚集函数的4个子函数str_sum_init,str_sum_iter,str_sum_combine,str_sum_final;其中str_sum_iter中定义了聚集算法为：采用'-'连接。
最后定义聚集函数strsum
 
代码详细见如下：
 
CREATE FUNCTION str_sum_init (dummy lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN '';
END FUNCTION;
 
CREATE FUNCTION str_sum_iter (result lvarchar(4000), value lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN result || value ||'-';
END FUNCTION;
 
CREATE FUNCTION str_sum_combine(partial1 lvarchar(4000), partial2 lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN partial1 || partial2;
END FUNCTION;
CREATE FUNCTION str_sum_final(final lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN final;
END FUNCTION;
 
CREATE AGGREGATE strsum  WITH
   (INIT = str_sum_init,
    ITER = str_sum_iter,
    COMBINE = str_sum_combine,
    FINAL = str_sum_final);
 -------
5 逗号分隔的自定义聚集函数
CREATE FUNCTION str_sum_init (dummy lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN '';
END FUNCTION;
 
CREATE FUNCTION str_sum_iter (result lvarchar(4000), value lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN result || value ||',';
END FUNCTION;
 
CREATE FUNCTION str_sum_combine(partial1 lvarchar(4000), partial2 lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN partial1 || partial2;
END FUNCTION;
 
CREATE FUNCTION str_sum_final(final lvarchar(4000))
   RETURNING lvarchar(4000);
   RETURN final;
END FUNCTION;
 
CREATE AGGREGATE strsum  WITH
   (INIT = str_sum_init,
    ITER = str_sum_iter,
    COMBINE = str_sum_combine,
    FINAL = str_sum_final);


--测试
 select rtrim(strsum(colname),',') from systables s1,syscolumns s2 where s1.tabid=s2.tabid and s1.tabname='test_1' and colname not in('insert_time','update_time');
col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,co
              l13,col14,col15,col16,col17,col18,col19,col20,col21,col22,col23