Re: multi-column aggregates

On Monday 13 March 2006 03:21 pm, Tom Lane wrote:
> Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
> > Thanks for the reply. Yes, subselects would work very well and in some
> > ways are more elegant then the hand waving we had to do to get the
> > multi-column aggregates to work.
>
> BTW, there is not any fundamental reason why we can't support aggregate
> functions with multiple inputs. I looked at this not long ago and
> determined that the major stumbling blocks would be
> (1) trying to keep the code in nodeAgg.c from getting a lot more
> complicated and hence slower for the single-input case;
> (2) figuring out how to change the syntax for CREATE AGGREGATE.
>
> It certainly seems doable if someone wants to spend time on it.
>
> regards, tom lane

That is interesting to know. Unfortunately, I don't believe my programming
foo is up to hacking pg code at this point. If I were to spend time on it
though, I think I'd rather spend time on true materialized views rather then
this since I have a working solution. :-)

When I started this, I would have been in much greater favor of multi-column
aggregates. As it stands, after learning about composite types, they are a
fairly elegant solution to the problem without making the code more complex
for the single column variant. Having said that, there are certain problem
domains where multi-column aggregates are really useful and if it were to
show up in a future release I certainly wouldn't complain.

BTW, I found it interesting that MS SQL Server doesn't appear to support
multi-column aggregates either. Of course I may not have known where to look
either.

One option for 2 above might be to use composite/row types for the create
aggregate call. If the Parens are optional for the single column versions
then it would be backwards compatible with aggregate definitions now.

ie
CREATE AGGREGATE some_agg (
BASETYPE = (input_data_type, input data_type2, ...),
SFUNC = sfunc,
STYPE = (state_data_type, state_data_type2, ...),
)

The main difference between this and what we have today would be that the user
doesn't have to create composite data types, or use typecasting if sfunc is
overloaded.

Just a thought.

-Chris