Data warehouse date dimenion script

date_dimenion.sql

CREATE SCHEMA IF NOT EXISTS dimensions;

DROP TABLE dimensions.dates;

CREATE TABLE dimensions.dates (

    id SERIAL PRIMARY KEY,

    date date,

    epoch bigint,

    day_suffix text,

    day_name text,

    day_of_week integer,

    day_of_month integer,

    day_of_quarter integer,

    day_of_year integer,

    week_of_month integer,

    week_of_year integer,

    week_of_year_iso text,

    month_actual integer,

    month_name text,

    month_name_abbreviated text,

    quarter_actual integer,

    quarter_name text,

    year_actual integer,

    year_iso integer,

    first_day_of_week date,

    last_day_of_week date,

    first_day_of_month date,

    last_day_of_month date,

    first_day_of_quarter date,

    last_day_of_quarter date,

    first_day_of_year date,

    last_day_of_year date,

    mmyyyy text,

    mmddyyyy text,

    weekend_indr boolean

);

INSERT INTO dimensions.dates (date) VALUES (NULL);


CREATE OR REPLACE FUNCTION insert_date_dimension_row() RETURNS trigger AS

$BODY$

BEGIN

  NEW.date := NEW.date;

  NEW.epoch := EXTRACT(epoch FROM NEW.date)::bigint;

  NEW.day_suffix := TO_CHAR(NEW.date, 'fmDDth');

  NEW.day_name := TO_CHAR(NEW.date, 'Day');

  NEW.day_of_week := EXTRACT(isodow FROM NEW.date)::int;

  NEW.day_of_month := EXTRACT(DAY FROM NEW.date)::int;

  NEW.day_of_quarter := NEW.date - DATE_TRUNC('quarter', NEW.date)::date + 1;

  NEW.day_of_year := EXTRACT(doy FROM NEW.date)::int;

  NEW.week_of_month := TO_CHAR(NEW.date, 'W')::INT;

  NEW.week_of_year := EXTRACT(week FROM NEW.date)::int;

  NEW.week_of_year_iso := TO_CHAR(NEW.date, 'YYYY"-W"IW-') || EXTRACT(isodow FROM NEW.date);

  NEW.month_actual := EXTRACT(MONTH FROM NEW.date)::int;

  NEW.month_name := TO_CHAR(NEW.date, 'Month');

  NEW.month_name_abbreviated := TO_CHAR(NEW.date, 'Mon');

  NEW.quarter_actual := EXTRACT(quarter FROM NEW.date)::int;

  NEW.quarter_name := CASE

    WHEN EXTRACT(quarter

      FROM NEW.date) = 1 THEN 'First'

    WHEN EXTRACT(quarter

      FROM NEW.date) = 2 THEN 'Second'

    WHEN EXTRACT(quarter

      FROM NEW.date) = 3 THEN 'Third'

    WHEN EXTRACT(quarter

      FROM NEW.date) = 4 THEN 'Fourth'

  END;

  NEW.year_actual := EXTRACT(year FROM NEW.date)::int;

  NEW.year_iso := EXTRACT(isoyear FROM NEW.date)::int;

  NEW.first_day_of_week := NEW.date +(1 -EXTRACT(isodow FROM NEW.date))::INT;

  NEW.last_day_of_week := NEW.date +(7 -EXTRACT(isodow FROM NEW.date))::INT;

  NEW.first_day_of_month := NEW.date +(1 -EXTRACT(DAY FROM NEW.date))::INT;

  NEW.last_day_of_month := (DATE_TRUNC('MONTH', NEW.date) + INTERVAL '1 MONTH - 1 day')::DATE;

  NEW.first_day_of_quarter := DATE_TRUNC('quarter', NEW.date)::DATE;

  NEW.last_day_of_quarter := (DATE_TRUNC('quarter', NEW.date) +INTERVAL '3 MONTH - 1 day')::DATE;

  NEW.first_day_of_year := TO_DATE(EXTRACT(year FROM NEW.date) || '-01-01', 'YYYY-MM-DD');

  NEW.last_day_of_year := TO_DATE(EXTRACT(year FROM NEW.date) || '-12-31', 'YYYY-MM-DD');

  NEW.mmyyyy := TO_CHAR(NEW.date, 'mmyyyy');

  NEW.mmddyyyy := TO_CHAR(NEW.date, 'mmddyyyy');

  NEW.weekend_indr := CASE

    WHEN EXTRACT(isodow FROM NEW.date) IN (6, 7) THEN TRUE

    ELSE FALSE

  END;



  RETURN NEW;

END;

$BODY$ LANGUAGE plpgsql;



CREATE TRIGGER "insert_date_dimension_row" BEFORE INSERT ON dimensions.dates

FOR EACH ROW EXECUTE PROCEDURE insert_date_dimension_row();



INSERT INTO dimensions.dates(date) SELECT datum::date AS date

FROM generate_series('2014-01-01', '2050-01-01', interval '1day') AS datum;

This is a good alteration of the original. It will insert an empty row as row 1, otherwise - works well. Thanks.

I'd use DROP TABLE if exists instead of a plain DROP.