With the cursor approach, I think it looks more easier to understand . Also it covers the point that the state has to be saved when the function is called for different values of x ,so we know the approach is correct. We are using the function attribute X in calculating the sum. I don't understand what you mean by not using declared argument directly.Well at least the cursor method looks far simpler from the ones we discussed earlier .On Wed, Mar 2, 2016 at 6:41 PM, Sergei Golubchik <serg@mariadb.org> wrote:Hi, Varun!
On Mar 02, Varun Gupta wrote:
> Well in HSQL we have the limitation of having maximum of 4 arguments , but
> in our syntax we can increase the number of arguments.The main thing is
> what I understood from HSQL and PostgreSQL is that an aggregate function
> would have 2 states.
> 1) when we compute the result that is the result keeps on getting updated
> on various calls to the function
> 2) when we return the result
>
> HSQL does that using the flag so we need to call an additional time with
> flag == TRUE so that result is returned .
> While in PostgreSQL we split the work into two regular functions . The
> problem is that the second function in PostgreSQL is optional so that adds
> to a bit of confusion.
What about other ideas presented in MDEV-7773? I like the cursor one.
Compare the standard SQL function:
CREATE FUNCTION avg() RETURNS DOUBLE
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE sum DOUBLE DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
DECLARE x DOUBLE;
DECLARE cur1 CURSOR FOR SELECT col1 FROM t1;
OPEN cur1;
LOOP
FETCH cur1 INTO x;
SET count:=count+1;
SET sum:=sum+x;
END LOOP;
END
This is a regular standard non-aggregate function that returns an
average of all values in a column 'col1' of the table 't1'.
The cursor idea of a syntax for an aggregate function uses the same
familiar logic:
CREATE AGGREGATE FUNCTION avg(x DOUBLE) RETURNS DOUBLE
BEGIN
DECLARE count INT DEFAULT 0;
DECLARE sum DOUBLE DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' RETURN sum/count;
LOOP
FETCH GROUP NEXT ROW;
SET count:=count+1;
SET sum:=sum+x;
END LOOP;
END
This looks simple, familiar, natural, leaves no artefacts.
What I don't like is that a function's declared argument is not used
directly. Looks a bit artificial :(
> The syntax for ORACLE I did not understand properly, would give it a try
> again.
Don't bother, it's an object oriented approach. An aggregate function is
an *object*. Object has different methods for result and for getting
updated. Object data fields used to store the intermediate state.
This is quite natural, if the DBMS supports objects. But we don't.
> For SYBASE I have been going through:
> http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc01034.1510/doc/html/asc1238775307180.html
Okay, so "External C/C++."
> For SQL server
> https://msdn.microsoft.com/en-in/library/ms182741.aspx
Same, "implementation is defined in a class of an assembly in the .NET
Framework"
> DB2 does not support user-defined aggregate functions
> http://www.ibm.com/developerworks/data/library/techarticle/0309stolze/0309stolze.html
Right.