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
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
|
CREATE FUNCTION _api.user_update()
RETURNS TRIGGER
LANGUAGE plpgsql VOLATILE
AS $BODY$
DECLARE
_user_id INTEGER;
_changed BOOLEAN;
BEGIN
_user_id = _api.get_user_id();
_changed = FALSE;
IF OLD.id <> _user_id THEN
PERFORM _api.raise_deny();
END IF;
-- username
NEW.username = COALESCE(NEW.username, OLD.username);
NEW.username := _api.trim(NEW.username);
PERFORM _api.validate_text(
_text => NEW.username,
_column => 'username',
_min => 1,
_max => 24
);
IF NEW.username IS DISTINCT FROM OLD.username THEN
PERFORM TRUE FROM xssbook.user
WHERE username = NEW.username;
IF FOUND THEN
PERFORM _api.raise_unique('username');
END IF;
_changed = TRUE;
END IF;
-- first name
NEW.first_name = COALESCE(NEW.first_name, OLD.first_name);
NEW.first_name := _api.trim(NEW.first_name);
PERFORM _api.validate_text(
_text => NEW.first_name,
_column => 'first_name',
_max => 256
);
IF NEW.first_name IS DISTINCT FROM OLD.first_name THEN
_changed = TRUE;
END IF;
-- last name
NEW.last_name = COALESCE(NEW.last_name, OLD.last_name);
NEW.last_name := _api.trim(NEW.last_name);
PERFORM _api.validate_text(
_text => NEW.last_name,
_column => 'last_name',
_max => 256
);
IF NEW.last_name IS DISTINCT FROM OLD.last_name THEN
_changed = TRUE;
END IF;
-- middle name
NEW.middle_name = COALESCE(NEW.middle_name, OLD.middle_name);
NEW.middle_name := _api.trim(NEW.middle_name);
PERFORM _api.validate_text(
_text => NEW.middle_name,
_column => 'middle_name',
_max => 256
);
IF NEW.middle_name IS DISTINCT FROM OLD.middle_name THEN
_changed = TRUE;
END IF;
-- email
NEW.email = COALESCE(NEW.email, OLD.email);
NEW.email := _api.trim(NEW.email);
PERFORM _api.validate_text(
_text => NEW.email,
_column => 'email',
_max => 256
);
IF NEW.email IS DISTINCT FROM OLD.email THEN
_changed = TRUE;
END IF;
-- gender
NEW.gender = COALESCE(NEW.gender, OLD.gender);
NEW.gender := _api.trim(NEW.gender);
PERFORM _api.validate_text(
_text => NEW.gender,
_column => 'gender',
_max => 256
);
IF NEW.gender IS DISTINCT FROM OLD.gender THEN
_changed = TRUE;
END IF;
-- birth date
NEW.birth_date = COALESCE(NEW.birth_date, OLD.birth_date);
IF NEW.birth_date IS DISTINCT FROM OLD.birth_date THEN
_changed = TRUE;
END IF;
-- profile bio
NEW.profile_bio = COALESCE(NEW.profile_bio, OLD.profile_bio);
NEW.profile_bio := _api.trim(NEW.profile_bio);
PERFORM _api.validate_text(
_text => NEW.profile_bio,
_column => 'profile_bio',
_max => 2048
);
IF NEW.profile_bio IS DISTINCT FROM OLD.profile_bio THEN
_changed = TRUE;
END IF;
IF _changed THEN
UPDATE xssbook.user SET
username = NEW.username,
first_name = NEW.first_name,
last_name = NEW.last_name,
middle_name = NEW.middle_name,
email = NEW.email,
gender = NEW.gender,
birth_date = NEW.birth_date,
profile_bio = NEW.profile_bio,
modified = clock_timestamp()
WHERE id = OLD.id;
END IF;
RETURN NEW;
END
$BODY$;
GRANT EXECUTE ON FUNCTION _api.user_update()
TO rest_user;
GRANT UPDATE ON TABLE api.user
TO rest_user;
GRANT UPDATE ON TABLE xssbook.user
TO rest_user;
CREATE TRIGGER api_user_update_trgr
INSTEAD OF UPDATE
ON api.user
FOR EACH ROW
EXECUTE PROCEDURE _api.user_update();
|
