Project Summary

-- ============================================================
-- Detailed and Summary Tables
-- ============================================================
CREATE TABLE detailed_table (
    rental_id     INTEGER,
    rental_date   TIMESTAMP,
    inventory_id  INTEGER,
    film_id       INTEGER,
    title         VARCHAR(255),
    customer_id   INTEGER
);
CREATE TABLE summary_table (
   month          VARCHAR(20),
   total_rentals  INTEGER
);
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'detailed_table';
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = 'summary_table';
-- ============================================================
-- User Designed Function
-- ============================================================
CREATE OR REPLACE FUNCTION get_rental_month(rental_date TIMESTAMP)
    RETURNS VARCHAR AS $$
BEGIN
    RETURN TO_CHAR(rental_date, 'FMMonth YYYY');
END;
$$ LANGUAGE plpgsql;
-- Example for function
SELECT get_rental_month('2006-02-14 10:00:00'::TIMESTAMP) AS sample_transformed_month;
-- ============================================================
-- Raw data extraction (preview only — actual load happens in later)
-- ============================================================
SELECT
    r.rental_id,
    r.rental_date,
    r.inventory_id,
    i.film_id,
    f.title,
    r.customer_id
FROM rental r
         JOIN inventory i ON r.inventory_id = i.inventory_id
         JOIN film f ON i.film_id = f.film_id;
-- ============================================================
-- Trigger function + trigger (created together)
-- ============================================================
CREATE OR REPLACE FUNCTION update_summary_table()
    RETURNS TRIGGER AS $$
DECLARE
    rental_month VARCHAR;
BEGIN
    rental_month := get_rental_month(NEW.rental_date);
    IF EXISTS (SELECT 1 FROM summary_table WHERE month = rental_month) THEN
        UPDATE summary_table
        SET total_rentals = total_rentals + 1
        WHERE month = rental_month;
    ELSE
        INSERT INTO summary_table (month, total_rentals)
        VALUES (rental_month, 1);
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Trigger for the updating of the summary table
-- It will trigger on an insertion into the detailed table
-- Each row that is inserted gets the update_summary_table function
CREATE TRIGGER trg_update_summary_table
    AFTER INSERT ON detailed_table
    FOR EACH ROW
EXECUTE FUNCTION update_summary_table();
-- ============================================================
-- Stored procedure
-- ============================================================
CREATE OR REPLACE PROCEDURE refresh_report()
    LANGUAGE plpgsql
AS $$
BEGIN
    TRUNCATE TABLE summary_table;
    TRUNCATE TABLE detailed_table;
    INSERT INTO detailed_table (rental_id, rental_date, inventory_id, film_id, title, customer_id)
    SELECT
        r.rental_id,
        r.rental_date,
        r.inventory_id,
        i.film_id,
        f.title,
        r.customer_id
    FROM rental r
             JOIN inventory i ON r.inventory_id = i.inventory_id
             JOIN film f ON i.film_id = f.film_id;
END;
$$;
-- Before calling the procedure (tables are empty)
SELECT COUNT(*) FROM detailed_table;
SELECT COUNT(*) FROM summary_table;
CALL refresh_report();
-- After calling the procedure (populated these tables)
SELECT COUNT(*) FROM detailed_table;
SELECT COUNT(*) FROM summary_table;
SELECT * FROM detailed_table LIMIT 10;
-- Final chronological report
SELECT month, total_rentals
FROM summary_table
ORDER BY TO_DATE(month, 'Month YYYY');
-- ============================================================
-- Live trigger demo
-- ============================================================
-- BEFORE (182 Rentals)
SELECT * FROM summary_table WHERE month = 'February 2006';
-- Dummy data for showing purposes
INSERT INTO detailed_table (rental_id, rental_date, inventory_id, film_id, title, customer_id)
VALUES (99999, '2006-02-14 10:00:00', 1, 1, 'TEST TITLE', 1);
-- AFTER (183 Rentals)
SELECT * FROM summary_table WHERE month = 'February 2006';
-- Runs at midnight on the 1st of every month.
SELECT cron.schedule('monthly-report-refresh', '0 0 1 * *', 'CALL refresh_report()');