Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2011

    Unanswered: function on trigger

    Hi guys

    I'm newbie in Psql and I'm trying to build one functions 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.

  2. #2
    Join Date
    Aug 2011

    the last field in the publisher table is for the amount of books published by each Publisher

    Idpublisher, name, city, Books, amount_Books_

  3. #3
    Join Date
    Aug 2011
    Glasgow, UK


    Hello marcos_Brz!

    Have you considered using a view? It's probably better suited to your example than a trigger.

    Using your example:
    --Create the Tables:
    create table books
    (bookid  int, title varchar(50), price decimal(14,2), idPublisher int);
    create table publisher
    (Idpublisher int , name varchar(50), city varchar(50));
    -- Popluate Tables:
    insert into books values(501, 'Zombie Survival Guide', 5.99, 5);
    insert into books values(502, 'World War Z', 6.99, 5);
    insert into books values(102, 'The Return of Sherlock Holmes', 3.99, 2);
    insert into books values(102, 'The Hound of the Baskervilles', 3.50, 2);
    insert into books values(102, 'The Adventures of Sherlock Holmes', 3.99, 2):
    insert into publisher values(5, 'Random House', 'New York');
    insert into publisher values(2, 'Penguin', 'London');

    Compile the view
    create view publisher_books as
    select a.Idpublisher,,, count(1) total_books
    from publisher a
    inner join books b
    on a.idPublisher=b.idPublisher
    group by a.Idpublisher,,;
    Select from the view
    postgres=# select * from publisher_books;
     idpublisher |     name     |   city   | total_books
               2 | Penguin      | London   |           3
               5 | Random House | New York |           2
    (2 rows)

  4. #4
    Join Date
    Aug 2011
    Glasgow, UK

    If you really just want to learn functions and triggers , I'd suggest having a look at:
    Practical PostgreSQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts