From: Chadwick Rolfs <cmr@shell.gis.net>
To: pgsql-sql@postgresql.org
Subject: Re: [PHP] faster output from php and postgres
Date: Tue, 27 May 2003 14:27:28 -0400 (EDT)
Message-ID: <Pine.GSO.4.05.10305271422550.10146-100000@shell.gis.net> (raw)
In-Reply-To: <200305271919.05735.dev@archonet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cmr@shell.gis.net
Subject: Re: [PHP] faster output from php and postgres
In-Reply-To: <Pine.GSO.4.05.10305271422550.10146-100000@shell.gis.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox