Re: [SQL] faster output from php and postgres (one resolution)

My solution to the problem stated below was to learn plpgsql, and write a
function that loops through the returned records, concatenating a string
together, and returning that string as the Author field.
This function was actually e-mailed to me by a collegue, and I did a bit
of doctoring (it still needs it) to get it functional (pun perhaps
intended :>):

----------------------------------------------------------------
create function show_auth(integer) returns varchar as '
declare
a record;
b alias for $1;
s varchar;
c varchar;
begin
s := '''';
c := '''';
for a in
select author.last
from pub_auth, author
where pub_auth.pub_id = b
and author.auth_id = pub_auth.auth_id
order by author.last
loop
s := s || c|| a.last;
c := '', '';
end loop;
return s;
end;
' language 'plpgsql';
----------------------------------------------------------------

Although there were many ways to do this, as Jean-Luc points out below, I
believe in the long run this one is best.
This function will be expanded to actually handle the entire query that
was starting with:

select auth_id
from author
where
lower(last) like lower('%".addslashes($search_term)."%')
order by last, first

-OR-

select distinct pub_auth.pub_id, title, source, year, type, length,
copyright, abstract, pdformat, keywords
from publication, pub_auth
where
keywords like '%".addslashes($search_term)."%'
and
publication.pub_id = pub_auth.pub_id
order by year desc, title

In other words, two different functions. I guess.

Hopefully this helps anyone else in the future!

On Tue, 27 May 2003, Jean-Luc Lachance wrote:

> KISS
>
> why not use PHP to concatenate the authors while pub_id is the same???
> If you insist on having each author in its own column,
> put them at the end and concatenate with <TD>.
>
> jll
>
>
> 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|
> > ----------------------------------------------------
> >
> > If there is a way on the SQL side to do this, that is ;>
> >
> > Here we go:
> >
> > CREATE TABLE "author" (
> > "auth_id" integer DEFAULT
> > nextval('"author_temp_auth_id_seq"'::text) NOT NULL,
> > "first" text,
> > "last" text,
> > "auth_last_updated" timestamp with time zone,
> > Constraint "author_temp_pkey" Primary Key ("auth_id")
> > );
> >
> > CREATE UNIQUE INDEX auth_id_author_key ON author USING btree (auth_id);
> >
> > CREATE UNIQUE INDEX auth_last_updated_author_key ON author USING btree
> > (auth_last_updated);
> >
> > CREATE TABLE "publication" (
> > "copyis" text,
> > "pub_id" integer DEFAULT nextval('publication_pub_id_seq'::text)
> > NOT NULL,
> > "title" text,
> > "source" text,
> > "year" text,
> > "month" text,
> > "length" text,
> > "type" text,
> > "keywords" text,
> > "copyright" text,
> > "abstract" text,
> > "pdformat" text,
> > "pub_last_updated" timestamp with time zone
> > );
> >
> > CREATE UNIQUE INDEX publication_pub_id_key ON publication USING btree
> > (pub_id);
> >
> > CREATE INDEX keywords_publication_key ON publication USING btree
> > (keywords);
> >
> > CREATE UNIQUE INDEX pub_last_updated_publication_ke ON publication USING
> > btree (pub_last_updated);
> >
> > CREATE UNIQUE INDEX pub_id_publication_key ON publication USING btree
> > (pub_id);
> >
> > CREATE TABLE "pub_auth" (
> > "pub_auth_id" integer DEFAULT
> > nextval('"pub_auth_temp_pub_auth_id_seq"'::text) NOT NULL,
> > "pub_id" integer,
> > "auth_id" integer,
> > Constraint "pub_auth_temp_pkey" Primary Key ("pub_auth_id")
> > );
> >
> > CREATE INDEX pub_id_pub_auth_key ON pub_auth USING btree (pub_id);
> >
> > CREATE INDEX auth_id_pub_auth_key ON pub_auth USING btree (auth_id);
> >
> > On Sat, 24 May 2003, Frank Bax wrote:
> >
> > > Finding previous examples of complex joins in archives is not likely an
> > > easy thing to find.
> > >
> > > pg_dump -s -t author -t publication -t pub_auth [database] | grep -v ^--
> > >
> > > Change [database] to the name of your database - this command will dump out
> > > schema relative to your request. Post the results to this list. Then ask
> > > us the question "how do I write a SELECT that produces...[ you finish this
> > > sentence]". Question probably more appropriate to the list you mentioned,
> > > but I expect there are people here who are just as capable of answering the
> > > question. I've even seen examples where the process goes through several
> > > emails before SQL produces desired results exactly.
> > >
> > > >How would a join make this easier?
> > >
> > > I have always found that one properly constructed complex query is always
> > > "cheaper" in runtime than numerous queries inside a foreach loop. Your
> > > final query will likely include joining a table to itself (this can
> > > sometimes be a difficult concept to grasp).
> > >
> > > Frank
> > >
> > >
> > > At 11:50 AM 5/24/03, Chadwick Rolfs wrote:
> > >
> > >
> > > >I'm glad this came up, because I have the same type of problem. Except,
> > > >I don't see how a join can work... of course, I'm not really schooled in
> > > >this stuff.
> > > >
> > > >I also have three tables: author, publication, and pub_auth.
> > > >
> > > >There are multiple authors for some publications, so it is necessary to
> > > >check each publication selected for ALL authors. I'm doing this with a
> > > >foreach loop on the result of each publication key returned.
> > > >
> > > >How would a join make this easier?
> > > >
> > > >I'm browsing the pgsql-sql archives now, but that may take a week. I'm
> > > >not sure what to search for...
> > > >
> >
> > -Chadwick
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> > (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

-Chadwick