Skip site navigation (1) Skip section navigation (2)

Re: sum the text of a text field

From: Sim Zacks <sim(at)compulab(dot)co(dot)il>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: sum the text of a text field
Date: 2008-10-29 11:49:33
Message-ID: ge9ikd$pqu$1@news.hub.org (view raw or flat)
Thread:
Lists: pgsql-general
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It was easier then I thought.

I built a custom function because I wanted each field value to be on its
own line.

create or replace function textsum(text,text) returns text as
$$
	select coalesce($1,'') || case when $1 is null then '' else case 	when
$2 is not null then E'\n' else '' end end || 				coalesce($2,'');
$$ language sql;

  create aggregate sum(text)
  (
	sfunc=textsum,
	stype=text
  );

Sim

Sim Zacks wrote:
> Is there any way (aside from creating a new aggregate type) to sum the
> text in a text field. I would like to group on a query and concatenate
> all the values of a specific field in the result set.
> 
> This is a common practice and currently I find myself writing functions
> to iterate the data to basically build my own text sum function.
> 
> I'm going to look into building my own aggregate type, but if there is
> something out there that works it will make life simpler.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkkITc0ACgkQjDX6szCBa+qcawCg2N9Xt9gnX0tvhaYE7iDJE5Lt
pwYAoIRipArzjODonqzr00peTP933yPY
=DNJz
-----END PGP SIGNATURE-----

In response to

pgsql-general by date

Next:From: Glyn AstillDate: 2008-10-29 11:56:44
Subject: Autovacuum and relfrozenxid
Previous:From: A. KretschmerDate: 2008-10-29 11:49:17
Subject: Re: sum the text of a text field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group