Changes between Version 8 and Version 9 of Stored Functions and Procedures
- Timestamp:
- 05/18/26 23:38:58 (8 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Stored Functions and Procedures
v8 v9 3 3 == Description == 4 4 5 This section contains reusable SQL functions and procedures used for calculations, validation, and reporting. 5 This section documents advanced PostgreSQL PL/pgSQL functions and procedures implemented for analytical processing, booking validation, overlap prevention, and RSVP reporting. 6 7 The implementation demonstrates reusable database-side logic for operational validation and analytical reporting. 6 8 7 9 == 1. calculate_wedding_total_cost() == … … 9 11 === Description === 10 12 11 Calculates the total confirmed cost of a wedding using venue, photographer, and band bookings. 12 13 The function calculates venue costs directly from booking prices and additionally computes photographer and band costs based on booked duration and hourly pricing. 13 Calculates the total wedding cost by aggregating: 14 * venue booking expenses 15 * photographer booking expenses 16 * band booking expenses 14 17 15 18 === SQL Code === … … 19 22 CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER) 20 23 RETURNS NUMERIC AS $$ 24 21 25 DECLARE 26 22 27 venue_cost NUMERIC := 0; 23 28 photographer_cost NUMERIC := 0; 24 29 band_cost NUMERIC := 0; 25 BEGIN 30 31 BEGIN 32 26 33 SELECT COALESCE(SUM(price), 0) 27 34 INTO venue_cost … … 34 41 * p.price_per_hour 35 42 ), 0) 43 36 44 INTO photographer_cost 45 37 46 FROM photographer_booking pb 38 JOIN photographer p ON pb.photographer_id = p.photographer_id 47 48 JOIN photographer p 49 ON pb.photographer_id = p.photographer_id 50 39 51 WHERE pb.wedding_id = p_wedding_id 40 52 AND pb.status = 'confirmed'; … … 44 56 * b.price_per_hour 45 57 ), 0) 58 46 59 INTO band_cost 60 47 61 FROM band_booking bb 48 JOIN band b ON bb.band_id = b.band_id 62 63 JOIN band b 64 ON bb.band_id = b.band_id 65 49 66 WHERE bb.wedding_id = p_wedding_id 50 67 AND bb.status = 'confirmed'; 51 68 52 69 RETURN venue_cost + photographer_cost + band_cost; 70 53 71 END; 54 72 $$ LANGUAGE plpgsql; … … 59 77 === Description === 60 78 61 Checks whether a venue is available for a selected date and time interval. 62 63 This implementation uses complete timestamp interval comparison instead of simple date equality checks in order to correctly detect all possible overlap scenarios. 64 65 This function compares complete timestamp intervals by combining the booking date with start and end time. It does not only compare equal dates. 66 67 The overlap rule is: 68 69 {{{ 70 #!sql 71 (new_start < existing_end) AND (new_end > existing_start) 72 }}} 73 74 This condition detects all possible overlap cases: 75 * the new interval starts inside an existing interval 76 * the new interval ends inside an existing interval 77 * the new interval completely contains an existing interval 78 * the existing interval completely contains the new interval 79 * both intervals are exactly the same 80 * intervals that only touch at the boundary are not treated as overlapping 79 Checks whether a venue is available for a requested time interval. 80 81 The implementation explicitly validates multiple overlap scenarios using separate interval comparison conditions. 82 83 The validation detects: 84 * intervals starting inside existing intervals 85 * intervals ending inside existing intervals 86 * intervals completely containing existing intervals 87 * intervals fully contained inside existing intervals 88 * identical intervals 89 * partial overlaps 90 91 Boundary-touching intervals are treated as valid non-overlapping intervals. 81 92 82 93 === SQL Code === … … 85 96 #!sql 86 97 CREATE OR REPLACE FUNCTION is_venue_available( 98 87 99 p_venue_id INTEGER, 88 100 p_date DATE, 89 101 p_start TIME, 90 102 p_end TIME 103 91 104 ) 105 92 106 RETURNS BOOLEAN AS $$ 93 BEGIN 107 108 BEGIN 109 94 110 IF p_end <= p_start THEN 95 RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; 111 RAISE EXCEPTION 112 'Invalid interval: end time must be after start time.'; 96 113 END IF; 97 114 98 115 RETURN NOT EXISTS ( 116 99 117 SELECT 1 118 100 119 FROM venue_booking vb 120 101 121 WHERE vb.venue_id = p_venue_id 102 122 AND vb.status <> 'cancelled' 123 103 124 AND ( 104 125 … … 122 143 ) 123 144 ); 145 124 146 END; 125 147 $$ LANGUAGE plpgsql; 148 }}} 126 149 127 150 == 3. is_photographer_available() == … … 129 152 === Description === 130 153 131 Checks whether a photographer is available for a selected date and time interval. 132 133 This implementation uses complete timestamp interval comparison instead of simple date equality checks in order to correctly detect all possible overlap scenarios. 134 135 The function combines booking dates and times into complete timestamp intervals in order to correctly validate overlapping reservations. 154 Checks whether a photographer is available for a requested booking interval. 155 156 The function validates all important overlap scenarios and prevents conflicting reservations. 136 157 137 158 === SQL Code === … … 140 161 #!sql 141 162 CREATE OR REPLACE FUNCTION is_photographer_available( 163 142 164 p_photographer_id INTEGER, 143 165 p_date DATE, 144 166 p_start TIME, 145 167 p_end TIME 168 146 169 ) 170 147 171 RETURNS BOOLEAN AS $$ 148 BEGIN 172 173 BEGIN 174 149 175 IF p_end <= p_start THEN 150 RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; 176 RAISE EXCEPTION 177 'Invalid interval: end time must be after start time.'; 151 178 END IF; 152 179 153 180 RETURN NOT EXISTS ( 181 154 182 SELECT 1 183 155 184 FROM photographer_booking pb 185 156 186 WHERE pb.photographer_id = p_photographer_id 157 187 AND pb.status <> 'cancelled' 188 158 189 AND ( 159 190 … … 177 208 ) 178 209 ); 210 179 211 END; 180 212 $$ LANGUAGE plpgsql; 213 }}} 181 214 182 215 == 4. is_band_available() == … … 184 217 === Description === 185 218 186 Checks whether a band is available for a selected date and time interval. 187 188 This implementation uses complete timestamp interval comparison instead of simple date equality checks in order to correctly detect all possible overlap scenarios. 189 190 The function validates overlapping booking intervals using complete timestamp comparison between requested and existing reservations. 219 Checks whether a music band is available for a requested booking interval. 220 221 The implementation prevents all major overlap conflict scenarios. 191 222 192 223 === SQL Code === … … 195 226 #!sql 196 227 CREATE OR REPLACE FUNCTION is_band_available( 228 197 229 p_band_id INTEGER, 198 230 p_date DATE, 199 231 p_start TIME, 200 232 p_end TIME 233 201 234 ) 235 202 236 RETURNS BOOLEAN AS $$ 203 BEGIN 237 238 BEGIN 239 204 240 IF p_end <= p_start THEN 205 RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; 241 RAISE EXCEPTION 242 'Invalid interval: end time must be after start time.'; 206 243 END IF; 207 244 208 245 RETURN NOT EXISTS ( 246 209 247 SELECT 1 248 210 249 FROM band_booking bb 250 211 251 WHERE bb.band_id = p_band_id 212 252 AND bb.status <> 'cancelled' 253 213 254 AND ( 214 255 … … 232 273 ) 233 274 ); 275 234 276 END; 235 277 $$ LANGUAGE plpgsql; 278 }}} 236 279 237 280 == 5. generate_rsvp_summary() == … … 239 282 === Description === 240 283 241 Returns RSVP statistics for a selected event. 242 243 The procedure generates summarized RSVP information including accepted, declined, and pending responses for analytical and reporting purposes. 244 245 === SQL Code === 246 247 {{{ 248 #!sql 249 CREATE OR REPLACE PROCEDURE generate_rsvp_summary(p_event_id INTEGER) 284 Generates RSVP statistics for a selected event. 285 286 The procedure reports: 287 * accepted RSVPs 288 * declined RSVPs 289 * pending RSVPs 290 291 === SQL Code === 292 293 {{{ 294 #!sql 295 CREATE OR REPLACE PROCEDURE generate_rsvp_summary( 296 p_event_id INTEGER 297 ) 298 250 299 LANGUAGE plpgsql 300 251 301 AS $$ 252 BEGIN 302 303 BEGIN 304 253 305 RAISE NOTICE 'Accepted: %', 306 254 307 ( 255 308 SELECT COUNT(*) 309 256 310 FROM event_rsvp 311 257 312 WHERE event_id = p_event_id 258 313 AND status = 'accepted' … … 260 315 261 316 RAISE NOTICE 'Declined: %', 317 262 318 ( 263 319 SELECT COUNT(*) 320 264 321 FROM event_rsvp 322 265 323 WHERE event_id = p_event_id 266 324 AND status = 'declined' … … 268 326 269 327 RAISE NOTICE 'Pending: %', 328 270 329 ( 271 330 SELECT COUNT(*) 331 272 332 FROM event_rsvp 333 273 334 WHERE event_id = p_event_id 274 335 AND status = 'pending' 275 336 ); 337 276 338 END; 277 339 $$; 278 340 }}} 341 342 == Summary == 343 344 The implemented functions and procedures provide: 345 * reusable analytical logic 346 * overlap conflict prevention 347 * interval validation 348 * vendor availability checking 349 * RSVP reporting 350 * financial calculation functionality 351 352 The implementation demonstrates advanced procedural programming capabilities inside PostgreSQL using PL/pgSQL.
