[jira] Issue Comment Edited: (DERBY-672) Re-enable user defined aggregates

["Rick Hillegas (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DERBY-672?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12457874#action_12457874 ] 

Rick Hillegas edited comment on DERBY-672 at 7/23/10 2:42 PM:
--------------------------------------------------------------

I do not see ANSI syntax for this feature. Here is the syntax used by some other databases.

----------------------------------------

Cloudscape 3.5:

CREATE AGGREGATE AggregateName FOR
    { JavaClassName | ClassAlias }

The aliased class has to be an implementation of a Cloudscape interface (COM.cloudscape.aggregates.AggregateDefinition). That, in turn, means that the class has to implement a couple methods:

accumulate() -- accumulator for non-grouped results
getResult()     -- the final result of the accumulation
merge()          -- accumulator for grouped results

---------------------------------------

Postgres 8.0.0:

CREATE AGGREGATE name (
    BASETYPE = input_data_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
)

Here

SFUNC is the name of an accumulator function
FFUNC is the name of a function which returns the accumulated result

-----------------------------------------------------------

IBM Informix:

CREATE AGGREGATE average
   WITH (
      INIT = initFunction,
      ITER = iterationFunction,
      COMBINE = combinationFunction,
      FINAL = resultFunction
      )

where

initFunction is a function initializing the accumulator
iterationFunction a counting function
combinationFunction an accumulator function
resultFunction a function which returns the accumulated result


-----------------------------------------------------------

Oracle:

In Oracle, you define a UDT which implements an aggregator interface. Then you bind that UDT to an aggregate name. See http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28425/aggr_functions.htm

CREATE TYPE MyAggregatorRoutines(
   STATIC FUNCTION ODCIAggregateInitialize( ... ) ...,
   MEMBER FUNCTION ODCIAggregateIterate(...) ... ,
   MEMBER FUNCTION ODCIAggregateMerge(...) ...,
   MEMBER FUNCTION ODCIAggregateTerminate(...)
);
CREATE TYPE BODY MyAggregatorRoutines IS 
...
END;

CREATE FUNCTION MyAggregate(x SomeDataType) RETURN SomeDataType 
AGGREGATE USING MyAggregateRoutines;







      was (Author: rhillegas):
    I do not see ANSI syntax for this feature. Here is the syntax used by some other databases.

----------------------------------------

Cloudscape 3.5:

CREATE AGGREGATE AggregateName FOR
    { JavaClassName | ClassAlias }

The aliased class has to be an implementation of a Cloudscape interface (COM.cloudscape.aggregates.AggregateDefinition). That, in turn, means that the class has to implement a couple methods:

accumulate() -- accumulator for non-grouped results
getResult()     -- the final result of the accumulation
merge()          -- accumulator for grouped results

---------------------------------------

Postgres 8.0.0:

CREATE AGGREGATE name (
    BASETYPE = input_data_type,
    SFUNC = sfunc,
    STYPE = state_data_type
    [ , FINALFUNC = ffunc ]
    [ , INITCOND = initial_condition ]
)

Here

SFUNC is the name of an accumulator function
FFUNC is the name of a function which returns the accumulated result

-----------------------------------------------------------

IBM Informix:

CREATE AGGREGATE average
   WITH (
      INIT = initFunction,
      ITER = iterationFunction,
      COMBINE = combinationFunction,
      FINAL = resultFunction
      )

where

initFunction is a function initializing the accumulator
iterationFunction a counting function
combinationFunction an accumulator function
resultFunction a function which returns the accumulated result

  
> Re-enable user defined aggregates
> ---------------------------------
>
>                 Key: DERBY-672
>                 URL: https://issues.apache.org/jira/browse/DERBY-672
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>
> Nicolas Dufour in an email thread titled "functions and list" started on November 2, 2005 requests the ability to create user defined aggregates.
> This functionality used to be in Cloudscape. It was disabled presumably because it was considered non-standard. However, most of the machinery needed for this feature is still in the code. We should re-enable user defined aggregates after we agree on acceptable syntax.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.