Re: two-argument aggregates and SQL 2003

Tom Lane wrote:
> I would really prefer to see CREATE AGGREGATE normalized to have a
> syntax comparable to CREATE FUNCTION (or DROP AGGREGATE for that
> matter):
> CREATE AGGREGATE aggname (typname [, ... ]) ...definition...
> but it's not clear how to get there without breaking backwards
> compatibility :-(

To modify the CREATE FUNCTION syntax into a new CREATE AGGREGATE syntax,
we would modify a few things, I think:

CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [, ...] ] )
[ RETURNS rettype ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

1) Drop [ argmode ] because there is no OUT or INOUT parameters possible.
2) Change implicit meaning of the [ rettype ] parameter to not allow SETOF.
(I'd love to have aggregates functions that take arbitrary numbers of rows as
input and return arbitrary numbers of rows as output. But I'm guessing the
internals of the backend would require much work to handle it?)
3) Add a state_data_type
4) Add an optional initial_condition
5) Add an optional sort_operator
6) Add some handling of a final_function like behavior, which I have not handled
below. Should it be done like the current CREATE AGGREGATE syntax, where you
must reference another function, or can anybody see a clean way to let this one
function do it all in one shot?

This might give us, excluding any final_function syntax:

CREATE [ OR REPLACE ] AGGREGATE
name ( [ [ argname ] argtype [, ...] ] )
STYPE state_data_type
[ INITCOND initial_condition ]
[ SORTOP sort_operator ]
[ RETURNS rettype ]
{ LANGUAGE langname
| IMMUTABLE | STABLE | VOLATILE
| CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
| AS 'definition'
| AS 'obj_file', 'link_symbol'
} ...
[ WITH ( attribute [, ...] ) ]

It seems that this syntax is distinct from the current syntax and that the
parser could support both. Thoughts?