Stored Functions and Procedures
Description
This section documents advanced PostgreSQL PL/pgSQL functions and procedures implemented for analytical processing, booking validation, overlap prevention, and RSVP reporting.
The implementation demonstrates reusable database-side logic for operational validation and analytical reporting.
1. calculate_wedding_total_cost()
Description
Calculates the total wedding cost by aggregating:
- venue booking expenses
- photographer booking expenses
- band booking expenses
SQL Code
CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER)
RETURNS NUMERIC AS $$
DECLARE
venue_cost NUMERIC := 0;
photographer_cost NUMERIC := 0;
band_cost NUMERIC := 0;
BEGIN
SELECT COALESCE(SUM(price), 0)
INTO venue_cost
FROM venue_booking
WHERE wedding_id = p_wedding_id
AND status = 'confirmed';
SELECT COALESCE(SUM(
EXTRACT(EPOCH FROM (pb.end_time - pb.start_time)) / 3600
* p.price_per_hour
), 0)
INTO photographer_cost
FROM photographer_booking pb
JOIN photographer p
ON pb.photographer_id = p.photographer_id
WHERE pb.wedding_id = p_wedding_id
AND pb.status = 'confirmed';
SELECT COALESCE(SUM(
EXTRACT(EPOCH FROM (bb.end_time - bb.start_time)) / 3600
* b.price_per_hour
), 0)
INTO band_cost
FROM band_booking bb
JOIN band b
ON bb.band_id = b.band_id
WHERE bb.wedding_id = p_wedding_id
AND bb.status = 'confirmed';
RETURN venue_cost + photographer_cost + band_cost;
END;
$$ LANGUAGE plpgsql;
2. is_venue_available()
Description
Checks whether a venue is available for a requested time interval.
The implementation explicitly validates multiple overlap scenarios using separate interval comparison conditions.
The validation detects:
- intervals starting inside existing intervals
- intervals ending inside existing intervals
- intervals completely containing existing intervals
- intervals fully contained inside existing intervals
- identical intervals
- partial overlaps
Boundary-touching intervals are treated as valid non-overlapping intervals.
SQL Code
CREATE OR REPLACE FUNCTION is_venue_available(
p_venue_id INTEGER,
p_date DATE,
p_start TIME,
p_end TIME
)
RETURNS BOOLEAN AS $$
BEGIN
IF p_end <= p_start THEN
RAISE EXCEPTION
'Invalid interval: end time must be after start time.';
END IF;
RETURN NOT EXISTS (
SELECT 1
FROM venue_booking vb
WHERE vb.venue_id = p_venue_id
AND vb.status <> 'cancelled'
AND (
((p_date + p_start) >= (vb."date" + vb.start_time)
AND (p_date + p_start) < (vb."date" + vb.end_time))
OR
((p_date + p_end) > (vb."date" + vb.start_time)
AND (p_date + p_end) <= (vb."date" + vb.end_time))
OR
((p_date + p_start) <= (vb."date" + vb.start_time)
AND (p_date + p_end) >= (vb."date" + vb.end_time))
OR
((p_date + p_start) >= (vb."date" + vb.start_time)
AND (p_date + p_end) <= (vb."date" + vb.end_time))
)
);
END;
$$ LANGUAGE plpgsql;
3. is_photographer_available()
Description
Checks whether a photographer is available for a requested booking interval.
The function validates all important overlap scenarios and prevents conflicting reservations.
SQL Code
CREATE OR REPLACE FUNCTION is_photographer_available(
p_photographer_id INTEGER,
p_date DATE,
p_start TIME,
p_end TIME
)
RETURNS BOOLEAN AS $$
BEGIN
IF p_end <= p_start THEN
RAISE EXCEPTION
'Invalid interval: end time must be after start time.';
END IF;
RETURN NOT EXISTS (
SELECT 1
FROM photographer_booking pb
WHERE pb.photographer_id = p_photographer_id
AND pb.status <> 'cancelled'
AND (
((p_date + p_start) >= (pb."date" + pb.start_time)
AND (p_date + p_start) < (pb."date" + pb.end_time))
OR
((p_date + p_end) > (pb."date" + pb.start_time)
AND (p_date + p_end) <= (pb."date" + pb.end_time))
OR
((p_date + p_start) <= (pb."date" + pb.start_time)
AND (p_date + p_end) >= (pb."date" + pb.end_time))
OR
((p_date + p_start) >= (pb."date" + pb.start_time)
AND (p_date + p_end) <= (pb."date" + pb.end_time))
)
);
END;
$$ LANGUAGE plpgsql;
4. is_band_available()
Description
Checks whether a music band is available for a requested booking interval.
The implementation prevents all major overlap conflict scenarios.
SQL Code
CREATE OR REPLACE FUNCTION is_band_available(
p_band_id INTEGER,
p_date DATE,
p_start TIME,
p_end TIME
)
RETURNS BOOLEAN AS $$
BEGIN
IF p_end <= p_start THEN
RAISE EXCEPTION
'Invalid interval: end time must be after start time.';
END IF;
RETURN NOT EXISTS (
SELECT 1
FROM band_booking bb
WHERE bb.band_id = p_band_id
AND bb.status <> 'cancelled'
AND (
((p_date + p_start) >= (bb."date" + bb.start_time)
AND (p_date + p_start) < (bb."date" + bb.end_time))
OR
((p_date + p_end) > (bb."date" + bb.start_time)
AND (p_date + p_end) <= (bb."date" + bb.end_time))
OR
((p_date + p_start) <= (bb."date" + bb.start_time)
AND (p_date + p_end) >= (bb."date" + bb.end_time))
OR
((p_date + p_start) >= (bb."date" + bb.start_time)
AND (p_date + p_end) <= (bb."date" + bb.end_time))
)
);
END;
$$ LANGUAGE plpgsql;
5. generate_rsvp_summary()
Description
Generates RSVP statistics for a selected event.
The procedure reports:
- accepted RSVPs
- declined RSVPs
- pending RSVPs
SQL Code
CREATE OR REPLACE PROCEDURE generate_rsvp_summary(
p_event_id INTEGER
)
LANGUAGE plpgsql
AS $$
BEGIN
RAISE NOTICE 'Accepted: %',
(
SELECT COUNT(*)
FROM event_rsvp
WHERE event_id = p_event_id
AND status = 'accepted'
);
RAISE NOTICE 'Declined: %',
(
SELECT COUNT(*)
FROM event_rsvp
WHERE event_id = p_event_id
AND status = 'declined'
);
RAISE NOTICE 'Pending: %',
(
SELECT COUNT(*)
FROM event_rsvp
WHERE event_id = p_event_id
AND status = 'pending'
);
END;
$$;
Summary
The implemented functions and procedures provide:
- reusable analytical logic
- overlap conflict prevention
- interval validation
- vendor availability checking
- RSVP reporting
- financial calculation functionality
The implementation demonstrates advanced procedural programming capabilities inside PostgreSQL using PL/pgSQL.
