authen
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_set_user_roles
Parameters
Name
Type
Mode
v_user_id
integer
IN
v_prop_id
integer
IN
v_app_code
text
IN
v_group_ids
int4[]
IN
Definition
BEGIN IF v_group_ids IS NULL THEN RETURN; END IF; IF EXISTS ( SELECT 1 FROM unnest(v_group_ids) gid LEFT JOIN public.user_group ug ON ug.id = gid AND ug.app_code = v_app_code AND (ug.prop_id = v_prop_id OR ug.prop_id IS NULL) WHERE ug.id IS NULL ) THEN RAISE EXCEPTION USING ERRCODE = '22023', MESSAGE = 'Invalid role for property'; END IF; UPDATE public.user_roles ur SET active = false WHERE ur.user_id = v_user_id AND ur.prop_id = v_prop_id AND ur.group_id IN ( SELECT ug.id FROM public.user_group ug WHERE ug.app_code = v_app_code AND (ug.prop_id = v_prop_id OR ug.prop_id IS NULL) ); INSERT INTO public.user_roles(user_id, prop_id, group_id, active) SELECT v_user_id, v_prop_id, gid, true FROM unnest(v_group_ids) gid ON CONFLICT ON CONSTRAINT user_roles_pkey DO UPDATE SET active = true; END