1 -- Bank holiday prediction functions 22/03/2013 Glyn Astill <glyn@8kb.co.uk>
\r
2 -- Mostly plagiarised from plpgsql functions posted to PostgreSQL mailing list
\r
3 -- on 15/05/2007 by Gary Stainburn (gary.stainburn@ringways.co.uk) and converted
\r
4 -- to plain sql. See:
\r
5 -- http://www.postgresql.org/message-id/flat/200705151509.41320.gary.stainburn@ringways.co.uk#200705151509.41320.gary.stainburn@ringways.co.uk
\r
6 -- http://www.postgresql.org/message-id/attachment/21954/bank_holidays.sql
\r
8 DROP FUNCTION IF EXISTS public.calculate_easter_sunday(integer);
\r
9 CREATE OR REPLACE FUNCTION public.calculate_easter_sunday(integer)
\r
14 WHEN (((((($1%19)*19)+24)%30)+(((($1%4)*2)+(($1%7)*4)+((((($1%19)*19)+24)%30)*6)+5)%7)) < 10) THEN
\r
15 ($1 || '-03-' || (((($1%19)*19)+24)%30)+(((($1%4)*2)+(($1%7)*4)+((((($1%19)*19)+24)%30)*6)+5)%7)+22)::date
\r
17 ($1 || '-04-' || (((($1%19)*19)+24)%30)+(((($1%4)*2)+(($1%7)*4)+((((($1%19)*19)+24)%30)*6)+5)%7)-9)::date
\r
20 LANGUAGE SQL IMMUTABLE;
\r
22 GRANT EXECUTE ON FUNCTION public.calculate_easter_sunday(integer) TO public;
\r
26 DROP FUNCTION IF EXISTS public.calculate_new_year(integer);
\r
27 CREATE OR REPLACE FUNCTION public.calculate_new_year(integer)
\r
31 CASE extract(dow FROM ($1 || '-01-01')::date)
\r
32 WHEN 0 THEN ($1 || '-01-02')::date
\r
33 WHEN 6 THEN ($1 || '-01-03')::date
\r
34 ELSE ($1 || '-01-01')::date
\r
37 LANGUAGE SQL IMMUTABLE;
\r
39 GRANT EXECUTE ON FUNCTION public.calculate_new_year(integer) TO public;
\r
43 DROP FUNCTION IF EXISTS public.calculate_christmas_day(integer);
\r
44 CREATE OR REPLACE FUNCTION public.calculate_christmas_day(integer)
\r
48 CASE extract(dow FROM ($1 || '-12-25')::date)
\r
49 WHEN 0 THEN ($1 || '-12-27')::date
\r
50 WHEN 6 THEN ($1 || '-12-28')::date
\r
51 ELSE ($1 || '-12-25')::date
\r
54 LANGUAGE SQL IMMUTABLE;
\r
56 GRANT EXECUTE ON FUNCTION public.calculate_christmas_day(integer) TO public;
\r
59 DROP FUNCTION IF EXISTS public.calculate_boxing_day(integer);
\r
60 CREATE OR REPLACE FUNCTION public.calculate_boxing_day(integer)
\r
64 CASE extract(dow FROM ($1 || '-12-26')::date)
\r
65 WHEN 0 THEN ($1 || '-12-27')::date
\r
66 WHEN 6 THEN ($1 || '-12-28')::date
\r
67 ELSE ($1 || '-12-26')::date
\r
70 LANGUAGE SQL IMMUTABLE;
\r
72 GRANT EXECUTE ON FUNCTION public.calculate_boxing_day(integer) TO public;
\r
76 DROP FUNCTION IF EXISTS public.calculate_may_day(integer);
\r
77 CREATE OR REPLACE FUNCTION public.calculate_may_day(integer)
\r
82 WHEN extract(dow FROM ($1 || '-05-01')::date) < 2 THEN ($1 || '-05-' || 2-extract(dow FROM ($1 || '-05-01')::date))::date
\r
83 ELSE ($1 || '-05-' || 9-extract(dow FROM ($1 || '-05-01')::date))::date
\r
86 LANGUAGE SQL IMMUTABLE;
\r
88 GRANT EXECUTE ON FUNCTION public.calculate_may_day(integer) TO public;
\r
92 DROP FUNCTION IF EXISTS public.calculate_spring_bank_holiday(integer);
\r
93 CREATE OR REPLACE FUNCTION public.calculate_spring_bank_holiday(integer)
\r
97 CASE extract(dow FROM ($1 || '-05-31')::date)
\r
98 WHEN 0 THEN ($1 || '-05-25')::date
\r
99 ELSE ($1 || '-05-' || 32-extract(dow FROM ($1 || '-05-31')::date))::date
\r
102 LANGUAGE SQL IMMUTABLE;
\r
104 GRANT EXECUTE ON FUNCTION public.calculate_spring_bank_holiday(integer) TO public;
\r
108 DROP FUNCTION IF EXISTS public.calculate_whitsun(integer);
\r
109 CREATE OR REPLACE FUNCTION public.calculate_whitsun(integer)
\r
112 SELECT public.calculate_spring_bank_holiday($1) ;
\r
114 LANGUAGE SQL IMMUTABLE;
\r
116 GRANT EXECUTE ON FUNCTION public.calculate_whitsun(integer) TO public;
\r
120 DROP FUNCTION IF EXISTS public.calculate_summer_bank_holiday(integer);
\r
121 CREATE OR REPLACE FUNCTION public.calculate_summer_bank_holiday(integer)
\r
125 CASE extract(dow FROM ($1 || '-08-31')::date)
\r
126 WHEN 0 THEN ($1 || '-08-25')::date
\r
127 ELSE ($1 || '-08-' || 32-extract(dow FROM ($1 || '-08-31')::date))::date
\r
130 LANGUAGE SQL IMMUTABLE;
\r
132 GRANT EXECUTE ON FUNCTION public.calculate_summer_bank_holiday(integer) TO public;
\r
136 DROP TYPE IF EXISTS public.bank_holidays CASCADE;
\r
137 CREATE TYPE public.bank_holidays AS
\r
138 (year integer, description varchar, bank_holiday date);
\r
140 DROP FUNCTION IF EXISTS public.calculate_bank_holidays(integer);
\r
141 CREATE OR REPLACE FUNCTION public.calculate_bank_holidays(integer)
\r
142 RETURNS SETOF public.bank_holidays AS
\r
144 SELECT $1, 'New Years Day', public.calculate_new_year($1)
\r
146 SELECT $1, 'Good Friday', (public.calculate_easter_sunday($1)-'2 days'::interval)::date
\r
148 SELECT $1, 'Easter Monday', (public.calculate_easter_sunday($1)+'1 days'::interval)::date
\r
150 SELECT $1, 'May Day', public.calculate_may_day($1)
\r
152 SELECT $1, 'Spring Bank Holiday', public.calculate_spring_bank_holiday($1)
\r
154 SELECT $1, 'Summer Bank Holiday', public.calculate_summer_bank_holiday($1)
\r
156 SELECT $1, 'Christmas Day', public.calculate_christmas_day($1)
\r
158 SELECT $1, 'Boxing Day', public.calculate_boxing_day($1)
\r
161 LANGUAGE SQL IMMUTABLE;
\r
163 GRANT EXECUTE ON FUNCTION public.calculate_bank_holidays(integer) TO public;
\r
167 DROP FUNCTION IF EXISTS public.calculate_bank_holidays(integer, integer);
\r
168 CREATE OR REPLACE FUNCTION public.calculate_bank_holidays(integer, integer)
\r
169 RETURNS SETOF bank_holidays AS
\r
171 SELECT (public.calculate_bank_holidays(generate_series)).* FROM generate_series($1, $2);
\r
173 LANGUAGE SQL IMMUTABLE;
\r
175 GRANT EXECUTE ON FUNCTION public.calculate_bank_holidays(integer, integer) TO public;
\r
179 DROP FUNCTION IF EXISTS public.detail_bank_holiday(date);
\r
180 CREATE OR REPLACE FUNCTION public.detail_bank_holiday(date)
\r
181 RETURNS SETOF bank_holidays AS
\r
183 SELECT * FROM public.calculate_bank_holidays(extract(year FROM $1)::integer)
\r
184 WHERE bank_holiday = $1;
\r
186 LANGUAGE SQL IMMUTABLE;
\r
188 GRANT EXECUTE ON FUNCTION public.detail_bank_holiday(date) TO public;
\r
192 DROP FUNCTION IF EXISTS public.is_bank_holiday(date);
\r
193 CREATE OR REPLACE FUNCTION public.is_bank_holiday(date)
\r
194 RETURNS boolean AS
\r
196 SELECT EXISTS(SELECT 1 FROM public.calculate_bank_holidays(extract(year FROM $1)::integer)
\r
197 WHERE bank_holiday = $1);
\r
199 LANGUAGE SQL IMMUTABLE;
\r
201 GRANT EXECUTE ON FUNCTION public.is_bank_holiday(date) TO public;
\r
205 DROP FUNCTION IF EXISTS public.count_bank_holidays(date, date);
\r
206 CREATE OR REPLACE FUNCTION public.count_bank_holidays(date, date)
\r
209 SELECT count(*) FROM public.calculate_bank_holidays(extract(year FROM $1)::integer, extract(year FROM $2)::integer)
\r
210 WHERE bank_holiday BETWEEN $1 AND $2;
\r
212 LANGUAGE SQL IMMUTABLE;
\r
214 GRANT EXECUTE ON FUNCTION public.count_bank_holidays(date, date) TO public;
\r