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

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;

2. is_venue_available()

Description

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

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
    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;

3. generate_rsvp_summary()

Description

Returns RSVP statistics for a selected event.

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'
    );
END;
$$;