git.8kb.co.uk Git - postgresql/bank_holidays/blob - bank_holidays.sql

   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
   7 \r
   8 DROP FUNCTION IF EXISTS public.calculate_easter_sunday(integer);\r
   9 CREATE OR REPLACE FUNCTION public.calculate_easter_sunday(integer) \r
  10 RETURNS date AS \r
  11 $BODY$    \r
  12     SELECT \r
  13     CASE \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
  16         ELSE\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
  18     END;\r
  19 $BODY$ \r
  20 LANGUAGE SQL IMMUTABLE;\r
  21 \r
  22 GRANT EXECUTE ON FUNCTION public.calculate_easter_sunday(integer) TO public;\r
  23 \r
  24 --\r
  25 \r
  26 DROP FUNCTION IF EXISTS public.calculate_new_year(integer);\r
  27 CREATE OR REPLACE FUNCTION public.calculate_new_year(integer) \r
  28 RETURNS date AS \r
  29 $BODY$    \r
  30     SELECT \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
  35     END;\r
  36 $BODY$ \r
  37 LANGUAGE SQL IMMUTABLE;\r
  38 \r
  39 GRANT EXECUTE ON FUNCTION public.calculate_new_year(integer) TO public;\r
  40 \r
  41 --\r
  42 \r
  43 DROP FUNCTION IF EXISTS public.calculate_christmas_day(integer);\r
  44 CREATE OR REPLACE FUNCTION public.calculate_christmas_day(integer) \r
  45 RETURNS date AS \r
  46 $BODY$    \r
  47     SELECT \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
  52     END;\r
  53 $BODY$ \r
  54 LANGUAGE SQL IMMUTABLE;\r
  55 \r
  56 GRANT EXECUTE ON FUNCTION public.calculate_christmas_day(integer) TO public;\r
  57 --\r
  58 \r
  59 DROP FUNCTION IF EXISTS public.calculate_boxing_day(integer);\r
  60 CREATE OR REPLACE FUNCTION public.calculate_boxing_day(integer) \r
  61 RETURNS date AS \r
  62 $BODY$    \r
  63     SELECT \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
  68     END;\r
  69 $BODY$ \r
  70 LANGUAGE SQL IMMUTABLE;\r
  71 \r
  72 GRANT EXECUTE ON FUNCTION public.calculate_boxing_day(integer) TO public;\r
  73 \r
  74 --\r
  75 \r
  76 DROP FUNCTION IF EXISTS public.calculate_may_day(integer);\r
  77 CREATE OR REPLACE FUNCTION public.calculate_may_day(integer) \r
  78 RETURNS date AS \r
  79 $BODY$    \r
  80     SELECT \r
  81     CASE \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
  84     END;\r
  85 $BODY$ \r
  86 LANGUAGE SQL IMMUTABLE;\r
  87 \r
  88 GRANT EXECUTE ON FUNCTION public.calculate_may_day(integer)  TO public;\r
  89 \r
  90 --\r
  91 \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
  94 RETURNS date AS \r
  95 $BODY$    \r
  96     SELECT \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
 100     END;\r
 101 $BODY$ \r
 102 LANGUAGE SQL IMMUTABLE;\r
 103 \r
 104 GRANT EXECUTE ON FUNCTION public.calculate_spring_bank_holiday(integer) TO public;\r
 105 \r
 106 --\r
 107 \r
 108 DROP FUNCTION IF EXISTS public.calculate_whitsun(integer);\r
 109 CREATE OR REPLACE FUNCTION public.calculate_whitsun(integer) \r
 110 RETURNS date AS \r
 111 $BODY$    \r
 112     SELECT public.calculate_spring_bank_holiday($1) ;\r
 113 $BODY$ \r
 114 LANGUAGE SQL IMMUTABLE;\r
 115 \r
 116 GRANT EXECUTE ON FUNCTION public.calculate_whitsun(integer)  TO public;\r
 117 \r
 118 --\r
 119 \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
 122 RETURNS date AS \r
 123 $BODY$    \r
 124     SELECT \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
 128     END;\r
 129 $BODY$ \r
 130 LANGUAGE SQL IMMUTABLE;\r
 131 \r
 132 GRANT EXECUTE ON FUNCTION public.calculate_summer_bank_holiday(integer) TO public;\r
 133 \r
 134 --\r
 135 \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
 139 \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
 143 $BODY$\r
 144     SELECT $1, 'New Years Day', public.calculate_new_year($1)\r
 145     UNION\r
 146     SELECT $1, 'Good Friday', (public.calculate_easter_sunday($1)-'2 days'::interval)::date\r
 147     UNION\r
 148     SELECT $1, 'Easter Monday', (public.calculate_easter_sunday($1)+'1 days'::interval)::date\r
 149     UNION \r
 150     SELECT $1, 'May Day', public.calculate_may_day($1)\r
 151     UNION \r
 152     SELECT $1, 'Spring Bank Holiday', public.calculate_spring_bank_holiday($1)\r
 153     UNION \r
 154     SELECT $1, 'Summer Bank Holiday', public.calculate_summer_bank_holiday($1)\r
 155     UNION \r
 156     SELECT $1, 'Christmas Day', public.calculate_christmas_day($1)\r
 157     UNION \r
 158     SELECT $1, 'Boxing Day', public.calculate_boxing_day($1)\r
 159     ORDER BY 3;\r
 160 $BODY$\r
 161 LANGUAGE SQL IMMUTABLE;\r
 162 \r
 163 GRANT EXECUTE ON FUNCTION public.calculate_bank_holidays(integer) TO public;\r
 164 \r
 165 --\r
 166 \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
 170 $BODY$\r
 171     SELECT (public.calculate_bank_holidays(generate_series)).* FROM generate_series($1, $2);\r
 172 $BODY$\r
 173 LANGUAGE SQL IMMUTABLE;\r
 174 \r
 175 GRANT EXECUTE ON FUNCTION public.calculate_bank_holidays(integer, integer) TO public;\r
 176 \r
 177 --\r
 178 \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
 182 $BODY$\r
 183     SELECT * FROM public.calculate_bank_holidays(extract(year FROM $1)::integer)\r
 184     WHERE bank_holiday = $1;\r
 185 $BODY$\r
 186 LANGUAGE SQL IMMUTABLE;\r
 187 \r
 188 GRANT EXECUTE ON FUNCTION public.detail_bank_holiday(date) TO public;\r
 189 \r
 190 --\r
 191 \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
 195 $BODY$\r
 196     SELECT EXISTS(SELECT 1 FROM public.calculate_bank_holidays(extract(year FROM $1)::integer)\r
 197     WHERE bank_holiday = $1);\r
 198 $BODY$\r
 199 LANGUAGE SQL IMMUTABLE;\r
 200 \r
 201 GRANT EXECUTE ON FUNCTION public.is_bank_holiday(date) TO public;\r
 202 \r
 203 --\r
 204 \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
 207 RETURNS bigint AS \r
 208 $BODY$\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
 211 $BODY$\r
 212 LANGUAGE SQL IMMUTABLE;\r
 213 \r
 214 GRANT EXECUTE ON FUNCTION public.count_bank_holidays(date, date) TO public;\r
 215 \r
 216 --