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 |
|
---|