Changes between Version 1 and Version 2 of Stored Functions and Procedures


Ignore:
Timestamp:
05/12/26 01:57:29 (2 weeks ago)
Author:
193284
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Stored Functions and Procedures

    v1 v2  
    1111Calculates the total cost of a wedding using venue, photographer, and band bookings.
    1212
    13 === SQL Code ===
    14 
    15 {{{
    16 CREATE OR REPLACE FUNCTION calculate_wedding_total_cost(p_wedding_id INTEGER)
    17 RETURNS NUMERIC AS $$
    18 DECLARE
    19     total_cost NUMERIC;
    20 BEGIN
    21     SELECT
    22         COALESCE(SUM(vb.price), 0)
    23     INTO total_cost
    24     FROM wedding w
    25     LEFT JOIN venue_booking vb ON w.wedding_id = vb.wedding_id
    26     WHERE w.wedding_id = p_wedding_id;
    27 
    28     RETURN total_cost;
    29 END;
    30 $$ LANGUAGE plpgsql;
    31 }}}
    32 
    3313== 2. is_venue_available() ==
    3414
    … …  
    3717Checks whether a venue is available for a selected date and time interval.
    3818
    39 === SQL Code ===
     19== 3. is_photographer_available() ==
    4020
    41 {{{
    42 CREATE OR REPLACE FUNCTION is_venue_available(
    43     p_venue_id INTEGER,
    44     p_date DATE,
    45     p_start TIME,
    46     p_end TIME
    47 )
    48 RETURNS BOOLEAN AS $$
    49 BEGIN
    50     RETURN NOT EXISTS (
    51         SELECT 1
    52         FROM venue_booking
    53         WHERE venue_id = p_venue_id
    54           AND "date" = p_date
    55           AND (
    56                 p_start < end_time
    57             AND p_end > start_time
    58           )
    59     );
    60 END;
    61 $$ LANGUAGE plpgsql;
    62 }}}
     21=== Description ===
    6322
    64 == 3. generate_rsvp_summary() ==
     23Checks whether a photographer is available for a selected date and time interval.
     24
     25== 4. generate_rsvp_summary() ==
    6526
    6627=== Description ===
    … …  
    7031=== SQL Code ===
    7132
    72 {{{
    73 CREATE OR REPLACE PROCEDURE generate_rsvp_summary(p_event_id INTEGER)
    74 LANGUAGE plpgsql
    75 AS $$
    76 BEGIN
    77     RAISE NOTICE 'Accepted: %',
    78     (
    79         SELECT COUNT(*)
    80         FROM event_rsvp
    81         WHERE event_id = p_event_id
    82           AND status = 'accepted'
    83     );
    84 END;
    85 $$;
    86 }}}
     33{{collapse(View SQL Code)
     34<syntaxhighlight lang="sql">
     35PASTE SQL FROM advanced_functions.sql
     36</syntaxhighlight>
     37}}