1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
|
-- migrations/001_notification_triggers.sql
-- 1. 알림 생성 시 실시간 NOTIFY 함수
CREATE OR REPLACE FUNCTION notify_new_notification()
RETURNS TRIGGER AS $$
BEGIN
-- 새 알림이 생성될 때 NOTIFY 전송
PERFORM pg_notify(
'new_notification',
json_build_object(
'id', NEW.id,
'user_id', NEW.user_id,
'title', NEW.title,
'message', NEW.message,
'type', NEW.type,
'related_record_id', NEW.related_record_id,
'related_record_type', NEW.related_record_type,
'is_read', NEW.is_read,
'created_at', NEW.created_at
)::text
);
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 2. 알림 읽음 상태 변경 시 NOTIFY 함수
CREATE OR REPLACE FUNCTION notify_notification_read()
RETURNS TRIGGER AS $$
BEGIN
-- 읽음 상태가 변경될 때 NOTIFY 전송
IF OLD.is_read != NEW.is_read THEN
PERFORM pg_notify(
'notification_read',
json_build_object(
'id', NEW.id,
'user_id', NEW.user_id,
'is_read', NEW.is_read,
'read_at', NEW.read_at
)::text
);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- 트리거 생성 (알림 테이블에만)
CREATE OR REPLACE TRIGGER trigger_notify_new_notification
AFTER INSERT ON notifications
FOR EACH ROW
EXECUTE FUNCTION notify_new_notification();
CREATE OR REPLACE TRIGGER trigger_notify_notification_read
AFTER UPDATE ON notifications
FOR EACH ROW
EXECUTE FUNCTION notify_notification_read();
|
