Postgres Pro
Downloads
Home   >   mailing lists

Thread: sum the text of a text field

sum the text of a text field

From
Sim Zacks
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

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

iEYEARECAAYFAkkIRxsACgkQjDX6szCBa+pClACgi1gV9FsE4Hj5bdOueHENwq2l
lo0An09lAf160qz/nYMnb0LLCF6VU7Qs
=pkwu
-----END PGP SIGNATURE-----

Re: sum the text of a text field

From
"A. Kretschmer"
Date:
am  Wed, dem 29.10.2008, um 13:20:59 +0200 mailte Sim Zacks folgendes:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> 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.

Do you want to concat all rows to a string?

,----[  Column to string with delimiter, David Fetter  ]
| test=*# select * from w;
|    t
| --------
|  test
|  foo
|  bar
|  foobar
| (4 rows)
|
| test=*# SELECT array_to_string(ARRAY(SELECT t FROM w), ',');
|    array_to_string
| ---------------------
|  test,foo,bar,foobar
| (1 row)
`----



HTH, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

Re: sum the text of a text field

From
Sim Zacks
Date:
-----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-----