1 | /** |
---|
2 | * PostgreSQL Functions for RRULE handling |
---|
3 | * |
---|
4 | * @package rscds |
---|
5 | * @subpackage database |
---|
6 | * @author Andrew McMillan <andrew@morphoss.com> |
---|
7 | * @copyright Morphoss Ltd - http://www.morphoss.com/ |
---|
8 | * @license http://gnu.org/copyleft/gpl.html GNU GPL v2 or later |
---|
9 | * |
---|
10 | * Coverage of this function set |
---|
11 | * - COUNT & UNTIL are handled, generally |
---|
12 | * - DAILY frequency, including BYDAY, BYMONTH, BYMONTHDAY, BYWEEKNO, BYMONTHDAY |
---|
13 | * - WEEKLY frequency, including BYDAY, BYMONTH, BYMONTHDAY, BYWEEKNO, BYSETPOS |
---|
14 | * - MONTHLY frequency, including BYDAY, BYMONTH, BYSETPOS |
---|
15 | * - YEARLY frequency, including BYMONTH, BYMONTHDAY, BYSETPOS, BYDAY |
---|
16 | * |
---|
17 | * Not covered as yet |
---|
18 | * - DAILY: BYYEARDAY, BYSETPOS* |
---|
19 | * - WEEKLY: BYYEARDAY |
---|
20 | * - MONTHLY: BYYEARDAY, BYMONTHDAY, BYWEEKNO |
---|
21 | * - YEARLY: BYYEARDAY |
---|
22 | * - SECONDLY |
---|
23 | * - MINUTELY |
---|
24 | * - HOURLY |
---|
25 | * |
---|
26 | */ |
---|
27 | |
---|
28 | -- Create a composite type for the parts of the RRULE. |
---|
29 | DROP TYPE rrule_parts CASCADE; |
---|
30 | CREATE TYPE rrule_parts AS ( |
---|
31 | base TIMESTAMP WITH TIME ZONE, |
---|
32 | until TIMESTAMP WITH TIME ZONE, |
---|
33 | freq TEXT, |
---|
34 | count INT, |
---|
35 | interval INT, |
---|
36 | bysecond INT[], |
---|
37 | byminute INT[], |
---|
38 | byhour INT[], |
---|
39 | bymonthday INT[], |
---|
40 | byyearday INT[], |
---|
41 | byweekno INT[], |
---|
42 | byday TEXT[], |
---|
43 | bymonth INT[], |
---|
44 | bysetpos INT[], |
---|
45 | wkst TEXT |
---|
46 | ); |
---|
47 | |
---|
48 | |
---|
49 | -- Create a function to parse the RRULE into it's composite type |
---|
50 | CREATE or REPLACE FUNCTION parse_rrule_parts( TIMESTAMP WITH TIME ZONE, TEXT ) RETURNS rrule_parts AS $$ |
---|
51 | DECLARE |
---|
52 | basedate ALIAS FOR $1; |
---|
53 | repeatrule ALIAS FOR $2; |
---|
54 | result rrule_parts%ROWTYPE; |
---|
55 | tempstr TEXT; |
---|
56 | BEGIN |
---|
57 | result.base := basedate; |
---|
58 | result.until := substring(repeatrule from 'UNTIL=([0-9TZ]+)(;|$)'); |
---|
59 | result.freq := substring(repeatrule from 'FREQ=([A-Z]+)(;|$)'); |
---|
60 | result.count := substring(repeatrule from 'COUNT=([0-9]+)(;|$)'); |
---|
61 | result.interval := COALESCE(substring(repeatrule from 'INTERVAL=([0-9]+)(;|$)')::int, 1); |
---|
62 | result.wkst := substring(repeatrule from 'WKST=(MO|TU|WE|TH|FR|SA|SU)(;|$)'); |
---|
63 | |
---|
64 | result.byday := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ','); |
---|
65 | |
---|
66 | result.byyearday := string_to_array(substring(repeatrule from 'BYYEARDAY=([0-9,+-]+)(;|$)'), ','); |
---|
67 | result.byweekno := string_to_array(substring(repeatrule from 'BYWEEKNO=([0-9,+-]+)(;|$)'), ','); |
---|
68 | result.bymonthday := string_to_array(substring(repeatrule from 'BYMONTHDAY=([0-9,+-]+)(;|$)'), ','); |
---|
69 | result.bymonth := string_to_array(substring(repeatrule from 'BYMONTH=(([+-]?[0-1]?[0-9],?)+)(;|$)'), ','); |
---|
70 | result.bysetpos := string_to_array(substring(repeatrule from 'BYSETPOS=(([+-]?[0-9]{1,3},?)+)(;|$)'), ','); |
---|
71 | |
---|
72 | result.bysecond := string_to_array(substring(repeatrule from 'BYSECOND=([0-9,]+)(;|$)'), ','); |
---|
73 | result.byminute := string_to_array(substring(repeatrule from 'BYMINUTE=([0-9,]+)(;|$)'), ','); |
---|
74 | result.byhour := string_to_array(substring(repeatrule from 'BYHOUR=([0-9,]+)(;|$)'), ','); |
---|
75 | |
---|
76 | RETURN result; |
---|
77 | END; |
---|
78 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
79 | |
---|
80 | |
---|
81 | -- Return a SETOF dates within the month of a particular date which match a string of BYDAY rule specifications |
---|
82 | CREATE or REPLACE FUNCTION rrule_month_byday_set( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
83 | DECLARE |
---|
84 | in_time ALIAS FOR $1; |
---|
85 | byday ALIAS FOR $2; |
---|
86 | dayrule TEXT; |
---|
87 | i INT; |
---|
88 | dow INT; |
---|
89 | index INT; |
---|
90 | first_dow INT; |
---|
91 | each_day TIMESTAMP WITH TIME ZONE; |
---|
92 | this_month INT; |
---|
93 | results TIMESTAMP WITH TIME ZONE[]; |
---|
94 | BEGIN |
---|
95 | |
---|
96 | IF byday IS NULL THEN |
---|
97 | -- We still return the single date as a SET |
---|
98 | RETURN NEXT in_time; |
---|
99 | RETURN; |
---|
100 | END IF; |
---|
101 | |
---|
102 | i := 1; |
---|
103 | dayrule := byday[i]; |
---|
104 | WHILE dayrule IS NOT NULL LOOP |
---|
105 | dow := position(substring( dayrule from '..$') in 'SUMOTUWETHFRSA') / 2; |
---|
106 | each_day := date_trunc( 'month', in_time ) + (in_time::time)::interval; |
---|
107 | this_month := date_part( 'month', in_time ); |
---|
108 | first_dow := date_part( 'dow', each_day ); |
---|
109 | |
---|
110 | -- Coerce each_day to be the first 'dow' of the month |
---|
111 | each_day := each_day - ( first_dow::text || 'days')::interval |
---|
112 | + ( dow::text || 'days')::interval |
---|
113 | + CASE WHEN dow < first_dow THEN '1 week'::interval ELSE '0s'::interval END; |
---|
114 | |
---|
115 | -- RAISE NOTICE 'From "%", for % finding dates. dow=%, this_month=%, first_dow=%', each_day, dayrule, dow, this_month, first_dow; |
---|
116 | IF length(dayrule) > 2 THEN |
---|
117 | index := (substring(dayrule from '^[0-9-]+'))::int; |
---|
118 | |
---|
119 | IF index = 0 THEN |
---|
120 | RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule; |
---|
121 | ELSIF index > 0 THEN |
---|
122 | -- The simplest case, such as 2MO for the second monday |
---|
123 | each_day := each_day + ((index - 1)::text || ' weeks')::interval; |
---|
124 | ELSE |
---|
125 | each_day := each_day + '5 weeks'::interval; |
---|
126 | WHILE date_part('month', each_day) != this_month LOOP |
---|
127 | each_day := each_day - '1 week'::interval; |
---|
128 | END LOOP; |
---|
129 | -- Note that since index is negative, (-2 + 1) == -1, for example |
---|
130 | index := index + 1; |
---|
131 | IF index < 0 THEN |
---|
132 | each_day := each_day + (index::text || ' weeks')::interval ; |
---|
133 | END IF; |
---|
134 | END IF; |
---|
135 | |
---|
136 | -- Sometimes (e.g. 5TU or -5WE) there might be no such date in some months |
---|
137 | IF date_part('month', each_day) = this_month THEN |
---|
138 | results[date_part('day',each_day)] := each_day; |
---|
139 | -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule; |
---|
140 | END IF; |
---|
141 | |
---|
142 | ELSE |
---|
143 | -- Return all such days that are within the given month |
---|
144 | WHILE date_part('month', each_day) = this_month LOOP |
---|
145 | results[date_part('day',each_day)] := each_day; |
---|
146 | each_day := each_day + '1 week'::interval; |
---|
147 | -- RAISE NOTICE 'Added "%" to list for %', each_day, dayrule; |
---|
148 | END LOOP; |
---|
149 | END IF; |
---|
150 | |
---|
151 | i := i + 1; |
---|
152 | dayrule := byday[i]; |
---|
153 | END LOOP; |
---|
154 | |
---|
155 | FOR i IN 1..31 LOOP |
---|
156 | IF results[i] IS NOT NULL THEN |
---|
157 | RETURN NEXT results[i]; |
---|
158 | END IF; |
---|
159 | END LOOP; |
---|
160 | |
---|
161 | RETURN; |
---|
162 | |
---|
163 | END; |
---|
164 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
165 | |
---|
166 | |
---|
167 | -- Return a SETOF dates within the month of a particular date which match a string of BYDAY rule specifications |
---|
168 | CREATE or REPLACE FUNCTION rrule_month_bymonthday_set( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
169 | DECLARE |
---|
170 | in_time ALIAS FOR $1; |
---|
171 | bymonthday ALIAS FOR $2; |
---|
172 | month_start TIMESTAMP WITH TIME ZONE; |
---|
173 | daysinmonth INT; |
---|
174 | i INT; |
---|
175 | BEGIN |
---|
176 | |
---|
177 | month_start := date_trunc( 'month', in_time ) + (in_time::time)::interval; |
---|
178 | daysinmonth := date_part( 'days', (month_start + interval '1 month') - interval '1 day' ); |
---|
179 | |
---|
180 | FOR i IN 1..31 LOOP |
---|
181 | EXIT WHEN bymonthday[i] IS NULL; |
---|
182 | |
---|
183 | CONTINUE WHEN bymonthday[i] > daysinmonth; |
---|
184 | CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth); |
---|
185 | |
---|
186 | IF bymonthday[i] > 0 THEN |
---|
187 | RETURN NEXT month_start + ((bymonthday[i] - 1)::text || 'days')::interval; |
---|
188 | ELSIF bymonthday[i] < 0 THEN |
---|
189 | RETURN NEXT month_start + ((daysinmonth + bymonthday[i])::text || 'days')::interval; |
---|
190 | ELSE |
---|
191 | RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i]; |
---|
192 | END IF; |
---|
193 | END LOOP; |
---|
194 | |
---|
195 | RETURN; |
---|
196 | |
---|
197 | END; |
---|
198 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
199 | |
---|
200 | |
---|
201 | -- Return a SETOF dates within the week of a particular date which match a single BYDAY rule specification |
---|
202 | CREATE or REPLACE FUNCTION rrule_week_byday_set( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
203 | DECLARE |
---|
204 | in_time ALIAS FOR $1; |
---|
205 | byday ALIAS FOR $2; |
---|
206 | dayrule TEXT; |
---|
207 | dow INT; |
---|
208 | our_day TIMESTAMP WITH TIME ZONE; |
---|
209 | i INT; |
---|
210 | BEGIN |
---|
211 | |
---|
212 | IF byday IS NULL THEN |
---|
213 | -- We still return the single date as a SET |
---|
214 | RETURN NEXT in_time; |
---|
215 | RETURN; |
---|
216 | END IF; |
---|
217 | |
---|
218 | our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval; |
---|
219 | |
---|
220 | i := 1; |
---|
221 | dayrule := byday[i]; |
---|
222 | WHILE dayrule IS NOT NULL LOOP |
---|
223 | dow := position(dayrule in 'SUMOTUWETHFRSA') / 2; |
---|
224 | RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval; |
---|
225 | i := i + 1; |
---|
226 | dayrule := byday[i]; |
---|
227 | END LOOP; |
---|
228 | |
---|
229 | RETURN; |
---|
230 | |
---|
231 | END; |
---|
232 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
233 | |
---|
234 | |
---|
235 | CREATE or REPLACE FUNCTION event_has_exceptions( TEXT ) RETURNS BOOLEAN AS $$ |
---|
236 | SELECT $1 ~ E'\nRECURRENCE-ID(;TZID=[^:]+)?:[[:space:]]*[[:digit:]]{8}(T[[:digit:]]{6})?' |
---|
237 | $$ LANGUAGE 'sql' IMMUTABLE STRICT; |
---|
238 | |
---|
239 | |
---|
240 | ------------------------------------------------------------------------------------------------------ |
---|
241 | -- Test the weekday of this date against the array of weekdays from the BYDAY rule (FREQ=WEEKLY or less) |
---|
242 | ------------------------------------------------------------------------------------------------------ |
---|
243 | CREATE or REPLACE FUNCTION test_byday_rule( TIMESTAMP WITH TIME ZONE, TEXT[] ) RETURNS BOOLEAN AS $$ |
---|
244 | DECLARE |
---|
245 | testme ALIAS FOR $1; |
---|
246 | byday ALIAS FOR $2; |
---|
247 | BEGIN |
---|
248 | -- Note that this doesn't work for MONTHLY/YEARLY BYDAY clauses which might have numbers prepended |
---|
249 | -- so don't call it that way... |
---|
250 | IF byday IS NOT NULL THEN |
---|
251 | RETURN ( substring( to_char( testme, 'DY') for 2 from 1) = ANY (byday) ); |
---|
252 | END IF; |
---|
253 | RETURN TRUE; |
---|
254 | END; |
---|
255 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
256 | |
---|
257 | |
---|
258 | ------------------------------------------------------------------------------------------------------ |
---|
259 | -- Test the month of this date against the array of months from the rule |
---|
260 | ------------------------------------------------------------------------------------------------------ |
---|
261 | CREATE or REPLACE FUNCTION test_bymonth_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$ |
---|
262 | DECLARE |
---|
263 | testme ALIAS FOR $1; |
---|
264 | bymonth ALIAS FOR $2; |
---|
265 | BEGIN |
---|
266 | IF bymonth IS NOT NULL THEN |
---|
267 | RETURN ( date_part( 'month', testme) = ANY (bymonth) ); |
---|
268 | END IF; |
---|
269 | RETURN TRUE; |
---|
270 | END; |
---|
271 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
272 | |
---|
273 | |
---|
274 | ------------------------------------------------------------------------------------------------------ |
---|
275 | -- Test the day in month of this date against the array of monthdays from the rule |
---|
276 | ------------------------------------------------------------------------------------------------------ |
---|
277 | CREATE or REPLACE FUNCTION test_bymonthday_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$ |
---|
278 | DECLARE |
---|
279 | testme ALIAS FOR $1; |
---|
280 | bymonthday ALIAS FOR $2; |
---|
281 | BEGIN |
---|
282 | IF bymonthday IS NOT NULL THEN |
---|
283 | RETURN ( date_part( 'day', testme) = ANY (bymonthday) ); |
---|
284 | END IF; |
---|
285 | RETURN TRUE; |
---|
286 | END; |
---|
287 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
288 | |
---|
289 | |
---|
290 | ------------------------------------------------------------------------------------------------------ |
---|
291 | -- Test the day in year of this date against the array of yeardays from the rule |
---|
292 | ------------------------------------------------------------------------------------------------------ |
---|
293 | CREATE or REPLACE FUNCTION test_byyearday_rule( TIMESTAMP WITH TIME ZONE, INT[] ) RETURNS BOOLEAN AS $$ |
---|
294 | DECLARE |
---|
295 | testme ALIAS FOR $1; |
---|
296 | byyearday ALIAS FOR $2; |
---|
297 | BEGIN |
---|
298 | IF byyearday IS NOT NULL THEN |
---|
299 | RETURN ( date_part( 'doy', testme) = ANY (byyearday) ); |
---|
300 | END IF; |
---|
301 | RETURN TRUE; |
---|
302 | END; |
---|
303 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
304 | |
---|
305 | |
---|
306 | ------------------------------------------------------------------------------------------------------ |
---|
307 | -- Given a cursor into a set, process the set returning the subset matching the BYSETPOS |
---|
308 | -- |
---|
309 | -- Note that this function *requires* PostgreSQL 8.3 or later for the cursor handling syntax |
---|
310 | -- to work. I guess we could do it with an array, instead, for compatibility with earlier |
---|
311 | -- releases, since there's a maximum of 366 positions in a set. |
---|
312 | ------------------------------------------------------------------------------------------------------ |
---|
313 | CREATE or REPLACE FUNCTION rrule_bysetpos_filter( REFCURSOR, INT[] ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
314 | DECLARE |
---|
315 | curse ALIAS FOR $1; |
---|
316 | bysetpos ALIAS FOR $2; |
---|
317 | valid_date TIMESTAMP WITH TIME ZONE; |
---|
318 | i INT; |
---|
319 | BEGIN |
---|
320 | |
---|
321 | IF bysetpos IS NULL THEN |
---|
322 | LOOP |
---|
323 | FETCH curse INTO valid_date; |
---|
324 | EXIT WHEN NOT FOUND; |
---|
325 | RETURN NEXT valid_date; |
---|
326 | END LOOP; |
---|
327 | ELSE |
---|
328 | FOR i IN 1..366 LOOP |
---|
329 | EXIT WHEN bysetpos[i] IS NULL; |
---|
330 | IF bysetpos[i] > 0 THEN |
---|
331 | FETCH ABSOLUTE bysetpos[i] FROM curse INTO valid_date; |
---|
332 | ELSE |
---|
333 | MOVE LAST IN curse; |
---|
334 | FETCH RELATIVE (bysetpos[i] + 1) FROM curse INTO valid_date; |
---|
335 | END IF; |
---|
336 | IF valid_date IS NOT NULL THEN |
---|
337 | RETURN NEXT valid_date; |
---|
338 | END IF; |
---|
339 | END LOOP; |
---|
340 | END IF; |
---|
341 | CLOSE curse; |
---|
342 | END; |
---|
343 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
344 | |
---|
345 | |
---|
346 | ------------------------------------------------------------------------------------------------------ |
---|
347 | -- Return another day's worth of events: i.e. one day that matches the criteria, since we don't |
---|
348 | -- currently implement sub-day scheduling. |
---|
349 | -- |
---|
350 | -- This is cheeky: The incrementing by a day is done outside the call, so we either return the |
---|
351 | -- empty set (if the input date fails our filters) or we return a set containing the input date. |
---|
352 | ------------------------------------------------------------------------------------------------------ |
---|
353 | CREATE or REPLACE FUNCTION daily_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
354 | DECLARE |
---|
355 | after ALIAS FOR $1; |
---|
356 | rrule ALIAS FOR $2; |
---|
357 | BEGIN |
---|
358 | |
---|
359 | IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN |
---|
360 | RETURN; |
---|
361 | END IF; |
---|
362 | |
---|
363 | IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN |
---|
364 | RETURN; |
---|
365 | END IF; |
---|
366 | |
---|
367 | IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN |
---|
368 | RETURN; |
---|
369 | END IF; |
---|
370 | |
---|
371 | IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN |
---|
372 | RETURN; |
---|
373 | END IF; |
---|
374 | |
---|
375 | IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN |
---|
376 | RETURN; |
---|
377 | END IF; |
---|
378 | |
---|
379 | -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial |
---|
380 | RETURN NEXT after; |
---|
381 | |
---|
382 | END; |
---|
383 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
384 | |
---|
385 | |
---|
386 | ------------------------------------------------------------------------------------------------------ |
---|
387 | -- Return another week's worth of events |
---|
388 | -- |
---|
389 | -- Doesn't handle truly obscure and unlikely stuff like BYWEEKNO=5;BYMONTH=1;BYDAY=WE,TH,FR;BYSETPOS=-2 |
---|
390 | -- Imagine that. |
---|
391 | ------------------------------------------------------------------------------------------------------ |
---|
392 | CREATE or REPLACE FUNCTION weekly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
393 | DECLARE |
---|
394 | after ALIAS FOR $1; |
---|
395 | rrule ALIAS FOR $2; |
---|
396 | valid_date TIMESTAMP WITH TIME ZONE; |
---|
397 | curse REFCURSOR; |
---|
398 | weekno INT; |
---|
399 | i INT; |
---|
400 | BEGIN |
---|
401 | |
---|
402 | IF rrule.byweekno IS NOT NULL THEN |
---|
403 | weekno := date_part('week',after); |
---|
404 | IF NOT weekno = ANY ( rrule.byweekno ) THEN |
---|
405 | RETURN; |
---|
406 | END IF; |
---|
407 | END IF; |
---|
408 | |
---|
409 | OPEN curse SCROLL FOR SELECT r FROM rrule_week_byday_set(after, rrule.byday ) r; |
---|
410 | RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d; |
---|
411 | |
---|
412 | END; |
---|
413 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
414 | |
---|
415 | |
---|
416 | ------------------------------------------------------------------------------------------------------ |
---|
417 | -- Return another month's worth of events |
---|
418 | ------------------------------------------------------------------------------------------------------ |
---|
419 | CREATE or REPLACE FUNCTION monthly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
420 | DECLARE |
---|
421 | after ALIAS FOR $1; |
---|
422 | rrule ALIAS FOR $2; |
---|
423 | valid_date TIMESTAMP WITH TIME ZONE; |
---|
424 | curse REFCURSOR; |
---|
425 | setpos INT; |
---|
426 | i INT; |
---|
427 | BEGIN |
---|
428 | |
---|
429 | /** |
---|
430 | * Need to investigate whether it is legal to set both of these, and whether |
---|
431 | * we are correct to UNION the results, or whether we should INTERSECT them. |
---|
432 | * So at this point, we refer to the specification, which grants us this |
---|
433 | * wonderfully enlightening vision: |
---|
434 | * |
---|
435 | * If multiple BYxxx rule parts are specified, then after evaluating the |
---|
436 | * specified FREQ and INTERVAL rule parts, the BYxxx rule parts are |
---|
437 | * applied to the current set of evaluated occurrences in the following |
---|
438 | * order: BYMONTH, BYWEEKNO, BYYEARDAY, BYMONTHDAY, BYDAY, BYHOUR, |
---|
439 | * BYMINUTE, BYSECOND and BYSETPOS; then COUNT and UNTIL are evaluated. |
---|
440 | * |
---|
441 | * My guess is that this means 'INTERSECT' |
---|
442 | */ |
---|
443 | IF rrule.byday IS NOT NULL AND rrule.bymonthday IS NOT NULL THEN |
---|
444 | OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r |
---|
445 | INTERSECT SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r |
---|
446 | ORDER BY 1; |
---|
447 | ELSIF rrule.bymonthday IS NOT NULL THEN |
---|
448 | OPEN curse SCROLL FOR SELECT r FROM rrule_month_bymonthday_set(after, rrule.bymonthday ) r ORDER BY 1; |
---|
449 | ELSE |
---|
450 | OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1; |
---|
451 | END IF; |
---|
452 | |
---|
453 | RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d; |
---|
454 | |
---|
455 | END; |
---|
456 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
457 | |
---|
458 | |
---|
459 | ------------------------------------------------------------------------------------------------------ |
---|
460 | -- If this is YEARLY;BYMONTH, abuse MONTHLY;BYMONTH for everything except the BYSETPOS |
---|
461 | ------------------------------------------------------------------------------------------------------ |
---|
462 | CREATE or REPLACE FUNCTION rrule_yearly_bymonth_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
463 | DECLARE |
---|
464 | after ALIAS FOR $1; |
---|
465 | rrule ALIAS FOR $2; |
---|
466 | current_base TIMESTAMP WITH TIME ZONE; |
---|
467 | rr rrule_parts; |
---|
468 | i INT; |
---|
469 | BEGIN |
---|
470 | |
---|
471 | IF rrule.bymonth IS NOT NULL THEN |
---|
472 | -- Ensure we don't pass BYSETPOS down |
---|
473 | rr := rrule; |
---|
474 | rr.bysetpos := NULL; |
---|
475 | FOR i IN 1..12 LOOP |
---|
476 | EXIT WHEN rr.bymonth[i] IS NULL; |
---|
477 | current_base := date_trunc( 'year', after ) + ((rr.bymonth[i] - 1)::text || ' months')::interval + (after::time)::interval; |
---|
478 | RETURN QUERY SELECT r FROM monthly_set(current_base,rr) r; |
---|
479 | END LOOP; |
---|
480 | ELSE |
---|
481 | -- We don't yet implement byweekno, byblah |
---|
482 | RETURN NEXT after; |
---|
483 | END IF; |
---|
484 | |
---|
485 | END; |
---|
486 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
487 | |
---|
488 | |
---|
489 | ------------------------------------------------------------------------------------------------------ |
---|
490 | -- Return another year's worth of events |
---|
491 | ------------------------------------------------------------------------------------------------------ |
---|
492 | CREATE or REPLACE FUNCTION yearly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
493 | DECLARE |
---|
494 | after ALIAS FOR $1; |
---|
495 | rrule ALIAS FOR $2; |
---|
496 | current_base TIMESTAMP WITH TIME ZONE; |
---|
497 | curse REFCURSOR; |
---|
498 | curser REFCURSOR; |
---|
499 | i INT; |
---|
500 | BEGIN |
---|
501 | |
---|
502 | IF rrule.bymonth IS NOT NULL THEN |
---|
503 | OPEN curse SCROLL FOR SELECT r FROM rrule_yearly_bymonth_set(after, rrule ) r; |
---|
504 | FOR current_base IN SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d LOOP |
---|
505 | current_base := date_trunc( 'day', current_base ) + (after::time)::interval; |
---|
506 | RETURN NEXT current_base; |
---|
507 | END LOOP; |
---|
508 | ELSE |
---|
509 | -- We don't yet implement byweekno, byblah |
---|
510 | RETURN NEXT after; |
---|
511 | END IF; |
---|
512 | END; |
---|
513 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
514 | |
---|
515 | |
---|
516 | ------------------------------------------------------------------------------------------------------ |
---|
517 | -- Combine all of that into something which we can use to generate a series from an arbitrary DTSTART/RRULE |
---|
518 | ------------------------------------------------------------------------------------------------------ |
---|
519 | CREATE or REPLACE FUNCTION rrule_event_instances_range( TIMESTAMP WITH TIME ZONE, TEXT, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, INT ) |
---|
520 | RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
521 | DECLARE |
---|
522 | basedate ALIAS FOR $1; |
---|
523 | repeatrule ALIAS FOR $2; |
---|
524 | mindate ALIAS FOR $3; |
---|
525 | maxdate ALIAS FOR $4; |
---|
526 | max_count ALIAS FOR $5; |
---|
527 | loopmax INT; |
---|
528 | loopcount INT; |
---|
529 | base_day TIMESTAMP WITH TIME ZONE; |
---|
530 | current_base TIMESTAMP WITH TIME ZONE; |
---|
531 | current TIMESTAMP WITH TIME ZONE; |
---|
532 | rrule rrule_parts%ROWTYPE; |
---|
533 | BEGIN |
---|
534 | loopcount := 0; |
---|
535 | |
---|
536 | SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule ); |
---|
537 | |
---|
538 | IF rrule.count IS NOT NULL THEN |
---|
539 | loopmax := rrule.count; |
---|
540 | ELSE |
---|
541 | -- max_count is pretty arbitrary, so we scale it somewhat here depending on the frequency. |
---|
542 | IF rrule.freq = 'DAILY' THEN |
---|
543 | loopmax := max_count * 20; |
---|
544 | ELSIF rrule.freq = 'WEEKLY' THEN |
---|
545 | loopmax := max_count * 10; |
---|
546 | ELSE |
---|
547 | loopmax := max_count; |
---|
548 | END IF; |
---|
549 | END IF; |
---|
550 | |
---|
551 | current_base := basedate; |
---|
552 | base_day := date_trunc('day',basedate); |
---|
553 | WHILE loopcount < loopmax AND current_base <= maxdate LOOP |
---|
554 | IF rrule.freq = 'DAILY' THEN |
---|
555 | FOR current IN SELECT d FROM daily_set(current_base,rrule) d WHERE d >= base_day LOOP |
---|
556 | -- IF test_byday_rule(current,rrule.byday) AND test_bymonthday_rule(current,rrule.bymonthday) AND test_bymonth_rule(current,rrule.bymonth) THEN |
---|
557 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
558 | IF current >= mindate THEN |
---|
559 | RETURN NEXT current; |
---|
560 | END IF; |
---|
561 | loopcount := loopcount + 1; |
---|
562 | EXIT WHEN loopcount >= loopmax; |
---|
563 | -- END IF; |
---|
564 | END LOOP; |
---|
565 | current_base := current_base + (rrule.interval::text || ' days')::interval; |
---|
566 | ELSIF rrule.freq = 'WEEKLY' THEN |
---|
567 | FOR current IN SELECT w FROM weekly_set(current_base,rrule) w WHERE w >= base_day LOOP |
---|
568 | IF test_byyearday_rule(current,rrule.byyearday) |
---|
569 | AND test_bymonthday_rule(current,rrule.bymonthday) |
---|
570 | AND test_bymonth_rule(current,rrule.bymonth) |
---|
571 | THEN |
---|
572 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
573 | IF current >= mindate THEN |
---|
574 | RETURN NEXT current; |
---|
575 | END IF; |
---|
576 | loopcount := loopcount + 1; |
---|
577 | EXIT WHEN loopcount >= loopmax; |
---|
578 | END IF; |
---|
579 | END LOOP; |
---|
580 | current_base := current_base + (rrule.interval::text || ' weeks')::interval; |
---|
581 | ELSIF rrule.freq = 'MONTHLY' THEN |
---|
582 | FOR current IN SELECT m FROM monthly_set(current_base,rrule) m WHERE m >= base_day LOOP |
---|
583 | -- IF /* test_byyearday_rule(current,rrule.byyearday) |
---|
584 | -- AND */ test_bymonth_rule(current,rrule.bymonth) |
---|
585 | -- THEN |
---|
586 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
587 | IF current >= mindate THEN |
---|
588 | RETURN NEXT current; |
---|
589 | END IF; |
---|
590 | loopcount := loopcount + 1; |
---|
591 | EXIT WHEN loopcount >= loopmax; |
---|
592 | -- END IF; |
---|
593 | END LOOP; |
---|
594 | current_base := current_base + (rrule.interval::text || ' months')::interval; |
---|
595 | ELSIF rrule.freq = 'YEARLY' THEN |
---|
596 | FOR current IN SELECT y FROM yearly_set(current_base,rrule) y WHERE y >= base_day LOOP |
---|
597 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
598 | IF current >= mindate THEN |
---|
599 | RETURN NEXT current; |
---|
600 | END IF; |
---|
601 | loopcount := loopcount + 1; |
---|
602 | EXIT WHEN loopcount >= loopmax; |
---|
603 | END LOOP; |
---|
604 | current_base := current_base + (rrule.interval::text || ' years')::interval; |
---|
605 | ELSE |
---|
606 | RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq; |
---|
607 | RETURN; |
---|
608 | END IF; |
---|
609 | EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until; |
---|
610 | END LOOP; |
---|
611 | -- RETURN QUERY; |
---|
612 | END; |
---|
613 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
614 | |
---|
615 | |
---|
616 | ------------------------------------------------------------------------------------------------------ |
---|
617 | -- A simplified DTSTART/RRULE only interface which applies some performance assumptions |
---|
618 | ------------------------------------------------------------------------------------------------------ |
---|
619 | CREATE or REPLACE FUNCTION event_instances( TIMESTAMP WITH TIME ZONE, TEXT ) |
---|
620 | RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$ |
---|
621 | DECLARE |
---|
622 | basedate ALIAS FOR $1; |
---|
623 | repeatrule ALIAS FOR $2; |
---|
624 | maxdate TIMESTAMP WITH TIME ZONE; |
---|
625 | BEGIN |
---|
626 | maxdate := current_date + '10 years'::interval; |
---|
627 | RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d; |
---|
628 | END; |
---|
629 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
630 | |
---|
631 | |
---|
632 | ------------------------------------------------------------------------------------------------------ |
---|
633 | -- In most cases we just want to know if there *is* an event overlapping the range, so we have a |
---|
634 | -- specific function for that. Note that this is *not* strict, and can be called with NULLs. |
---|
635 | ------------------------------------------------------------------------------------------------------ |
---|
636 | CREATE or REPLACE FUNCTION rrule_event_overlaps( TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, TEXT, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH TIME ZONE ) |
---|
637 | RETURNS BOOLEAN AS $$ |
---|
638 | DECLARE |
---|
639 | dtstart ALIAS FOR $1; |
---|
640 | dtend ALIAS FOR $2; |
---|
641 | repeatrule ALIAS FOR $3; |
---|
642 | in_mindate ALIAS FOR $4; |
---|
643 | in_maxdate ALIAS FOR $5; |
---|
644 | base_date TIMESTAMP WITH TIME ZONE; |
---|
645 | mindate TIMESTAMP WITH TIME ZONE; |
---|
646 | maxdate TIMESTAMP WITH TIME ZONE; |
---|
647 | BEGIN |
---|
648 | |
---|
649 | IF dtstart IS NULL THEN |
---|
650 | RETURN NULL; |
---|
651 | END IF; |
---|
652 | IF dtend IS NULL THEN |
---|
653 | base_date := dtstart; |
---|
654 | ELSE |
---|
655 | base_date := dtend; |
---|
656 | END IF; |
---|
657 | |
---|
658 | IF in_mindate IS NULL THEN |
---|
659 | mindate := current_date - '10 years'::interval; |
---|
660 | ELSE |
---|
661 | mindate := in_mindate; |
---|
662 | END IF; |
---|
663 | |
---|
664 | IF in_maxdate IS NULL THEN |
---|
665 | maxdate := current_date + '10 years'::interval; |
---|
666 | ELSE |
---|
667 | -- If we add the duration onto the event, then an overlap occurs if dtend <= increased end of range. |
---|
668 | maxdate := in_maxdate + (base_date - dtstart); |
---|
669 | END IF; |
---|
670 | |
---|
671 | IF repeatrule IS NULL THEN |
---|
672 | RETURN (dtstart <= maxdate AND base_date >= mindate); |
---|
673 | END IF; |
---|
674 | |
---|
675 | SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1; |
---|
676 | RETURN FOUND; |
---|
677 | |
---|
678 | END; |
---|
679 | $$ LANGUAGE 'plpgsql' IMMUTABLE; |
---|
680 | |
---|
681 | |
---|
682 | -- Create a composite type for the parts of the RRULE. |
---|
683 | DROP TYPE rrule_instance CASCADE; |
---|
684 | CREATE TYPE rrule_instance AS ( |
---|
685 | dtstart TIMESTAMP WITH TIME ZONE, |
---|
686 | rrule TEXT, |
---|
687 | instance TIMESTAMP WITH TIME ZONE |
---|
688 | ); |
---|
689 | |
---|
690 | CREATE or REPLACE FUNCTION rrule_event_instances( TIMESTAMP WITH TIME ZONE, TEXT ) |
---|
691 | RETURNS SETOF rrule_instance AS $$ |
---|
692 | DECLARE |
---|
693 | basedate ALIAS FOR $1; |
---|
694 | repeatrule ALIAS FOR $2; |
---|
695 | maxdate TIMESTAMP WITH TIME ZONE; |
---|
696 | current TIMESTAMP WITH TIME ZONE; |
---|
697 | result rrule_instance%ROWTYPE; |
---|
698 | BEGIN |
---|
699 | maxdate := current_date + '10 years'::interval; |
---|
700 | |
---|
701 | result.dtstart := basedate; |
---|
702 | result.rrule := repeatrule; |
---|
703 | |
---|
704 | FOR current IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP |
---|
705 | result.instance := current; |
---|
706 | RETURN NEXT result; |
---|
707 | END LOOP; |
---|
708 | |
---|
709 | END; |
---|
710 | $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT; |
---|
711 | |
---|
712 | |
---|
713 | CREATE or REPLACE FUNCTION icalendar_interval_to_SQL( TEXT ) RETURNS interval AS $function$ |
---|
714 | SELECT CASE WHEN substring($1,1,1) = '-' THEN -1 ELSE 1 END * regexp_replace( regexp_replace($1, '[PT-]', '', 'g'), '([A-Z])', E'\\1 ', 'g')::interval; |
---|
715 | $function$ LANGUAGE 'SQL' IMMUTABLE STRICT; |
---|
716 | |
---|