Re: [Postgres-xc-developers] postgres-xc:errors

[Mason S. <mas...@en...>]

On 12/1/10 5:51 AM, sch19831106 wrote:
> Hi developers.
> Excuse me! I am shen changhai.
> *****************************  postgres-xc Environment 
> ********************************
> Executed statement " create aggregate  "  on the postgres-xc,statement 
> and results are as follows:
> Statement:
> create aggregate newavg2 (sfunc = int4pl, basetype = int4,stype = int4,finalfunc = int2um, initcond = '0');
> Results:
> ERROR:  aggregate ctype must be specified

In Postgres-XC, you also need to specify a ctype function, as in XC 
aggregates are divided into 3 phases, not 2.  I thought this was in the 
documentation. Here is some additional info from an email I received 
from Andrei Martsinchyk:

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

http://www.postgresql.org/docs/8.4/static/sql-createaggregate.html 
<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.




Regards,

Mason


> ******************************* pg Environment 
> ***********************************
> Executed statement " create aggregate  "  on the pg,statement and 
> results are as follows:
> Statement:
> create aggregate newavg2 (sfunc = int4pl, basetype = int4,stype = int4,finalfunc = int2um, initcond = '0');
> Results:
> ERROR:  function int2um(integer) does not exist
> The above results are correct?
> Thanks!
> **********************
> shen changhai
> Email: sch...@16... <mailto:sch...@16...>
>
>
> ------------------------------------------------------------------------------
> Increase Visibility of Your 3D Game App&  Earn a Chance To Win $500!
> Tap into the largest installed PC base&  get more eyes on your game by
> optimizing for Intel(R) Graphics Technology. Get started today with the
> Intel(R) Software Partner Program. Five $500 cash prizes are up for grabs.
> http://p.sf.net/sfu/intelisp-dev2dev
>
>
> _______________________________________________
> Postgres-xc-developers mailing list
> Pos...@li...
> https://lists.sourceforge.net/lists/listinfo/postgres-xc-developers
>    


-- 
Mason Sharp
EnterpriseDB Corporation
The Enterprise Postgres Company


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.