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.dc01034.1510/doc/html/asc1238775307180.html

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/0309stolze.html

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