2 * PostgreSQL Functions for RRULE handling
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
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
18 * - DAILY: BYYEARDAY, BYSETPOS*
20 * - MONTHLY: BYYEARDAY, BYMONTHDAY, BYWEEKNO
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,
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 $$
52 basedate ALIAS FOR $1;
53 repeatrule ALIAS FOR $2;
54 result rrule_parts%ROWTYPE;
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)(;|$)');
64 result.byday := string_to_array( substring(repeatrule from 'BYDAY=(([+-]?[0-9]{0,2}(MO|TU|WE|TH|FR|SA|SU),?)+)(;|$)'), ',');
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},?)+)(;|$)'), ',');
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,]+)(;|$)'), ',');
78 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
91 each_day TIMESTAMP WITH TIME ZONE;
93 results TIMESTAMP WITH TIME ZONE[];
97 -- We still return the single date as a SET
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 );
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;
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;
120 RAISE NOTICE 'Ignored invalid BYDAY rule part "%".', bydayrule;
122 -- The simplest case, such as 2MO for the second monday
123 each_day := each_day + ((index - 1)::text || ' weeks')::interval;
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;
129 -- Note that since index is negative, (-2 + 1) == -1, for example
132 each_day := each_day + (index::text || ' weeks')::interval ;
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;
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;
156 IF results[i] IS NOT NULL THEN
157 RETURN NEXT results[i];
164 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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 $$
170 in_time ALIAS FOR $1;
171 bymonthday ALIAS FOR $2;
172 month_start TIMESTAMP WITH TIME ZONE;
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' );
181 EXIT WHEN bymonthday[i] IS NULL;
183 CONTINUE WHEN bymonthday[i] > daysinmonth;
184 CONTINUE WHEN bymonthday[i] < (-1 * daysinmonth);
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;
191 RAISE NOTICE 'Ignored invalid BYMONTHDAY part "%".', bymonthday[i];
198 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
204 in_time ALIAS FOR $1;
208 our_day TIMESTAMP WITH TIME ZONE;
212 IF byday IS NULL THEN
213 -- We still return the single date as a SET
218 our_day := date_trunc( 'week', in_time ) + (in_time::time)::interval;
222 WHILE dayrule IS NOT NULL LOOP
223 dow := position(dayrule in 'SUMOTUWETHFRSA') / 2;
224 RETURN NEXT our_day + ((dow - 1)::text || 'days')::interval;
232 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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;
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 $$
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) );
255 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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 $$
264 bymonth ALIAS FOR $2;
266 IF bymonth IS NOT NULL THEN
267 RETURN ( date_part( 'month', testme) = ANY (bymonth) );
271 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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 $$
280 bymonthday ALIAS FOR $2;
282 IF bymonthday IS NOT NULL THEN
283 RETURN ( date_part( 'day', testme) = ANY (bymonthday) );
287 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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 $$
296 byyearday ALIAS FOR $2;
298 IF byyearday IS NOT NULL THEN
299 RETURN ( date_part( 'doy', testme) = ANY (byyearday) );
303 $$ LANGUAGE 'plpgsql' IMMUTABLE;
306 ------------------------------------------------------------------------------------------------------
307 -- Given a cursor into a set, process the set returning the subset matching the BYSETPOS
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 $$
316 bysetpos ALIAS FOR $2;
317 valid_date TIMESTAMP WITH TIME ZONE;
321 IF bysetpos IS NULL THEN
323 FETCH curse INTO valid_date;
325 RETURN NEXT valid_date;
329 EXIT WHEN bysetpos[i] IS NULL;
330 IF bysetpos[i] > 0 THEN
331 FETCH ABSOLUTE bysetpos[i] FROM curse INTO valid_date;
334 FETCH RELATIVE (bysetpos[i] + 1) FROM curse INTO valid_date;
336 IF valid_date IS NOT NULL THEN
337 RETURN NEXT valid_date;
343 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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.
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 $$
359 IF rrule.bymonth IS NOT NULL AND NOT date_part('month',after) = ANY ( rrule.bymonth ) THEN
363 IF rrule.byweekno IS NOT NULL AND NOT date_part('week',after) = ANY ( rrule.byweekno ) THEN
367 IF rrule.byyearday IS NOT NULL AND NOT date_part('doy',after) = ANY ( rrule.byyearday ) THEN
371 IF rrule.bymonthday IS NOT NULL AND NOT date_part('day',after) = ANY ( rrule.bymonthday ) THEN
375 IF rrule.byday IS NOT NULL AND NOT substring( to_char( after, 'DY') for 2 from 1) = ANY ( rrule.byday ) THEN
379 -- Since we don't do BYHOUR, BYMINUTE or BYSECOND yet this becomes a trivial
383 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
386 ------------------------------------------------------------------------------------------------------
387 -- Return another week's worth of events
389 -- Doesn't handle truly obscure and unlikely stuff like BYWEEKNO=5;BYMONTH=1;BYDAY=WE,TH,FR;BYSETPOS=-2
391 ------------------------------------------------------------------------------------------------------
392 CREATE or REPLACE FUNCTION weekly_set( TIMESTAMP WITH TIME ZONE, rrule_parts ) RETURNS SETOF TIMESTAMP WITH TIME ZONE AS $$
396 valid_date TIMESTAMP WITH TIME ZONE;
402 IF rrule.byweekno IS NOT NULL THEN
403 weekno := date_part('week',after);
404 IF NOT weekno = ANY ( rrule.byweekno ) THEN
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;
413 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
423 valid_date TIMESTAMP WITH TIME ZONE;
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:
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.
441 * My guess is that this means 'INTERSECT'
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
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;
450 OPEN curse SCROLL FOR SELECT r FROM rrule_month_byday_set(after, rrule.byday ) r ORDER BY 1;
453 RETURN QUERY SELECT d FROM rrule_bysetpos_filter(curse,rrule.bysetpos) d;
456 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
466 current_base TIMESTAMP WITH TIME ZONE;
471 IF rrule.bymonth IS NOT NULL THEN
472 -- Ensure we don't pass BYSETPOS down
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;
481 -- We don't yet implement byweekno, byblah
486 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
496 current_base TIMESTAMP WITH TIME ZONE;
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;
509 -- We don't yet implement byweekno, byblah
513 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
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;
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;
536 SELECT * INTO rrule FROM parse_rrule_parts( basedate, repeatrule );
538 IF rrule.count IS NOT NULL THEN
539 loopmax := rrule.count;
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;
547 loopmax := max_count;
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
561 loopcount := loopcount + 1;
562 EXIT WHEN loopcount >= loopmax;
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)
572 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
573 IF current >= mindate THEN
576 loopcount := loopcount + 1;
577 EXIT WHEN loopcount >= loopmax;
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)
586 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
587 IF current >= mindate THEN
590 loopcount := loopcount + 1;
591 EXIT WHEN loopcount >= loopmax;
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
601 loopcount := loopcount + 1;
602 EXIT WHEN loopcount >= loopmax;
604 current_base := current_base + (rrule.interval::text || ' years')::interval;
606 RAISE NOTICE 'A frequency of "%" is not handled', rrule.freq;
609 EXIT WHEN rrule.until IS NOT NULL AND current > rrule.until;
613 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
622 basedate ALIAS FOR $1;
623 repeatrule ALIAS FOR $2;
624 maxdate TIMESTAMP WITH TIME ZONE;
626 maxdate := current_date + '10 years'::interval;
627 RETURN QUERY SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d;
629 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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 $$
639 dtstart ALIAS FOR $1;
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;
649 IF dtstart IS NULL THEN
652 IF dtend IS NULL THEN
653 base_date := dtstart;
658 IF in_mindate IS NULL THEN
659 mindate := current_date - '10 years'::interval;
661 mindate := in_mindate;
664 IF in_maxdate IS NULL THEN
665 maxdate := current_date + '10 years'::interval;
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);
671 IF repeatrule IS NULL THEN
672 RETURN (dtstart <= maxdate AND base_date >= mindate);
675 SELECT d INTO mindate FROM rrule_event_instances_range( base_date, repeatrule, mindate, maxdate, 60 ) d LIMIT 1;
679 $$ LANGUAGE 'plpgsql' IMMUTABLE;
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,
687 instance TIMESTAMP WITH TIME ZONE
690 CREATE or REPLACE FUNCTION rrule_event_instances( TIMESTAMP WITH TIME ZONE, TEXT )
691 RETURNS SETOF rrule_instance AS $$
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;
699 maxdate := current_date + '10 years'::interval;
701 result.dtstart := basedate;
702 result.rrule := repeatrule;
704 FOR current IN SELECT d FROM rrule_event_instances_range( basedate, repeatrule, basedate, maxdate, 300 ) d LOOP
705 result.instance := current;
710 $$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;
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;