Changes between Version 2 and Version 3 of DatabaseProgramming-AdvDb

Timestamp:

05/09/26 18:03:19 (2 weeks ago)

Author:

231175

Comment:

--

DatabaseProgramming-AdvDb

@@ -3 +3 @@
 == Функции
 
+Функција за креирање на review од страна на корисник за курс.
+
+{{{
+create or replace function write_review(
+    user_id bigint,
+    course_version_id bigint,
+    new_rating int,
+    new_comment text
+)
+    returns review
+as
+$$
+    declare
+        r review;
+        e_id bigint;
+    begin
+        select e.id into e_id
+        from enrollment e
+        where e.user_id = write_review.user_id
+          and e.course_version_id = write_review.course_version_id
+        limit 1;
+
+        if exists(select 1 from enrollment e join review rv on e.id = rv.enrollment_id where e.id = e_id) then
+            return null; -- User has already written a review for this course version
+        else
+            insert into review (rating, comment, date, enrollment_id)
+            values (write_review.new_rating, write_review.new_comment, now(), e_id)
+            returning * into r;
+            return r;
+        end if;
+    end;
+$$ language plpgsql;
+}}}
+
 == Процедури
+
+Процедура за испраќање на мејлови за потсетување за онлајн состанок со експерт.
+
+{{{
+create or replace procedure send_all_emails()
+as
+$$
+    declare
+        email_to_send meeting_email_reminder;
+    begin
+        for email_to_send in
+            select * from meeting_email_reminder where sent = false and scheduled_at <= now()
+        loop
+            perform pg_notify('send_email', email_to_send.id::text);
+
+
+            update meeting_email_reminder set sent = true where id = email_to_send.id;
+        end loop;
+    end;
+$$
+    language plpgsql;
+}}}
 
 == Тригери