From: wyatt@draggoo.com
To: pgsql-sql@postgresql.org
Subject: Single VIEW, and FUNCTION questions...
Date: 6 Nov 2001 18:12:03 -0800
Message-ID: <20011107021203.9606.cpmta@c001.snv.cp.net> (raw)
Hey all,
I've got a book database here, and I'm trying to create a single VIEW that lists *all* of the books. Here are the relavent parts:
Data tables: book, author, publisher, category, series, set
These tables store the appropriate data for each table; for instance, the book table stores the ISBN (if any), title, publisher (index to publisher table), copyright year, and a primary key series.
Joining tables: bookauthor, bookcategory, bookseries, bookset
These tables are generally columns containing integers for each of the keys to join them; the bookauthor has a book key and an author key, the bookseries has a book key, series key, and the place in the series (book 1, 2, etc).
What I am trying to do is get a single VIEW to include *all* of the books, as well as the series, place in the series, and set *if* they have them. But, since not all books belong to a series or set, all I have been able to manage is to get several VIEWs: one with all of the books in a series, one with books in a set, one with books in both series *and* sets, and one with the others --- with some overlap (books in sets *and* series, for instance, end up in all three of the VIEWs).
Is there a way to get all of these into a single VIEW with a single query? Or am I going to have to do several queries from Java or Perl and put them together (final project is in Java)?
Also, I'm trying to figure out how much should be implemented as FUNCTIONS in the database, and how much should be in the Java/Web portion. For instance, when a book is deleted out of the book table, should a I delete all of the references to that book in the database with a trigger, or do it with the Java portion?
Thanks,
Wyatt
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: wyatt@draggoo.com
Subject: Re: Single VIEW, and FUNCTION questions...
In-Reply-To: <20011107021203.9606.cpmta@c001.snv.cp.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