To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Ram Nathaniel <ram_nathaniel@hotmail.com>,
	pgsql-sql@postgresql.org
Subject: Re: SQL syntax extentions - to put postgres ahead in the race 
In-reply-to: <8583.1091776047@sss.pgh.pa.us> 
References: <BAY7-DAV12V0KUvkRkZ000f7e18@hotmail.com>
	<20040805220147.O92438@megazone.bigpanda.com>
	<8583.1091776047@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 06 Aug 2004 03:07:27 -0400"
Date: Fri, 06 Aug 2004 10:30:06 -0400
Message-ID: <11623.1091802606@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I wrote:
> There's nothing particularly stopping us from supporting
> multiple-argument aggregates, except a lack of round tuits.

BTW, you can actually fake this pretty well in 8.0, by making an
aggregate that uses a rowtype input.  For example:

regression=# create type twostrings as (s1 text, s2 text);
CREATE TYPE
regression=# create function list_concat(text, twostrings) returns text as $$
regression$# select case when $1 is null then $2.s1
regression$# when $2.s1 is null then $1
regression$# else $1 || $2.s2 || $2.s1
regression$# end$$ language sql;
CREATE FUNCTION
regression=# create aggregate concat (
regression(# basetype = twostrings,
regression(# stype = text,
regression(# sfunc = list_concat);
CREATE AGGREGATE
regression=# select * from text_tbl;
        f1
-------------------
 doh!
 hi de ho neighbor
 more stuff
 and more
(4 rows)

regression=# select concat((f1, '|')) from text_tbl;
                   concat
--------------------------------------------
 doh!|hi de ho neighbor|more stuff|and more
(1 row)


This is somewhat inefficient compared to native support for
multi-argument aggregates, but at least we have something we can point
people to until we find time to make that happen.

			regards, tom lane