From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Non-procedural field merging?
Date: Fri, 05 Jan 2001 11:53:49 -0500
Message-ID: <10278.978713629@sss.pgh.pa.us> (raw)
In-Reply-To: <000f01c076fe$2e27b9a0$1001a8c0@archonet.com>
References: <000f01c076fe$2e27b9a0$1001a8c0@archonet.com>
"Richard Huxton" <dev@archonet.com> writes:
> I have two tables, foo and foo2:
> richardh=> select * from foo;
> a | b
> ---+-----
> 1 | xxx
> 1 | yyy
> richardh=> select * from foo2;
> c | d
> ---+---
> 1 |
> And I would like to set d to 'xxxyyy' (i.e. merge entries from b).
You could do it with a user-defined aggregate function (initial
value '' and transition function ||). I am not sure that aggregates
work in an intelligent way in UPDATE --- ie, I am not sure it would
work to do
update foo2 set d = catenate(foo.b) from foo where foo.a=foo2.c;
I seem to recall some discussion concluding that that didn't have
very well-defined semantics. But you could do
SELECT a, catenate(b) INTO TEMP TABLE t1 FROM foo GROUP BY a;
and then update into foo2 from the temp table.
> PS - I realise I might get 'xxxyyy' or 'yyyxxx' without forcing an order but
> I don't actually care in this case.
Check. You don't have any control over the order in which input rows
will be presented to an aggregate function.
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, dev@archonet.com
Subject: Re: Non-procedural field merging?
In-Reply-To: <10278.978713629@sss.pgh.pa.us>
References: <000f01c076fe$2e27b9a0$1001a8c0@archonet.com> <10278.978713629@sss.pgh.pa.us>
* 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