maria-developers team mailing list archive
Mailing list archive
Re: MDEV-7773, Aggregate Stored Functions
I have found some of ways to implement this project using C++, SQL, java,
As I had mentioned earlier, I was searching for some of database systems
that have this facility or in some way can implement it.
The database system that I discovered while learning for this topic is HP
Vertica and in this system the basic approach is creating
user defined library saved as .so for C/C++ or .jarr for java and loading
it using CREATE LIBRARY command. When we call SQL function HP Vertica passes
data values to the code in the library to process it.
CREATE [ OR REPLACE ] AGGREGATE FUNCTION [[db-name.]schema.]function-name
... AS LANGUAGE 'language' NAME 'factory' LIBRARY library_name;
example using this method is as given below
=> CREATE LIBRARY AggregateFunctions AS
=> CREATE AGGREGATE FUNCTION ag_avg AS LANGUAGE 'C++' NAME 'AverageFactory'
CREATE AGGREGATE FUNCTION
=> CREATE AGGREGATE FUNCTION ag_cat AS LANGUAGE 'C++' NAME 'ConcatenateFactory'
CREATE AGGREGATE FUNCTION
Expanded display is on.
select * from user_functions;
The only drawback in this method that this can be implemented using C++
only for HP Vertica.
The another method that I discovered is given in the latest update of
oracle i.e Oracle 9i (9)
the refrence link for same is
According to this we require two components to implement aggregate functions
1. an object type specification and body; and
2. a PL/SQL function.
There is a development framework for data cartridges (think of this as a
template) provided by Oracle. This provides the structure of the object
type and its methods (down to the detail of how we name them) and also
defines how we create our PL/SQL function. For example, our object type
will contain the following:attribute(s) for holding state information.
These can be of any existing built-in or user-defined datatype;
- a mandatory ODCIAggregateInitialize static method to reset the state
attributes at the start of an aggregation;
- a mandatory ODCIAggregateIterate member method to apply each input
value to the running aggregate value;
- a mandatory ODCIAggregateTerminate member method to return the final
result of the aggregate; and
- an optional ODCIAggregateMerge member method, used to combine the
results of more than one stream of aggregation (for example, with parallel
query) before returning a result.
More detailed information, explanation and examples are given in the link
that I have mentioned above.
Please let me know whether I am working in right direction or not and
whether the methods I have mentioned can be used or not to implement the