Mime-Version: 1.0
Message-Id: <p06002001bd3047910697@[192.168.5.55]>
Date: Fri, 30 Jul 2004 13:46:36 -0500
To: pgsql-sql@postgresql.org
From: Ray Aspeitia <aspeitia@sells.com>
Subject: How to create an aggregate?
Content-Type: text/plain; charset="us-ascii" ; format="flowed"

Not sure if this made it through before I subscribed, so...

Hello all,

before I ask, this is what I have done so far:

-- created this new aggregate function
CREATE AGGREGATE groupconcat_array (
     SFUNC = array_append,
     BASETYPE = anyelement,
     STYPE = anyarray,
     initcond = '{}'
);


-- ran this select statement
SELECT array_to_string(groupconcat_array(oa.order_number), '|') ...


output is text : 46952|46953|46954|46955|46949


What I would like to do is just have 1 function that does the same thing like:

SELECT groupjoin('|', field2) FROM mytable GROUP BY field1

with the same output as my current implementation.

I tried doing that with the CREATE AGGREGATE in conjunction with the 
FINALFUNC parameter set to array_to_string, but array_to_string needs 
2 parameters to function. and I do not know the reference name of the 
STYPE variable while it is in the aggregate function to pass to it. I 
also would like to pass the delimiter to the aggregate as a parameter 
and I am not sure if it can handle that.

I know that this is just being picky, but any insight would be 
appreciated. Thanks.

Ray A.


--