gistfile1.sql

-- There is a monetary type but it is obsolete.
DROP DOMAIN IF EXISTS currency CASCADE;
CREATE DOMAIN currency AS numeric(10, 2);

DROP TABLE IF EXISTS products CASCADE;
CREATE TABLE products
(
    id serial NOT NULL,
    "name" character varying(50) NOT NULL UNIQUE,
    CONSTRAINT product_pkey PRIMARY KEY (id)
)
WITH (
    OIDS=FALSE
);

\COPY products ("name") FROM 'products.csv' WITH CSV

DROP TABLE IF EXISTS prices CASCADE;
CREATE TABLE prices
(
    id serial NOT NULL,
    product_id integer NOT NULL,
    "price" currency NOT NULL,
    "from_date" date NOT NULL DEFAULT 'today',
    CONSTRAINT price_pkey PRIMARY KEY (id),
    CONSTRAINT product_exists FOREIGN KEY (product_id)
               REFERENCES products (id)
               ON DELETE CASCADE
               ON UPDATE CASCADE
)
WITH (
    OIDS=FALSE
);
CREATE INDEX price_product_id_idx ON prices (product_id);
CREATE INDEX price_from_date_idx ON prices (from_date);

\COPY prices ("product_id", "price") FROM 'prices.csv' WITH CSV

--DROP VIEW IF EXISTS product_prices CASCADE;
--CREATE VIEW product_prices AS
--    SELECT name, price 
--           FROM products, prices 
--           WHERE products.id = prices.product_id AND 
--                 prices.from_date = (SELECT max(prices.from_date) FROM products, prices WHERE prices.from_date <= 'today' AND products.id = prices.product_id);

DROP VIEW IF EXISTS product_prices CASCADE;
CREATE VIEW product_prices AS
    SELECT name, price
           FROM products AS prod, prices AS pric
           WHERE prod.id = pric.product_id AND 
                 pric.from_date = (SELECT max(from_date) FROM prices WHERE prices.from_date <= 'today' AND prices.product_id = prod.id);