FunctionsProceduresTriggers: 03_functions.sql

File 03_functions.sql, 3.6 KB (added by 231184, 6 days ago)

03_functions

Line 
1-- =====================================================
2-- 1. Get appointments for one customer
3-- =====================================================
4
5CREATE OR REPLACE FUNCTION get_customer_appointments(
6 p_customer_id INT
7)
8 RETURNS TABLE (
9 appointment_id INT,
10 status TEXT,
11 created_at TIMESTAMP,
12 customer_id INT,
13 customer_name TEXT,
14 business_id INT,
15 business_name TEXT,
16 service_id INT,
17 service_name TEXT,
18 employee_id INT,
19 employee_name TEXT,
20 appointment_date DATE,
21 start_time TIME,
22 end_time TIME,
23 city TEXT
24 )
25 LANGUAGE plpgsql
26AS $$
27BEGIN
28 RETURN QUERY
29 SELECT
30 cav.appointment_id,
31 cav.status::TEXT,
32 cav.created_at,
33 cav.customer_id,
34 cav.customer_name::TEXT,
35 cav.business_id,
36 cav.business_name::TEXT,
37 cav.service_id,
38 cav.service_name::TEXT,
39 cav.employee_id,
40 cav.employee_name::TEXT,
41 cav.date,
42 cav.start_time,
43 cav.end_time,
44 cav.city::TEXT
45 FROM customer_appointments_view cav
46 WHERE cav.customer_id = p_customer_id;
47END;
48$$;
49
50
51
52-- =====================================================
53-- 2. Get available slots for business and date
54-- =====================================================
55
56CREATE OR REPLACE FUNCTION get_available_slots(
57 p_business_id INT,
58 p_date_from DATE,
59 p_date_to DATE
60)
61 RETURNS TABLE (
62 slot_id INT,
63 slot_date DATE,
64 start_time TIME,
65 end_time TIME,
66 business_id INT,
67 business_name TEXT,
68 employee_id INT,
69 employee_name TEXT,
70 service_id INT,
71 service_name TEXT,
72 price NUMERIC,
73 duration_minutes INT
74 )
75 LANGUAGE plpgsql
76AS $$
77BEGIN
78 RETURN QUERY
79 SELECT
80 av.slot_id,
81 av.date,
82 av.start_time,
83 av.end_time,
84 av.business_id,
85 av.business_name::TEXT,
86 av.employee_id,
87 av.employee_name::TEXT,
88 av.service_id,
89 av.service_name::TEXT,
90 av.price,
91 av.duration_minutes
92 FROM available_slots av
93 WHERE av.business_id = p_business_id
94 AND av.date >= p_date_from
95 AND av.date <= p_date_to;
96END;
97$$;
98
99
100
101-- =====================================================
102-- 3. Get review summary for one business
103-- =====================================================
104
105CREATE OR REPLACE FUNCTION get_business_review_summary(
106 p_business_id INT
107)
108 RETURNS TABLE (
109 business_id INT,
110 business_name TEXT,
111 avg_rating NUMERIC,
112 total_reviews BIGINT
113 )
114 LANGUAGE plpgsql
115AS $$
116BEGIN
117 RETURN QUERY
118 SELECT
119 rs.business_id,
120 rs.business_name::TEXT,
121 rs.avg_rating,
122 rs.total_reviews
123 FROM review_summary rs
124 WHERE rs.business_id = p_business_id;
125END;
126$$;