From: Alvaro Herrera <alvherre@dcc.uchile.cl>
To: nickf@doxpop.com
Cc: pgsql-sql@postgresql.org
Subject: Re: interesting SQL puzzle - concatenating column with itself.
Date: Sun, 8 May 2005 17:29:05 -0400
Message-ID: <20050508212905.GA14422@dcc.uchile.cl> (raw)
In-Reply-To: <427E60BC.8060701@ontko.com>
References: <427E60BC.8060701@ontko.com>
On Sun, May 08, 2005 at 01:55:56PM -0500, Nick Fankhauser wrote:
>
> Hi- I'm doing a conversion from an older database in which a memo field was
> handled by storing one line per record and then displaying the related
> records in order. I want to compress all of the lines into a single text
> field with one record per memo entry.
>
>
> So for instance, the old database looks like this:
>
>
> memo_id | sequence | memo_text
> ---------------------------------------
> 666 | 1 | The quick
> 666 | 2 | red fox
> 666 | 3 | jumped over
> 666 | 4 | the lazy brown dog
>
>
> And my goal is to transform each group of lines into a single record that
> looks like this:
>
>
> memo_id | memo_text
> ----------------------------------------------------------
> 666 | The quick red fox jumped over the lazy brown dog
>
>
> Any thoughts on how to do this via sql? I could write a little program to
> do it, but it seems like there must be a pure-SQL solution that I'm not
> seeing.
You can create a custom aggregate to do concatenation and group by
memo_id.
create or replace function text_cat(text, text) returns text called on null input
language sql immutable as 'select case when $1 is null then $2 when $2
is null then $1 else $1 || '' '' || $2 end';
create aggregate textcat (basetype = text, sfunc = text_cat, stype = text);
create table memos (memo_id int, sequence int, memo_text text);
insert into memos values (666, 3, 'jumped over');
insert into memos values (666, 1, 'The quick');
insert into memos values (666, 4, 'the lazy brown dog');
insert into memos values (666, 2, 'red fox');
select memo_id, textcat(memo_text) from (select * from memos order by
memo_id, sequence) as foo group by memo_id;
The order is not really guaranteed, though if this is a one-shot thing,
you may get away with turning off hashed aggregates.
--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"There was no reply" (Kernel Traffic)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: alvherre@dcc.uchile.cl, nickf@doxpop.com
Subject: Re: interesting SQL puzzle - concatenating column with itself.
In-Reply-To: <20050508212905.GA14422@dcc.uchile.cl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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