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. The syntax for ORACLE I did not understand properly, would give it a try again . For SYBASE I have been going through: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc0... For SQL server https://msdn.microsoft.com/en-in/library/ms182741.aspx DB2 does not support user-defined aggregate functions http://www.ibm.com/developerworks/data/library/techarticle/0309stolze/0309st... On Wed, Mar 2, 2016 at 4:10 PM, Sergei Golubchik <serg@mariadb.org> wrote:
Hi, Varun!
On Mar 02, Varun Gupta wrote:
Well i have checked out the syntax for the databases for aggregate functions. I thought using the syntax similar to that of HSQL would be good. In it if we are having N tuples then we are computing the values for the N rows and then after that we make another call and return the value for the function. Well this seems very reasonable to do. Using PostgreSQL <http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html> we need to have two regular functions but if we have an aggregate function like Count(column_name) , then this would be done in just a single function , I guess we would not need two functions for it .
Yes, I agree about PostgreSQL syntax. An aggregate function there has "artefacts" - two regular functions that show up in the list of functions and can be called directly too. I think this is confusing.
HSQL syntax, I mean, exactly as in HSQL, is full of random limitations. http://hsqldb.org/doc/guide/sqlroutines-chapt.html#src_aggregate_functions
In HSQL an aggregate function can have only *one* argument, and the state is, always, exactly *two* variables. We, of course, won't have these limitation, if we'll do HSQL-style syntax.
But this syntax is also kind of hackish. One function that has two different semantics and the 'flag' argument that selects which one to use. And many parameters in the function declaration, while only one is in the function invocation.
Oracle uses an object-oriented syntax.
Did you find any other DBMS that support this feature?
Regards, Sergei Chief Architect MariaDB and security@mariadb.org