From: Nick Fankhauser <nickf@ontko.com>
To: Alvaro Herrera <alvherre@dcc.uchile.cl>
Cc: nickf@doxpop.com
Cc: pgsql-sql@postgresql.org
Subject: Re: interesting SQL puzzle - concatenating column with itself.
Date: Sun, 08 May 2005 16:48:26 -0500
Message-ID: <427E892A.1000107@ontko.com> (raw)
In-Reply-To: <20050508212905.GA14422@dcc.uchile.cl>
References: <427E60BC.8060701@ontko.com>
<20050508212905.GA14422@dcc.uchile.cl>
Excellent! Thanks for providing both the idea and an example. I didn't get the idea right away, but the example made it clear. I'll try that on my table and report back on how it works out.
Regards,
-Nick
------------------------------------------------------------------
Nick Fankhauser
nickf@ontko.com Phone 765.935.4283 Fax 765.962.9788
Ray Ontko & Co. - Software Consulting Services
http://www.ontko.com
Alvaro Herrera wrote:
>
> 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.
>
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: nickf@ontko.com, alvherre@dcc.uchile.cl, nickf@doxpop.com
Subject: Re: interesting SQL puzzle - concatenating column with itself.
In-Reply-To: <427E892A.1000107@ontko.com>
* 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