initial commit

1 files changed, 44 insertions, 0 deletions

diff --git a/sql/functions/auth.insert_user_role_group.sql b/sql/functions/auth.insert_user_role_group.sql
new file mode 100644
index 0000000..ee8b21e
--- /dev/null
+++ b/sql/functions/auth.insert_user_role_group.sql
@@ -0,0 +1,44 @@
+drop function auth.insert_user_role_group (
+    p_user_id bigint,
+    p_role_group_name character varying
+);
+
+create or replace function auth.insert_user_role_group (
+    p_user_id bigint,
+    p_role_group_name character varying
+)
+returns bigint
+as $$
+declare
+  l_role_group_id bigint;
+  l_user_role_group_id bigint;
+begin
+    select rg.id into l_role_group_id
+    from auth.role_groups rg
+    where rg.name = p_role_group_name;
+
+    if(l_role_group_id is not null) then
+        select urg.id into l_user_role_group_id
+        from auth.users_role_groups urg
+        where urg.user_id = p_user_id
+            and urg.role_group_id = l_role_group_id;
+
+        if(l_user_role_group_id is null) then
+            insert into auth.users_role_groups (
+                user_id,
+                role_group_id
+            ) values (
+                p_user_id,
+                l_role_group_id
+            );
+
+            select urg.id into l_user_role_group_id
+            from auth.users_role_groups urg
+            where urg.user_id = p_user_id
+                and urg.role_group_id = l_role_group_id;
+        end if;
+    end if;
+
+    return l_user_role_group_id;
+end;
+$$ language plpgsql;