From b8423f4d05c75094b7b9d09c6f0bb353acc9be1c Mon Sep 17 00:00:00 2001
From: ckonstanski <carlos.konstanski@olo.com>
Date: Sat, 25 May 2024 18:03:25 -0600
Subject: initial commit

---
 .../auth.get_all_role_groups_for_user.sql          | 36 ++++++++++++++++++++++
 1 file changed, 36 insertions(+)
 create mode 100644 sql/functions/auth.get_all_role_groups_for_user.sql

(limited to 'sql/functions/auth.get_all_role_groups_for_user.sql')

diff --git a/sql/functions/auth.get_all_role_groups_for_user.sql b/sql/functions/auth.get_all_role_groups_for_user.sql
new file mode 100644
index 0000000..f8c111d
--- /dev/null
+++ b/sql/functions/auth.get_all_role_groups_for_user.sql
@@ -0,0 +1,36 @@
+drop function auth.get_all_role_groups_for_user (
+    p_user_id bigint
+);
+
+create or replace function auth.get_all_role_groups_for_user (
+    p_user_id bigint
+)
+returns setof auth.role_groups_t
+as $$
+begin
+    if auth.superuser_p(p_user_id) = true::bool then
+        return query
+        select null::bigint as user_role_group_id,
+            p_user_id as user_id,
+            id as role_group_id,
+            name,
+            description
+        from auth.role_groups
+        order by name asc;
+    else
+        return query
+        select urg.id as user_role_group_id,
+            urg.user_id,
+            rg.id as role_group_id,
+            rg.name,
+            rg.description
+        from auth.users_role_groups urg
+        inner join auth.role_groups rg
+            on urg.role_group_id = rg.id
+        where urg.user_id = p_user_id
+        order by rg.name asc;
+    end if;
+
+    return;
+end;
+$$ language plpgsql;
-- 
cgit v1.3