Skip site navigation (1) Skip section navigation (2)

Re: faster output from php and postgres

From: Rod Taylor <rbt(at)rbt(dot)ca>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Chadwick Rolfs <cmr(at)shell(dot)gis(dot)net>,Frank Bax <fbax(at)sympatico(dot)ca>, pgsql-php(at)postgresql(dot)org,pgsql-sql(at)postgresql(dot)org
Subject: Re: faster output from php and postgres
Date: 2003-05-27 18:54:36
Message-ID: 1054061675.93507.40.camel@jester (view raw or flat)
Thread:
Lists: pgsql-phppgsql-sql
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 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.

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.


-- 
Rod Taylor <rbt(at)rbt(dot)ca>

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

In response to

pgsql-php by date

Next:From: Jean-Luc LachanceDate: 2003-05-27 19:35:39
Subject: Re: [SQL] faster output from php and postgres
Previous:From: Chadwick RolfsDate: 2003-05-27 18:27:28
Subject: Re: [PHP] faster output from php and postgres

pgsql-sql by date

Next:From: Jean-Luc LachanceDate: 2003-05-27 19:35:39
Subject: Re: [SQL] faster output from php and postgres
Previous:From: Chadwick RolfsDate: 2003-05-27 18:27:28
Subject: Re: [PHP] faster output from php and postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group