source: contrib/davical/dba/rrule_functions-8.1.sql @ 3733

Revision 3733, 25.4 KB checked in by gabriel.malheiros, 13 years ago (diff)

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