Thread: function on trigger
hi guys 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.
On Aug 31, 2011, at 18:39, Marcos Hercules Santos <mhercs@gmail.com> wrote: > hi guys > > 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. Is there a question somewhere? > Do not be tempted by the dark side Marcos. Create a view that uses a join and a count. Only if you have significant performanceissues would you then consider materializing that view. If you insist, or simply want to learn, read the sections on UPDATE, CREATE TRIGGER, and CREATE FUNCTION. Try doing it followingthose examples. Ask more specific questions if something specific stumps you. Do it incrementally. Get the raw SQL UPDATE working then create the trigger and function and get those working. Then combinethe two. David J.
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
On 09/01/2011 01:39 AM, Marcos Hercules Santos wrote: <blockquote cite="mid:b3ba2b32-278f-4216-9de3-39ab2162fc23@y21g2000yqy.googlegroups.com"type="cite"><pre wrap="">hi guys 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. </pre></blockquote> One way to do this would be with a view, so you don't need the trigger. The other one would need to beafter update, insert and delete. This would include code such as:<br /> update publisher set books=books[+|-]1 where idpublisher=[new|old].idpublisher;<br/><br /> Sim<br />
On Aug 31, 7:39 pm, Marcos Hercules Santos <mhe...@gmail.com> wrote: > hi guys > > 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. sorry guys, the last field in the publisher table is for the amount of books published by each Publisher Idpublisher, name, city, Books, amount_Books_ Guys, I'm discarding creating any view. I already have the query to count the data, but my problem is how to insert this data in the amount_books_ using function... thank goodness for the reply