X-Original-To: pgsql-sql@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 5AB19924C2E for ; Tue, 27 May 2003 14:33:35 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 57690-05 for ; Tue, 27 May 2003 14:33:07 -0400 (EDT) Received: from mx03.gis.net (mx03.gis.net [208.218.130.11]) by developer.postgresql.org (Postfix) with ESMTP id 2CD18924C25 for ; Tue, 27 May 2003 14:33:07 -0400 (EDT) Received: from shell.gis.net ([208.218.129.18]) by mx03.gis.net; Tue, 27 May 2003 14:33:05 -0400 Date: Tue, 27 May 2003 14:27:28 -0400 (EDT) From: Chadwick Rolfs To: pgsql-sql@postgresql.org Subject: Re: [PHP] faster output from php and postgres In-Reply-To: <200305271919.05735.dev@archonet.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Rcpt-To: X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/523 X-Sequence-Number: 13688 On Tue, 27 May 2003, Richard Huxton wrote: > On Tuesday 27 May 2003 5:34 pm, Chadwick Rolfs wrote: > > So, I have the same problem, but I need all authors for each publication > > to show up in it's own column. I tried the full join query from a > > suggestion off pgsql-sql, but it only returns ONE author id TWICE instead > > of ALL authors at once. > > > > I'll do some RTFMing of the joins.. and post any results I get > > > > BUT, right now, looping over each publication with php isn't taking that > > long. I would like to know how to make this query, though! > > > > Please let me know how to get a result like: > > ____________________________________________________ > > > > |All Authors|Title|Source|Year|Type|Length|Keywords| > > Well, if you search the archives for terms "text", "concat", "aggregate" you > should come up with one solution. This involves writing your own aggregate > function, like SUM() but for text. Don't worry, it's not difficult. The only > issue is that you won't be able to guarantee the order of authors in the > field. > > There might be something on this in my PostgreSQL Notes on > http://techdocs.postgresql.org/ too - can't remember for sure. > > -- > Richard Huxton A function is definitely in need. But I already have PHP doing something of this sort. I realize that PostgreSQL would most likely do it faster. What I meant by the result output above was that there would be _many_ results, but for each publication only ONE column for ALL authors in EACH row. (*whew*) So perhaps a function, but not an aggregate function. I'll look into functions next, as it seems that there is no single SQL statement that can do this... I can't see how. -Chadwick