summaryrefslogtreecommitdiff
path: root/db/rest/user/api_user_insert.sql
diff options
context:
space:
mode:
Diffstat (limited to 'db/rest/user/api_user_insert.sql')
-rw-r--r--db/rest/user/api_user_insert.sql121
1 files changed, 121 insertions, 0 deletions
diff --git a/db/rest/user/api_user_insert.sql b/db/rest/user/api_user_insert.sql
new file mode 100644
index 0000000..da3ae2d
--- /dev/null
+++ b/db/rest/user/api_user_insert.sql
@@ -0,0 +1,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();