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

    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
    Posts
    3
    sorry,

    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
    Location
    Glasgow, UK
    Posts
    36

    Cool

    Hello marcos_Brz!

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

    Using your example:
    Code:
    --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
    Code:
    create view publisher_books as
    select a.Idpublisher, a.name, a.city, count(1) total_books
    from publisher a
    inner join books b
    on a.idPublisher=b.idPublisher
    group by a.Idpublisher, a.name, a.city;
    Select from the view
    Code:
    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
    Location
    Glasgow, UK
    Posts
    36
    Afterthought...

    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
  •