Subject: Re: faster output from php and postgres
From: Rod Taylor <rbt@rbt.ca>
To: Richard Huxton <dev@archonet.com>
Cc: Chadwick Rolfs <cmr@shell.gis.net>,
	Frank Bax <fbax@sympatico.ca>, pgsql-php@postgresql.org,
	pgsql-sql@postgresql.org
In-Reply-To: <200305271919.05735.dev@archonet.com>
References: <Pine.GSO.4.05.10305271222540.10146-100000@shell.gis.net>
	<200305271919.05735.dev@archonet.com>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-rZDO1AjUTV8uz8SzqNdS"
Organization: 
Message-Id: <1054061675.93507.40.camel@jester>
Mime-Version: 1.0
Date: 27 May 2003 14:54:36 -0400

--=-rZDO1AjUTV8uz8SzqNdS
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

On Tue, 2003-05-27 at 14:19, 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 inste=
ad
> > 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|
>=20
> Well, if you search the archives for terms "text", "concat", "aggregate" =
you=20
> should come up with one solution. This involves writing your own aggregat=
e=20
> function, like SUM() but for text. Don't worry, it's not difficult. The o=
nly=20
> issue is that you won't be able to guarantee the order of authors in the=
=20
> field.

If order is required:

SELECT custom_aggregate(author) as authors
  FROM (SELECT author FROM table ORDER BY author) AS tab;

The above should give you authors in alphabetical order if
custom_aggregate() was written to concatenate text.


--=20
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

--=-rZDO1AjUTV8uz8SzqNdS
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: This is a digitally signed message part

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (FreeBSD)

iD8DBQA+07Rr6DETLow6vwwRAoN1AJ4hRSmJGcwQDaHREofEY9iJjhZdAACcDpQV
00AMuAv0JwkKfhzBDD7voDY=
=h85N
-----END PGP SIGNATURE-----

--=-rZDO1AjUTV8uz8SzqNdS--