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.