User-defined Aggregates

前言：User-defined Aggregates,自定义聚合。
聚合本身是指将一个group归为一个条目的行为
比如 count 总数，avg平均数，sum求和，concat字符串连接，都是典型的聚合函数
而，自定义聚合，本质上讲是类似于oop里利用base类实现自定义函数的方法，
即：PLPGSQL的接口给了一个实现的接口：
create aggregate aggregate_name(被聚合的type)(
a = a_type, -- accumulator type
init = init_type, -- initial accumulator value
sfunc = func_name -- increment function
);
实现自定义aggregate，本质上是定义了这个聚合的初始值，累加类型和函数名。

1. Introduction
   count(*), the built-in sql aggregate function, ignore None values.

To count both None and other types together, we should define our own aggregate.

create function
                oneMore(sum integer, x anyelement)  returns integer
as $$
begin
      if x is null then
              return sum + 1;
      else
              return sum + 1;
      end if;
end;
$$ language 'plpgsql';  
create aggregate countAll(anyelement)(
        stype = integer, -- the accumulator type
        initcond = 0,      -- initial accumulator value
        sfunc=oneMore --increment function
);

1.if...then... else... end if;
2.anyelement 所有类型

select p.name as pizza, count(t.name)
from     Pizza p
        join Has h on h.pizza = p.id
        join Topping t  on h.topping = t.id
group by p.name

--aggregate to concat strings, using "," - separator

create or replace function
                  append(soFar text, item text) returns text
as $$
begin
      if  soFar = '' then
            return item;
      else
          return soFar||'|'||item;
      end if;
end;
$$ language 'plpgsql';

drop aggregate if exists list(text);

create aggregate list(text)(
        stype = text, -- accumulator type
        initcond = ' ', -- initial accumulator value
        sfunc = append -- increment function
);

||'|'||: || concatenate symbol连接符
*对function我没可以用create or replace,然而aggregate并没有类似的写法