How to create an aggregate?

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.


--


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: How to create an aggregate?

Ray Aspeitia writes:
> I also would like to pass the delimiter to the aggregate as a parameter
> and I am not sure if it can handle that.

It can't. You'll need a single-argument finalfunc that hardwires the
delimiter, ie,
array_to_string($1, '|')

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: How to create an aggregate?

Ray Aspeitia writes:

> I also would like to pass the delimiter to the aggregate as a parameter and
> I am not sure if it can handle that.

Currently aggregates that take multiple parameters are just not supported.

--
greg


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: How to create an aggregate?

So the AGGREGATE function also references parameters like a regular
SQL function. Good to know.

Figured I'd ask anyway. Thanks for the info Tom, Greg.

Ray A.


>
> > I also would like to pass the delimiter to the aggregate as a parameter
>> and I am not sure if it can handle that.
>
>It can't. You'll need a single-argument finalfunc that hardwires the
>delimiter, ie,
> array_to_string($1, '|')
>
> regards, tom lane


--
Ray Aspeitia
Sells Printing Company LLC
aspeitia@sells.com
(262) 317-8314

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org