Stored Functions and Procedures

Description

This section contains reusable SQL functions and procedures used for calculations, validation, and reporting.

1. calculate_wedding_total_cost()

Description

Calculates the total confirmed cost of a wedding using venue, photographer, and band bookings.

The function calculates venue costs directly from booking prices and additionally computes photographer and band costs based on booked duration and hourly pricing.

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 selected date and time interval.

This implementation uses complete timestamp interval comparison instead of simple date equality checks in order to correctly detect all possible overlap scenarios.

This function compares complete timestamp intervals by combining the booking date with start and end time. It does not only compare equal dates.

The overlap rule is:

(new_start < existing_end) AND (new_end > existing_start)

This condition detects all possible overlap cases:

• the new interval starts inside an existing interval
• the new interval ends inside an existing interval
• the new interval completely contains an existing interval
• the existing interval completely contains the new interval
• both intervals are exactly the same
• intervals that only touch at the boundary are not treated as overlapping

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 selected date and time interval.

This implementation uses complete timestamp interval comparison instead of simple date equality checks in order to correctly detect all possible overlap scenarios.

The function combines booking dates and times into complete timestamp intervals in order to correctly validate overlapping reservations.

=== SQL Code ===

{{{
#!sql
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 band is available for a selected date and time interval.

This implementation uses complete timestamp interval comparison instead of simple date equality checks in order to correctly detect all possible overlap scenarios.

The function validates overlapping booking intervals using complete timestamp comparison between requested and existing reservations.

=== SQL Code ===

{{{
#!sql
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 ===

Returns RSVP statistics for a selected event.

The procedure generates summarized RSVP information including accepted, declined, and pending responses for analytical and reporting purposes.

=== SQL Code ===

{{{
#!sql
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;
$$;
}}}
}}}
}}}