45 lines
1.0 KiB
PL/PgSQL
45 lines
1.0 KiB
PL/PgSQL
grant
|
|
execute on function medreport.accept_invitation (text, uuid) to service_role;
|
|
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.accept_invitation(token text, user_id uuid)
|
|
RETURNS uuid
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
target_account_id uuid;
|
|
target_role varchar(50);
|
|
begin
|
|
select
|
|
account_id,
|
|
role into target_account_id,
|
|
target_role
|
|
from
|
|
medreport.invitations
|
|
where
|
|
invite_token = token
|
|
and expires_at > now();
|
|
|
|
if not found then
|
|
raise exception 'Invalid or expired invitation token';
|
|
end if;
|
|
|
|
insert into medreport.accounts_memberships(
|
|
user_id,
|
|
account_id,
|
|
account_role)
|
|
values (
|
|
accept_invitation.user_id,
|
|
target_account_id,
|
|
target_role);
|
|
|
|
delete from medreport.invitations
|
|
where invite_token = token;
|
|
|
|
update medreport.notifications
|
|
set dismissed = true
|
|
where link ilike '%' || token || '%';
|
|
|
|
return target_account_id;
|
|
end;$function$
|
|
; |