GM01tequ

1CREATE SCHEMA IF NOT EXISTS dimensions;
2DROP TABLE dimensions.dates;
3CREATE TABLE dimensions.dates (
4    id SERIAL PRIMARY KEY,
5    date date,
6    epoch bigint,
7    day_suffix text,
8    day_name text,
9    day_of_week integer,
10    day_of_month integer,
11    day_of_quarter integer,
12    day_of_year integer,
13    week_of_month integer,
14    week_of_year integer,
15    week_of_year_iso text,
16    month_actual integer,
17    month_name text,
18    month_name_abbreviated text,
19    quarter_actual integer,
20    quarter_name text,
21    year_actual integer,
22    year_iso integer,
23    first_day_of_week date,
24    last_day_of_week date,
25    first_day_of_month date,
26    last_day_of_month date,
27    first_day_of_quarter date,
28    last_day_of_quarter date,
29    first_day_of_year date,
30    last_day_of_year date,
31    mmyyyy text,
32    mmddyyyy text,
33    weekend_indr boolean
34);
35
36CREATE OR REPLACE FUNCTION insert_date_dimension_row() RETURNS trigger AS
37$BODY$
38BEGIN
39  NEW.date := NEW.date;
40  NEW.epoch := EXTRACT(epoch FROM NEW.date)::bigint;
41  NEW.day_suffix := TO_CHAR(NEW.date, 'fmDDth');
42  NEW.day_name := TO_CHAR(NEW.date, 'Day');
43  NEW.day_of_week := EXTRACT(isodow FROM NEW.date)::int;
44  NEW.day_of_month := EXTRACT(DAY FROM NEW.date)::int;
45  NEW.day_of_quarter := NEW.date - DATE_TRUNC('quarter', NEW.date)::date + 1;
46  NEW.day_of_year := EXTRACT(doy FROM NEW.date)::int;
47  NEW.week_of_month := TO_CHAR(NEW.date, 'W')::INT;
48  NEW.week_of_year := EXTRACT(week FROM NEW.date)::int;
49  NEW.week_of_year_iso := TO_CHAR(NEW.date, 'YYYY"-W"IW-') || EXTRACT(isodow FROM NEW.date);
50  NEW.month_actual := EXTRACT(MONTH FROM NEW.date)::int;
51  NEW.month_name := TO_CHAR(NEW.date, 'Month');
52  NEW.month_name_abbreviated := TO_CHAR(NEW.date, 'Mon');
53  NEW.quarter_actual := EXTRACT(quarter FROM NEW.date)::int;
54  NEW.quarter_name := CASE
55    WHEN EXTRACT(quarter
56      FROM NEW.date) = 1 THEN 'First'
57    WHEN EXTRACT(quarter
58      FROM NEW.date) = 2 THEN 'Second'
59    WHEN EXTRACT(quarter
60      FROM NEW.date) = 3 THEN 'Third'
61    WHEN EXTRACT(quarter
62      FROM NEW.date) = 4 THEN 'Fourth'
63  END;
64  NEW.year_actual := EXTRACT(year FROM NEW.date)::int;
65  NEW.year_iso := EXTRACT(isoyear FROM NEW.date)::int;
66  NEW.first_day_of_week := NEW.date +(1 -EXTRACT(isodow FROM NEW.date))::INT;
67  NEW.last_day_of_week := NEW.date +(7 -EXTRACT(isodow FROM NEW.date))::INT;
68  NEW.first_day_of_month := NEW.date +(1 -EXTRACT(DAY FROM NEW.date))::INT;
69  NEW.last_day_of_month := (DATE_TRUNC('MONTH', NEW.date) + INTERVAL '1 MONTH - 1 day')::DATE;
70  NEW.first_day_of_quarter := DATE_TRUNC('quarter', NEW.date)::DATE;
71  NEW.last_day_of_quarter := (DATE_TRUNC('quarter', NEW.date) +INTERVAL '3 MONTH - 1 day')::DATE;
72  NEW.first_day_of_year := TO_DATE(EXTRACT(year FROM NEW.date) || '-01-01', 'YYYY-MM-DD');
73  NEW.last_day_of_year := TO_DATE(EXTRACT(year FROM NEW.date) || '-12-31', 'YYYY-MM-DD');
74  NEW.mmyyyy := TO_CHAR(NEW.date, 'mmyyyy');
75  NEW.mmddyyyy := TO_CHAR(NEW.date, 'mmddyyyy');
76  NEW.weekend_indr := CASE
77    WHEN EXTRACT(isodow FROM NEW.date) IN (6, 7) THEN TRUE
78    ELSE FALSE
79  END;
80
81  RETURN NEW;
82END;
83$BODY$ LANGUAGE plpgsql;
84
85CREATE TRIGGER "insert_date_dimension_row" BEFORE INSERT ON dimensions.dates
86FOR EACH ROW EXECUTE PROCEDURE insert_date_dimension_row();
87
88INSERT INTO dimensions.dates(date) SELECT datum::date AS date
89FROM generate_series('2014-01-01', '2050-01-01', interval '1day') AS datum;