-- create tables, items holds item number, name and amount sold.
-- sales holds sales transactions 
CREATE TABLE items2 (itemno int, itemname char(20));
CREATE TABLE sales2 (itemno int, price decimal(6, 2));

--create a discount function
CREATE OR REPLACE FUNCTION discount(numeric) RETURNS numeric AS $disc$
  SELECT $1 * 0.8;
$disc$ LANGUAGE SQL;


--insert data
insert  into items2(itemno, itemname)
 values (5, 'book'),
        (6, 'bell'),
        (7, 'candle');

insert  into sales2(itemno, price)
 values (5, 6.50),
        (6, 7.25),
        (6, 9.25);

select  itemno, discount(price) from sales2;

-- create an insert and calculate sales function
CREATE OR REPLACE FUNCTION add_and_total(int, numeric) RETURNS numeric AS $aas$
  INSERT into sales2(itemno, price)
   VALUES ($1, $2);
  SELECT SUM(price)
  FROM sales2
  WHERE itemno = $1;
$aas$ LANGUAGE SQL;

-- use the and and total function
SELECT add_and_total(6, 8.00);
SELECT * FROM sales2;

-- create a function to return sales of an item in pgsql
CREATE OR REPLACE FUNCTION total_sales(int) RETURNS numeric AS $ts$
BEGIN
  RETURN (SELECT SUM(price) FROM sales2 WHERE itemno = $1);
END $ts$ LANGUAGE 'plpgsql';

-- use this function
SELECT itemname, total_sales(itemno)
FROM items2;

-- handle the case where there are no sales yet
CREATE OR REPLACE FUNCTION total_sales(int) RETURNS numeric AS $ts$
DECLARE
  itemcount int; 
BEGIN
  SELECT count(*) INTO itemcount FROM sales2 WHERE itemno = $1;
  IF itemcount = 0 THEN RETURN 0.0;
  ELSE RETURN (SELECT SUM(price) FROM sales2 WHERE itemno = $1);
  END IF;
END $ts$ LANGUAGE 'plpgsql';

-- use this function
SELECT itemname, total_sales(itemno)
FROM items2;