[Postgres-xc-developers] CREATE AGGREGATE

[Andrei M. <and...@en...>]

Hi Sudo-san,

Good description of CREATE AGGREGATE can be found in the Postgres docs:

http://www.postgresql.org/docs/8.4/static/sql-createaggregate.html

Postgres-XC added additional step (collection function) between
transition and final calculation functions, and schema

sfunc( internal-state, next-data-values ) ---> next-internal-state
ffunc( internal-state ) ---> aggregate-value

was changed to

sfunc( node-internal-state, next-data-values ) ---> node-internal-state
cfunc( coord-internal-state, node-internal-state ) ---> coord-internal-state
ffunc( coord-internal-state ) ---> aggregate-value

To reflect this change following parameters was added to CREATE AGGREGATE:

CFUNC - collection function,
CTYPE - collection data type,
INITCOLLECT - (optional) initial collection value.

It is hard to develop aggregate function from the scratch. For my
testing I recreated existing functions under different name.
Example:

create aggregate mymin (int4) (sfunc=int4smaller, stype=int4,
cfunc=int4smaller, ctype=int4);

You can see the list of defined aggregate functions querying pg_aggregate table:

select * pg_aggregate;

You can also try and construct own aggregate function. You would need
to choose functions for sfunc, cfunc and ffunc (component functions)
from already defined in Postgres.
You can see the list of defined functions querying pg_proc table:

select * pg_proc;

There are requirements for return types and parameter types of the
component functions.
cfunc:
data type of the first parameter must be the same as return type. Data
types of remaining parameters must be the same as data types of the
aggregate  function
sfunc:
must have two parameters, data type of first parameter must be the
same as return type of sfunc, data type of second parameter must be
the same as return type of cfunc
ffunc:
must have one parameter, data type must be the same as return type of sfunc.

And finally data types, you can see the list of defined data types
querying pg_type table:
select * pg_type;

Feel free to ask if you have a question.

-- 
Andrei Martsinchyk

EntepriseDB Corporation
The Enterprise Postgres Company

Website: www.enterprisedb.com
EnterpriseDB Blog: http://blogs.enterprisedb.com/
Follow us on Twitter: http://www.twitter.com/enterprisedb

This e-mail message (and any attachment) is intended for the use of
the individual or entity to whom it is addressed. This message
contains information from EnterpriseDB Corporation that may be
privileged, confidential, or exempt from disclosure under applicable
law. If you are not the intended recipient or authorized to receive
this for the intended recipient, any use, dissemination, distribution,
retention, archiving, or copying of this communication is strictly
prohibited. If you have received this e-mail in error, please notify
the sender immediately by reply e-mail and delete this message.