Changes between Version 5 and Version 6 of Stored Functions and Procedures
- Timestamp:
- 05/13/26 19:58:20 (13 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
Stored Functions and Procedures
v5 v6 9 9 === Description === 10 10 11 Calculates the total cost of a wedding using venue, photographer, and band bookings. 12 13 === SQL Code === 14 15 <pre> 11 Calculates the total confirmed cost of a wedding using venue, photographer, and band bookings. 12 13 === SQL Code === 14 15 {{{ 16 #!sql 16 17 CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER) 17 18 RETURNS NUMERIC AS $$ 18 19 DECLARE 19 total_cost NUMERIC; 20 BEGIN 21 SELECT 22 COALESCE(SUM(vb.price), 0) 23 INTO total_cost 24 FROM wedding w 25 LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id 26 WHERE w.wedding_id = p_wedding_id; 27 28 RETURN total_cost; 29 END; 30 $$ LANGUAGE plpgsql; 31 </pre> 20 venue_cost NUMERIC := 0; 21 photographer_cost NUMERIC := 0; 22 band_cost NUMERIC := 0; 23 BEGIN 24 SELECT COALESCE(SUM(price), 0) 25 INTO venue_cost 26 FROM venue_booking 27 WHERE wedding_id = p_wedding_id 28 AND status = 'confirmed'; 29 30 SELECT COALESCE(SUM( 31 EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600 32 * p.price_per_hour 33 ), 0) 34 INTO photographer_cost 35 FROM photographer_booking pb 36 JOIN photographer p ON pb.photographer_id = p.photographer_id 37 WHERE pb.wedding_id = p_wedding_id 38 AND pb.status = 'confirmed'; 39 40 SELECT COALESCE(SUM( 41 EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600 42 * b.price_per_hour 43 ), 0) 44 INTO band_cost 45 FROM band_booking bb 46 JOIN band b ON bb.band_id = b.band_id 47 WHERE bb.wedding_id = p_wedding_id 48 AND bb.status = 'confirmed'; 49 50 RETURN venue_cost + photographer_cost + band_cost; 51 END; 52 $$ LANGUAGE plpgsql; 53 }}} 32 54 33 55 == 2. is_venue_available() == … … 37 59 Checks whether a venue is available for a selected date and time interval. 38 60 39 === SQL Code === 40 41 <pre> 61 This function compares complete timestamp intervals by combining the booking date with start and end time. It does not only compare equal dates. 62 63 The overlap rule is: 64 65 {{{ 66 #!sql 67 (new_start < existing_end) AND (new_end > existing_start) 68 }}} 69 70 This condition detects all possible overlap cases: 71 * the new interval starts inside an existing interval 72 * the new interval ends inside an existing interval 73 * the new interval completely contains an existing interval 74 * the existing interval completely contains the new interval 75 * both intervals are exactly the same 76 * intervals that only touch at the boundary are not treated as overlapping 77 78 === SQL Code === 79 80 {{{ 81 #!sql 42 82 CREATE OR REPLACE FUNCTION is_venue_available( 43 83 p_venue_id INTEGER, … … 48 88 RETURNS BOOLEAN AS $$ 49 89 BEGIN 90 IF p_end <= p_start THEN 91 RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; 92 END IF; 93 50 94 RETURN NOT EXISTS ( 51 95 SELECT 1 52 FROM venue_booking 53 WHERE v enue_id = p_venue_id54 AND "date" = p_date96 FROM venue_booking vb 97 WHERE vb.venue_id = p_venue_id 98 AND vb.status <> 'cancelled' 55 99 AND ( 56 p_start < end_time57 AND p_end > start_time100 (p_date + p_start) < (vb."date" + vb.end_time) 101 AND (p_date + p_end) > (vb."date" + vb.start_time) 58 102 ) 59 103 ); 60 104 END; 61 105 $$ LANGUAGE plpgsql; 62 </pre> 63 64 This function does not only compare equal dates. It compares complete timestamp intervals using: 65 66 {{{ 67 #!sql 68 (new_start < existing_end) AND (new_end > existing_start) 69 }}} 70 71 This condition detects all possible overlap cases: 72 * the new interval starts inside an existing interval 73 * the new interval ends inside an existing interval 74 * the new interval completely contains an existing interval 75 * the existing interval completely contains the new interval 76 * both intervals are exactly the same 77 * intervals that only touch at the boundary are not treated as overlapping 106 }}} 78 107 79 108 == 3. is_photographer_available() == … … 85 114 === SQL Code === 86 115 87 <pre> 116 {{{ 117 #!sql 88 118 CREATE OR REPLACE FUNCTION is_photographer_available( 89 119 p_photographer_id INTEGER, … … 94 124 RETURNS BOOLEAN AS $$ 95 125 BEGIN 126 IF p_end <= p_start THEN 127 RAISE EXCEPTION 'Invalid interval: end time must be after start time.'; 128 END IF; 129 96 130 RETURN NOT EXISTS ( 97 131 SELECT 1 98 FROM photographer_booking 99 WHERE p hotographer_id = p_photographer_id100 AND "date" = p_date132 FROM photographer_booking pb 133 WHERE pb.photographer_id = p_photographer_id 134 AND pb.status <> 'cancelled' 101 135 AND ( 102 p_start < end_time103 AND p_end > start_time136 (p_date + p_start) < (pb."date" + pb.end_time) 137 AND (p_date + p_end) > (pb."date" + pb.start_time) 104 138 ) 105 139 ); 106 140 END; 107 141 $$ LANGUAGE plpgsql; 108 </pre> 142 }}} 109 143 110 144 == 4. is_band_available() == … … 113 147 114 148 Checks whether a band is available for a selected date and time interval. 115 116 This function uses full timestamp interval comparison by combining the booking date with start and end time. This avoids incorrect availability results when intervals overlap in different ways.117 149 118 150 === SQL Code === … … 154 186 === SQL Code === 155 187 156 <pre> 188 {{{ 189 #!sql 157 190 CREATE OR REPLACE PROCEDURE generate_rsvp_summary(p_event_id INTEGER) 158 191 LANGUAGE plpgsql … … 166 199 AND status = 'accepted' 167 200 ); 201 202 RAISE NOTICE 'Declined: %', 203 ( 204 SELECT COUNT(*) 205 FROM event_rsvp 206 WHERE event_id = p_event_id 207 AND status = 'declined' 208 ); 209 210 RAISE NOTICE 'Pending: %', 211 ( 212 SELECT COUNT(*) 213 FROM event_rsvp 214 WHERE event_id = p_event_id 215 AND status = 'pending' 216 ); 168 217 END; 169 218 $$; 170 </pre> 219 }}}
