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 cost of a wedding using venue, photographer, and band bookings.

SQL Code

<pre> CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER) RETURNS NUMERIC AS $$ DECLARE

total_cost NUMERIC;

BEGIN

SELECT

COALESCE(SUM(vb.price), 0)

INTO total_cost FROM wedding w LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id WHERE w.wedding_id = p_wedding_id;

RETURN total_cost;

END; $$ LANGUAGE plpgsql; </pre>

2. is_venue_available()

Description

Checks whether a venue is available for a selected date and time interval.

SQL Code

<pre> CREATE OR REPLACE FUNCTION is_venue_available(

p_venue_id INTEGER, p_date DATE, p_start TIME, p_end TIME

) RETURNS BOOLEAN AS $$ BEGIN

RETURN NOT EXISTS (

SELECT 1 FROM venue_booking WHERE venue_id = p_venue_id

AND "date" = p_date AND (

p_start < end_time

AND p_end > start_time

)

);

END; $$ LANGUAGE plpgsql; </pre>

3. is_photographer_available()

Description

Checks whether a photographer is available for a selected date and time interval.

SQL Code

<pre> CREATE OR REPLACE FUNCTION is_photographer_available(

p_photographer_id INTEGER, p_date DATE, p_start TIME, p_end TIME

) RETURNS BOOLEAN AS $$ BEGIN

RETURN NOT EXISTS (

SELECT 1 FROM photographer_booking WHERE photographer_id = p_photographer_id

AND "date" = p_date AND (

p_start < end_time

AND p_end > start_time

)

);

END; $$ LANGUAGE plpgsql; </pre>

4. is_band_available()

Description

Checks whether a band is available for a selected date and time interval.

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.

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.end_time)
            AND (p_date + p_end)   > (bb."date" + bb.start_time)
          )
    );
END;
$$ LANGUAGE plpgsql;

5. generate_rsvp_summary()

Description

Returns RSVP statistics for a selected event.

SQL Code

<pre> 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'

);

END; $$; </pre>