Received: from sss.pgh.pa.us (sss.pgh.pa.us [209.114.132.154]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f05GrV713546 for ; Fri, 5 Jan 2001 11:53:31 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.1/8.11.1) with ESMTP id f05GroB10281; Fri, 5 Jan 2001 11:53:50 -0500 (EST) To: "Richard Huxton" cc: pgsql-sql@postgresql.org Subject: Re: Non-procedural field merging? In-reply-to: <000f01c076fe$2e27b9a0$1001a8c0@archonet.com> References: <000f01c076fe$2e27b9a0$1001a8c0@archonet.com> Comments: In-reply-to "Richard Huxton" message dated "Fri, 05 Jan 2001 09:59:20 +0000" Date: Fri, 05 Jan 2001 11:53:49 -0500 Message-ID: <10278.978713629@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200101/62 X-Sequence-Number: 723 "Richard Huxton" 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