User-defined|User-defined Aggregates User-definedAggregates

前言：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，本质上是定义了这个聚合的初始值，累加类型和函数名。

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) fromPizza p join Has h on h.pizza = p.id join Topping ton h.topping = t.id group by p.name

--aggregate to concat strings, using "," - separatorcreate or replace function append(soFar text, item text) returns text as $$ begin ifsoFar = '' 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 );

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