Thread: Concatenate results of a single column query

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

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

> 
> 
> 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 

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

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.