"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
