informix-connstra

来源：互联网发布：python自动化运维系统编辑：程序博客网时间：2024/06/06 18:50

CREATE FUNCTION connstra_init (dummy lvarchar(4000))
RETURNING lvarchar(4000);
RETURN '';
END FUNCTION;

CREATE FUNCTION connstra_iter (result lvarchar(4000), value lvarchar(4000))
RETURNING lvarchar(4000);
RETURN result || value ||',';
END FUNCTION;

CREATE FUNCTION connstra_combine(partial1 lvarchar(4000), partial2 lvarchar(4000))
RETURNING lvarchar(4000);
RETURN partial1 || partial2;
END FUNCTION;

CREATE FUNCTION connstra_final(final lvarchar(4000))
RETURNING lvarchar(4000);
RETURN final;
END FUNCTION;

CREATE AGGREGATE connstra WITH
(INIT = connstra_init,
ITER = connstra_iter,
COMBINE = connstra_combine,
FINAL = connstra_final);

--测试
create table t1(a int,b int);
insert into t1 values(1,2);
insert into t1 values(11,22);
insert into t1 values(111,222);
insert into t1 values(1111,333);

select * from t1;

> select connstra(a) from t1;
connstra 1,11,111,1111,

1 row(s) retrieved.

--去掉最后一个逗号
> select rtrim(connstra(a),',') from t1;

(expression) 1,11,111,1111

1 row(s) retrieved.

0 0