Re: Non-procedural field merging?

"Richard Huxton" <dev(at)archonet(dot)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