gistfile1.sql

CREATE OR REPLACE FUNCTION uspGetPubStatsADA (publishcode text)
-- Publisher Code
RETURNS text AS
$$

DECLARE pubcode text;
  pubname text;
	AuthorCount int;
	TotalbookCount int;
	Booktitle text;
	onHandValues int;
	SumofonHand int;
	
BEGIN

-- Get the published code from supplied code

	Select publishercode INTO pubcode
	from publisher
	where publishercode = publishcode;


-- check if publisher exists

IF (pubcode is null)
THEN 
     RAISE NOTICE 'No publisher exists with the given publishercode (%) ', publishcode; 
     
     RETURN (-1);
END IF;

Select publishername INTO pubname
from publisher
Where publishercode = publishcode;


       
RAISE NOTICE 'Publisher Name: (%) ', pubname ;

Select count( DISTINCT author.authornum) INTO AuthorCount
from book,wrote,author
Where book.publishercode = publishcode AND
book.bookcode = wrote.bookcode AND wrote.authornum = author.authornum;

RAISE NOTICE 'Number of distinct authors for publisher:  (%) ', AuthorCount ;

Select count(bookcode) INTO TotalbookCount
from book
Where book.publishercode = publishcode;

RAISE NOTICE 'Number of different books published:  (%) ', TotalbookCount;

Select title INTO Booktitle 
from book,wrote,author
Where book.publishercode = publishcode AND
book.bookcode = wrote.bookcode AND wrote.authornum = author.authornum;

RAISE NOTICE 'Number of distinct authors for publisher:  (%) ', AuthorCount;

Create table temp AS
Select title, sum(onHand) as BigSum
From book,inventory
Where book.bookcode = inventory.bookcode AND
book.publishercode = publishcode
Group by book.title;

Select title INTO Booktitle
From temp
Where BigSum = (Select max(BigSum)
               From temp);

RAISE NOTICE 'The book title with the most copies from the publisher is: (%)  ', Booktitle;

               
Select max(Bigsum) INTO onHandValues
From (SELECT sum(onhand) as Bigsum,
  book.title  
FROM 
  public.book, 
  public.inventory
WHERE 
  book.bookcode = inventory.bookcode AND
  book.publishercode = publishcode
Group by book.title) as Bignum ;

RAISE NOTICE 'Number of copies for the above book:  (%)', onHandValues;


Select sum(SumofHand) INTO SumofonHand
From (SELECT sum(onhand) as SumofHand
  FROM   book,  inventory
  WHERE 
  book.bookcode = inventory.bookcode AND
  book.publishercode = publishcode)as foo;

RAISE NOTICE 'Cummulative Sum of onHand books for publisher:  (%)', SumofonHand ;


DROP TABLE temp;
RETURN (0);
END;
$$ language plpgsql;




SELECT uspGetPubStatsADA ('JP');
SELECT uspGetPubStatsADA ('AP');
SELECT uspGetPubStatsADA ('PL');