aggregate 函数使用示例

 

--本实例演示函数功能
create or replace function pg_sum(integer,integer) returns integer as
$body$
begin
 if $1 is null then
  return $2;
 else
  return $1 + $2;
 end if;
end;
$body$ language 'plpgsql'

--对最后结果乘以2
create or replace function pg_sum_finc(integer) returns integer as
$body$
begin
 return 2 * $1;
end;
$body$ language 'plpgsql'

--drop AGGREGATE pg_sum(integer);
CREATE  AGGREGATE  pg_sum(integer)(
SFUNC = pg_sum,
STYPE = integer,
FINALFUNC=pg_sum_finc,
INITCOND =100   --初始值
);

 

create table tmp_pg_sum(id integer,s1 integer,s2 decimal(12,2));
insert into tmp_pg_sum(id,s1,s2) values(1,2,2.4),
(1,3,2.2),
(2,2,2.5);

select id,pg_sum(s1),sum(s2) from tmp_pg_sum group by id;

----字符串合并处理，主要实现数据参数，并实现分割符
create or replace function pg_concat(text,text,text) returns text as
$body$
begin
 if $1 is null then
  return $2||$3;
 else
  return $1||$2||$3;
 end if;
end;
$body$
language 'plpgsql'

--创建一个finc函数,处理最后一位空格
create or replace function pg_concat_fin(text) returns text as
$body$
begin
 return  substr($1,1,length($1)-1);

end;
$body$
language 'plpgsql'

--drop aggregate  pg_concat(text,text);
create aggregate pg_concat(text,text) (
sfunc = pg_concat,
stype = text,
finalfunc=pg_concat_fin);

 

create table tmp_pg_concat(id integer,name1 varchar(16),name2 varchar(16));

insert into tmp_pg_concat(id,name1,name2)
values(1,'a','a'),
(1,'b','b'),
(1,'c','c'),
(2,'a','a'),
(2,'a','a'),
(3,'a','a');

--
select id,pg_concat(name1,'%') from tmp_pg_concat group by id;