root/trunk/functions/cronschedule.sql

Revision 9, 6.8 kB (checked in by robert, 7 years ago)

a function that takes vixie style input and gives back list of accepted times

Line 
1 create or replace function cronschedule
2     (
3     in p_starttime timestamp without time zone,
4     in p_stoptime timestamp without time zone,
5     in p_min text, in p_hour text, in p_dom text,
6     in p_month text, in p_dow text
7     )
8 RETURNS SETOF TIMESTAMPTZ
9 RETURNS NULL ON NULL INPUT
10 AS $$
11 DECLARE
12 v_sql text;
13 v_record record;
14
15 v_startint integer;
16 v_endint integer;
17 v_return_me boolean;
18
19 v_min_arr text[];
20 v_hour_arr text[];
21 v_dom_arr text[];
22 v_month_arr text[];
23 v_dow_arr text[];
24
25 v_i integer;
26 v_rec record;
27 v_nums record;
28 v_pos integer;
29 v_mod text;
30  
31 v_min_all integer[];
32 v_hour_all integer[];
33 v_dom_all integer[];
34 v_month_all integer[];
35 v_dow_all integer[];
36
37 BEGIN
38
39 select extract(epoch from p_starttime)::integer, extract(epoch from p_stoptime)::integer into v_startint, v_endint;
40 raise notice 'intvals: % & %',v_startint, v_endint;
41
42 IF p_min IS NOT NULL THEN
43     select string_to_array(p_min,',') into v_min_arr;
44     v_i := 0;
45     FOR v_rec IN select v_min_arr[x] as v from generate_series(1,array_upper(v_min_arr,1)) as x LOOP
46         -- RAISE NOTICE 'processing entry %',v_rec.v;
47         IF v_rec.v = '*' THEN
48             FOR v_nums IN select * from generate_series(0,59) LOOP
49                 v_min_all[v_i] := v_nums.generate_series;
50                 v_i := v_i+1;
51             END LOOP;
52         ELSIF v_rec.v ~ '\\*' THEN
53             select strpos(v_rec.v,'/') into v_pos;
54             select substr(v_rec.v,v_pos+1) into v_mod;
55             -- RAISE NOTICE 'pos & mod %,%',v_pos, v_mod;
56             FOR v_nums IN select * from generate_series(0,59) LOOP
57                 IF (v_nums.generate_series % v_mod::int = 0) THEN
58                     v_min_all[v_i] := v_nums.generate_series;
59                     v_i := v_i+1;
60                 END IF;
61             END LOOP;
62         ELSE
63            v_min_all[v_i] := v_rec.v;
64            v_i := v_i+1;
65         END IF;
66     END LOOP;
67 END IF;
68
69
70 IF p_hour IS NOT NULL THEN
71 select string_to_array(p_hour,',') into v_hour_arr;
72     v_i := 0;
73     FOR v_rec IN select v_hour_arr[x] as v from generate_series(1,array_upper(v_hour_arr,1)) as x LOOP
74         -- RAISE NOTICE 'processing entry %',v_rec.v;
75         IF v_rec.v = '*' THEN
76             FOR v_nums IN select * from generate_series(0,23) LOOP
77                 v_hour_all[v_i] := v_nums.generate_series;
78                 v_i := v_i+1;
79             END LOOP;
80         ELSIF v_rec.v ~ '\\*' THEN
81             select strpos(v_rec.v,'/') into v_pos;
82             select substr(v_rec.v,v_pos+1) into v_mod;
83             -- RAISE NOTICE 'pos & mod %,%',v_pos, v_mod;
84             FOR v_nums IN select * from generate_series(0,23) LOOP
85                 IF (v_nums.generate_series % v_mod::int = 0) THEN
86                     v_hour_all[v_i] := v_nums.generate_series;
87                     v_i := v_i+1;
88                 END IF;
89             END LOOP;
90         ELSE
91            v_hour_all[v_i] := v_rec.v;
92            v_i := v_i+1;
93         END IF;
94     END LOOP;
95 END IF;
96
97 IF p_dom IS NOT NULL THEN
98     select string_to_array(p_dom,',') into v_dom_arr;
99     v_i := 0;
100     FOR v_rec IN select v_dom_arr[x] as v from generate_series(1,array_upper(v_dom_arr,1)) as x LOOP
101         -- RAISE NOTICE 'processing entry %',v_rec.v;
102         IF v_rec.v = '*' THEN
103             FOR v_nums IN select * from generate_series(1,31) LOOP
104                 v_dom_all[v_i] := v_nums.generate_series;
105                 v_i := v_i+1;
106             END LOOP;
107         ELSIF v_rec.v ~ '\\*' THEN
108             select strpos(v_rec.v,'/') into v_pos;
109             select substr(v_rec.v,v_pos+1) into v_mod;
110             -- RAISE NOTICE 'pos & mod %,%',v_pos, v_mod;
111             FOR v_nums IN select * from generate_series(1,31) LOOP
112                 IF (v_nums.generate_series % v_mod::int = 0) THEN
113                     v_dom_all[v_i] := v_nums.generate_series;
114                     v_i := v_i+1;
115                 END IF;
116             END LOOP;
117         ELSE
118            v_dom_all[v_i] := v_rec.v;
119            v_i := v_i+1;
120         END IF;
121     END LOOP;
122 END IF;
123
124 IF p_month IS NOT NULL THEN
125     select string_to_array(p_month,',') into v_month_arr;
126     v_i := 0;
127     FOR v_rec IN select v_month_arr[x] as v from generate_series(1,array_upper(v_month_arr,1)) as x LOOP
128         -- RAISE NOTICE 'processing entry %',v_rec.v;
129         IF v_rec.v = '*' THEN
130             FOR v_nums IN select * from generate_series(1,12) LOOP
131                 v_month_all[v_i] := v_nums.generate_series;
132                 v_i := v_i+1;
133             END LOOP;
134         ELSIF v_rec.v ~ '\\*' THEN
135             select strpos(v_rec.v,'/') into v_pos;
136             select substr(v_rec.v,v_pos+1) into v_mod;
137             -- RAISE NOTICE 'pos & mod %,%',v_pos, v_mod;
138             FOR v_nums IN select * from generate_series(1,12) LOOP
139                 IF (v_nums.generate_series % v_mod::int = 0) THEN
140                     v_month_all[v_i] := v_nums.generate_series;
141                     v_i := v_i+1;
142                 END IF;
143             END LOOP;
144         ELSE
145            v_month_all[v_i] := v_rec.v;
146            v_i := v_i+1;
147         END IF;
148     END LOOP;
149 END IF;
150
151 IF p_dow IS NOT NULL THEN
152     select string_to_array(p_dow,',') into v_dow_arr;
153     v_i := 0;
154     FOR v_rec IN select v_dow_arr[x] as v from generate_series(1,array_upper(v_dow_arr,1)) as x LOOP
155         -- RAISE NOTICE 'processing entry %',v_rec.v;
156         IF v_rec.v = '*' THEN
157             FOR v_nums IN select * from generate_series(0,6) LOOP
158                 v_dow_all[v_i] := v_nums.generate_series;
159                 v_i := v_i+1;
160             END LOOP;
161         ELSIF v_rec.v ~ '\\*' THEN
162             select strpos(v_rec.v,'/') into v_pos;
163             select substr(v_rec.v,v_pos+1) into v_mod;
164             -- RAISE NOTICE 'pos & mod %,%',v_pos, v_mod;
165             FOR v_nums IN select * from generate_series(0,6) LOOP
166                 IF (v_nums.generate_series % v_mod::int = 0) THEN
167                     v_dow_all[v_i] := v_nums.generate_series;
168                     v_i := v_i+1;
169                 END IF;
170             END LOOP;
171         ELSE
172            -- in vixie, you can use 0 or 7 for sunday, in pg it must be 0
173            IF v_rec.v = 7 THEN
174                 v_rec.v := 0;
175            END IF;
176            v_dow_all[v_i] := v_rec.v;
177            v_i := v_i+1;
178         END IF;
179     END LOOP;
180 END IF;
181
182
183 FOR v_record IN
184     select (p_starttime + '1 minute'::interval * x) as ptime from generate_series(0,(v_endint-v_startint)/60) x
185 LOOP
186     IF extract(minute from v_record.ptime) <> ALL (v_min_all) THEN
187         continue;
188     END IF;
189
190     IF extract(hour from v_record.ptime) <> ALL (v_hour_all) THEN
191         continue;
192     END IF;
193    
194     IF extract(day from v_record.ptime) <> ALL (v_dom_all) THEN
195         continue;
196     END IF;
197
198     IF extract(month from v_record.ptime) <> ALL (v_month_all) THEN
199         continue;
200     END IF;
201
202     IF extract(dow from v_record.ptime) <> ALL (v_dow_all) THEN
203         continue;
204     END IF;
205
206     RETURN NEXT v_record.ptime;
207
208 END LOOP;
209
210 RETURN;
211
212 END
213 $$ LANGUAGE plpgsql;
214
Note: See TracBrowser for help on using the browser.