pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Concatenate results of a single column query
6+ messages / 5 participants
[nested] [flat]
* Concatenate results of a single column query
@ 2004-04-18 14:36 Marco Lazzeri <marco.lazzeri@noze.it>
0 siblings, 0 replies; 6+ messages in thread
From: Marco Lazzeri @ 2004-04-18 14:36 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I would like to concatenate results of a single column query, something
like the futurable (dev docs for 7.5)
SELECT
p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
FROM people AS p
Any suggestions?
Thanks
Marco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Concatenate results of a single column query
@ 2004-04-18 15:44 Marco Lazzeri <marcomail@noze.it>
2004-04-18 17:42 ` Re: Concatenate results of a single column query Greg Stark <gsstark@mit.edu>
0 siblings, 1 reply; 6+ messages in thread
From: Marco Lazzeri @ 2004-04-18 15:44 UTC (permalink / raw)
To: pgsql-sql
Hi All,
I would like to concatenate results of a single column query using
PostgreSQL 7.3.
Something like the coming feature
SELECT
p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
FROM people AS p
(devel docs for 7.5:
http://developer.postgresql.org/docs/postgres/sql-expressions.html )
Any suggestions?
Thanks
Marco
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Concatenate results of a single column query
2004-04-18 15:44 Concatenate results of a single column query Marco Lazzeri <marcomail@noze.it>
@ 2004-04-18 17:42 ` Greg Stark <gsstark@mit.edu>
1970-01-01 00:00 ` Re: Concatenate results of a single column query Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 6+ messages in thread
From: Greg Stark @ 2004-04-18 17:42 UTC (permalink / raw)
To: Marco Lazzeri <marcomail@noze.it>; +Cc: pgsql-sql
Marco Lazzeri <marcomail@noze.it> writes:
> SELECT
> p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
> FROM people AS p
> Any suggestions?
Something like:
db=> create aggregate array_aggregate (basetype = integer, sfunc = array_append, stype = integer[], initcond = '{}');
CREATE AGGREGATE
db=> select array_aggregate(id) from tab;
array_aggregate
------------------------------------------------------------------------------------------------------------------------------------------------------------
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,29,30,31,32,33,34,36,37,38,39,40,41,42,43,27,26,44,45,46,47,48,49,50,51,52,53,54,35}
(1 row)
--
greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Concatenate results of a single column query
2004-04-18 15:44 Concatenate results of a single column query Marco Lazzeri <marcomail@noze.it>
2004-04-18 17:42 ` Re: Concatenate results of a single column query Greg Stark <gsstark@mit.edu>
@ 1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2004-04-19 13:45 ` Re: Concatenate results of a single column query Greg Stark <gsstark@mit.edu>
2004-04-19 15:47 ` Re: Concatenate results of a single column query CoL <col@mportal.hu>
0 siblings, 2 replies; 6+ messages in thread
From: Christoph Haller @ 1970-01-01 00:00 UTC (permalink / raw)
To: gsstark@mit.edu; +Cc: pgsql-sql
>
>
> Marco Lazzeri <marcomail@noze.it> writes:
>
> > SELECT
> > p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
> > FROM people AS p
>
> > Any suggestions?
>
> Something like:
>
> db=> create aggregate array_aggregate (basetype = integer, sfunc = array_append, stype = integer[], initcond = '{}');
> CREATE AGGREGATE
>
> db=> select array_aggregate(id) from tab;
> array_aggregate
> ------------------------------------------------------------------------------------------------------------------------------------------------------------
> {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,29,30,31,32,33,34,36,37,38,39,40,41,42,43,27,26,44,45,46,47,48,49,50,51,52,53,54,35}
> (1 row)
>
> --
> greg
>
>
Interesting feature, but I cannot find function array_append:
ERROR: AggregateCreate: function array_append(integer[], integer) does not exist
TIA
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Concatenate results of a single column query
2004-04-18 15:44 Concatenate results of a single column query Marco Lazzeri <marcomail@noze.it>
2004-04-18 17:42 ` Re: Concatenate results of a single column query Greg Stark <gsstark@mit.edu>
1970-01-01 00:00 ` Re: Concatenate results of a single column query Christoph Haller <ch@rodos.fzk.de>
@ 2004-04-19 13:45 ` Greg Stark <gsstark@mit.edu>
1 sibling, 0 replies; 6+ messages in thread
From: Greg Stark @ 2004-04-19 13:45 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: gsstark@mit.edu; pgsql-sql
Christoph Haller <ch@rodos.fzk.de> writes:
> Interesting feature, but I cannot find function array_append:
> ERROR: AggregateCreate: function array_append(integer[], integer) does not exist
It's new in Postgres 7.4
I think you could do this in 7.3 though, it would just be more awkward. Try ||
but I think that's new in 7.4 as well. Otherwise I think you would have to
pick out the upper bound of the array with array_dims and set the upper+1'th
element of the array.
If you're doing text you may want to go directly to a textual concatenation
like:
CREATE FUNCTION concat_agg_accum(text, text) RETURNS text
AS 'select $1 || '', '' || $2'
LANGUAGE sql IMMUTABLE STRICT;
CREATE AGGREGATE concat_agg (
BASETYPE = text,
SFUNC = concat_agg_accum,
STYPE = text
);
--
greg
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Concatenate results of a single column query
2004-04-18 15:44 Concatenate results of a single column query Marco Lazzeri <marcomail@noze.it>
2004-04-18 17:42 ` Re: Concatenate results of a single column query Greg Stark <gsstark@mit.edu>
1970-01-01 00:00 ` Re: Concatenate results of a single column query Christoph Haller <ch@rodos.fzk.de>
@ 2004-04-19 15:47 ` CoL <col@mportal.hu>
1 sibling, 0 replies; 6+ messages in thread
From: CoL @ 2004-04-19 15:47 UTC (permalink / raw)
To: pgsql-sql
hi,
Christoph Haller wrote:
> Interesting feature, but I cannot find function array_append:
> ERROR: AggregateCreate: function array_append(integer[], integer) does not exist
try with pg 7.4
C.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-04-19 15:47 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-04-18 14:36 Concatenate results of a single column query Marco Lazzeri <marco.lazzeri@noze.it>
2004-04-18 15:44 Concatenate results of a single column query Marco Lazzeri <marcomail@noze.it>
2004-04-18 17:42 ` Greg Stark <gsstark@mit.edu>
1970-01-01 00:00 ` Christoph Haller <ch@rodos.fzk.de>
2004-04-19 13:45 ` Greg Stark <gsstark@mit.edu>
2004-04-19 15:47 ` CoL <col@mportal.hu>
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