blob: ee8b21e30c570637eaf710e6a9b8b8a2f5f1d210 (
plain)
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
|
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;
|
