Re: Non-procedural field merging?

"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