pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to create an aggregate?
4+ messages / 3 participants
[nested] [flat]
* How to create an aggregate?
@ 2004-07-30 18:46 Ray Aspeitia <aspeitia@sells.com>
2004-07-31 00:50 ` Re: How to create an aggregate? Tom Lane <tgl@sss.pgh.pa.us>
2004-08-02 04:25 ` Re: How to create an aggregate? Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 4+ messages in thread
From: Ray Aspeitia @ 2004-07-30 18:46 UTC (permalink / raw)
To: pgsql-sql
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.
--
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create an aggregate?
2004-07-30 18:46 How to create an aggregate? Ray Aspeitia <aspeitia@sells.com>
@ 2004-07-31 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-02 13:00 ` Re: How to create an aggregate? Ray Aspeitia <aspeitia@sells.com>
1 sibling, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-07-31 00:50 UTC (permalink / raw)
To: Ray Aspeitia <aspeitia@sells.com>; +Cc: pgsql-sql
Ray Aspeitia <aspeitia@sells.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create an aggregate?
2004-07-30 18:46 How to create an aggregate? Ray Aspeitia <aspeitia@sells.com>
2004-07-31 00:50 ` Re: How to create an aggregate? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-08-02 13:00 ` Ray Aspeitia <aspeitia@sells.com>
0 siblings, 0 replies; 4+ messages in thread
From: Ray Aspeitia @ 2004-08-02 13:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to create an aggregate?
2004-07-30 18:46 How to create an aggregate? Ray Aspeitia <aspeitia@sells.com>
@ 2004-08-02 04:25 ` Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 4+ messages in thread
From: Greg Stark @ 2004-08-02 04:25 UTC (permalink / raw)
To: Ray Aspeitia <aspeitia@sells.com>; +Cc: pgsql-sql
Ray Aspeitia <aspeitia@sells.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-08-02 13:00 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-30 18:46 How to create an aggregate? Ray Aspeitia <aspeitia@sells.com>
2004-07-31 00:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-08-02 13:00 ` Ray Aspeitia <aspeitia@sells.com>
2004-08-02 04:25 ` Greg Stark <gsstark@mit.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox