Re: function on trigger

On 08/31/11 3:39 PM, Marcos Hercules Santos wrote:
> I'm newbie in Psql and I'm trying to build one function in order to
> count the products for each supplier. So i'm gonna put it quite simply
> though this example
>
>
> Please, consider a table called books with the following fields
>
> bookid, title, price, idPublisher
>
>
>
> and one another table called publisher
>
> Idpublisher, name, city, Books
>
>
> Being in that last field from Publisher, called book, I gotta have the
> amount of published books for each publisher.

get rid of the books field on your publisher table, thats dynamic and
changes as you add/remove books from the book table. to get that data,
try...

SELECT p.idPublisher, p.name, p.city, COUNT(b.bookid) AS books FROM
publisher p JOIN books b USING idPublisher GROUP BY p.idPublisher;

you could make this a view if its too cumbersome.

CREATE VIEW publisher_books SELECT p.idPublisher, p.name,
p.city, COUNT(b.bookid) AS books FROM publisher p JOIN books b USING
idPublisher GROUP BY p.idPublisher;

SELECT * from publisher_books;

and of course, add other WHERE conditions...

SELECT books FROM publisher_books WHERE name=?;

--
john r pierce N 37, W 122
santa cruz ca mid-left coast