Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: Chadwick Rolfs <cmr@shell.gis.net>, Frank Bax <fbax@sympatico.ca>
Subject: Re: [SQL] faster output from php and postgres
Date: Tue, 27 May 2003 19:19:05 +0100
User-Agent: KMail/1.4.3
Cc: pgsql-php@postgresql.org, pgsql-sql@postgresql.org
References: <Pine.GSO.4.05.10305271222540.10146-100000@shell.gis.net>
In-Reply-To: <Pine.GSO.4.05.10305271222540.10146-100000@shell.gis.net>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200305271919.05735.dev@archonet.com>

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" yo=
u=20
should come up with one solution. This involves writing your own aggregate=
=20
function, like SUM() but for text. Don't worry, it's not difficult. The onl=
y=20
issue is that you won't be able to guarantee the order of authors in the=20
field.

There might be something on this in my PostgreSQL Notes on=20
http://techdocs.postgresql.org/ too - can't remember for sure.

--=20
  Richard Huxton