How can I reduce the number of queries to my PostgreSQL database?

[Martin Christensen]

>>>>> "SR" ==   <shay at shay-riggs.fsnet.co.uk> writes:
SR> Scenario: I have a python script which creates web page listing
SR> all books in the database, and all authors for each book. My
SR> python script does essentially three things:

SR> 1. retrieve a list of all book_ids and book_titles.

SR> 2. for each book_id, query the bookauthors table and retrieve all
SR> author names for that book_id.

SR> 3. display it all out as an html table on a web page.

That's one query, if you're willing to make it advanced enough,
although you need to make an aggregate to enable PostgreSQL to
concatenate and comma separate author names. However, this aggregate
will typically need more than one database function. Such an aggregate
could be as follows:

CREATE OR REPLACE FUNCTION author_agg_sfunc(TEXT, authors.name%TYPE)
RETURNS TEXT AS '
SELECT $1 || '', '' || $2;
' LANGUAGE sql;

CREATE OR REPLACE FUNCTION author_agg_ffunc(TEXT)
RETURNS TEXT AS '
SELECT trim(trailing '', '' from $1);
' LANGUAGE sql;

CREATE AGGREGATE author_agg (
  basetype  = VARCHAR(100),
  sfunc     = author_agg_sfunc,
  stype     = TEXT,
  finalfunc = author_agg_ffunc,
  initcond  = ''
);

Then you could use it as follows:

SELECT author_agg(authors.name),
       foo,
       bar
  FROM authors, writes, books
 WHERE authors.id     = writes.author_id
   AND writes.book_id = books.id
 GROUP BY foo, bar;

This is the solution that I would use after working nearly a decade
with databases. It is neither simple nor obvious to the novice, but
it's the Right Way To Do It. For a learning exercise, this is way over
the top, but I thought you might benefit from seeing that - so long as
you only need information that would reasonably fit in one table on a
web page or the equivalent - one query is always enough. Or perhaps
that should be One Query Is Always Enough. :-) Learn at your own pace,
though, but you might want to keep this in mind for future reference.

Martin