# root/trunk/functions/cronschedule.sql

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

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.