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
|
CREATE FUNCTION _api.user_insert()
RETURNS TRIGGER
LANGUAGE plpgsql VOLATILE
AS $BODY$
DECLARE
_length INTEGER;
BEGIN
PERFORM _api.validate_text(
_text => NEW.username,
_column => 'username',
_min => 1,
_max => 24
);
PERFORM TRUE FROM admin.user
WHERE username = NEW.username;
IF FOUND THEN
PERFORM _api.raise_unique('username');
END IF;
PERFORM _api.validate_text(
_text => NEW.password,
_column => 'password',
_max => 256
);
PERFORM _api.validate_text(
_text => NEW.first_name,
_nullable => TRUE,
_column => 'first_name',
_max => 256
);
NEW.first_name = COALESCE(NEW.first_name, ''::text);
PERFORM _api.validate_text(
_text => NEW.last_name,
_nullable => TRUE,
_column => 'last_name',
_max => 256
);
NEW.last_name = COALESCE(NEW.last_name, ''::text);
PERFORM _api.validate_text(
_text => NEW.middle_name,
_nullable => TRUE,
_column => 'middle_name',
_max => 256
);
NEW.middle_name = COALESCE(NEW.middle_name, ''::text);
PERFORM _api.validate_text(
_text => NEW.email,
_column => 'email',
_max => 256
);
PERFORM _api.validate_text(
_text => NEW.gender,
_column => 'gender',
_max => 256
);
IF NEW.birth_date IS NULL THEN
PERFORM _api.raise_null('birth_date');
END IF;
PERFORM _api.validate_text(
_text => NEW.profile_bio,
_nullable => TRUE,
_column => 'profile_bio',
_max => 2048
);
NEW.profile_bio = COALESCE(NEW.profile_bio, ''::text);
INSERT INTO admin.user (
username,
password,
first_name,
last_name,
middle_name,
email,
gender,
birth_date,
profile_avatar,
profile_banner,
profile_bio
) VALUES (
NEW.username,
NEW.password,
NEW.first_name,
NEW.last_name,
NEW.middle_name,
NEW.email,
NEW.gender,
NEW.birth_date,
NEW.profile_avatar,
NEW.profile_banner,
NEW.profile_bio
);
NEW.password := NULL;
RETURN NEW;
END
$BODY$;
GRANT EXECUTE ON FUNCTION _api.user_insert()
TO rest_anon, rest_user;
GRANT INSERT ON TABLE api.user
TO rest_anon, rest_user;
GRANT INSERT ON TABLE admin.user
TO rest_anon, rest_user;
GRANT UPDATE ON TABLE sys.user_id_seq
TO rest_anon, rest_user;
CREATE TRIGGER api_user_insert_trgr
INSTEAD OF INSERT
ON api.user
FOR EACH ROW
EXECUTE PROCEDURE _api.user_insert();
|
