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 <[email protected]> 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. > > Regards, > Sergei > Chief Architect MariaDB > and [email protected] >
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
