Re: SQL query - single text value from group by - Mailing list pgsql-general

mike wrote:
> Hi
>
> I am trying to work out if this is possible in a select query
>
> I have a group by query which could result in several rows, what I want
> to do is do a text equivalent of a sum() eg:
>
> SELECT sum(inv_id),date,cust
> from invoice
> group by date,cust
>
> Is there any way to get to a single concatenated inv_id field with just
> one row?

Yes, you can define your own aggregate. For example, I did this the
other day:

/*
   Custom aggregate
     This aggregate is so we can aggregate text into paragraph blocks
*/
CREATE OR REPLACE FUNCTION join_paras(text, text) RETURNS text AS
'
     SELECT CASE
         WHEN ($1 = '''') THEN $2
         ELSE $1 || ''\n'' || $2
     END;
' LANGUAGE 'SQL' IMMUTABLE;

CREATE AGGREGATE agg_paras (sfunc1=join_paras, basetype=text,
stype1=text, initcond1='');

Note I defined my own text-concatenation function because I wanted to
insert newlines between each block of text. If you just wanted joined
text you could use the built-in textcat()

Full specs of create aggregate are in the manuals.

HTH
--
   Richard Huxton
   Archonet Ltd