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