47 lines
1.4 KiB
PL/PgSQL
47 lines
1.4 KiB
PL/PgSQL
alter table "medreport"."accounts_memberships" add column "has_seen_confirmation" boolean not null default false;
|
|
|
|
set check_function_bodies = off;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_unseen_membership_confirmation(p_user_id uuid DEFAULT auth.uid())
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SECURITY DEFINER
|
|
SET search_path TO 'medreport', 'extensions'
|
|
AS $function$
|
|
select exists (
|
|
select 1
|
|
from medreport.accounts_memberships am
|
|
where am.user_id = p_user_id
|
|
and am.has_seen_confirmation = false
|
|
);
|
|
$function$
|
|
;
|
|
|
|
grant execute on function medreport.has_unseen_membership_confirmation(uuid)
|
|
to authenticated, anon;
|
|
|
|
drop function if exists "medreport"."has_personal_code"(account_id uuid);
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_consent_personal_data(account_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = ''
|
|
AS $function$BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1
|
|
FROM medreport.accounts
|
|
WHERE id = account_id
|
|
AND has_consent_personal_data IS TRUE
|
|
);
|
|
END;$function$
|
|
;
|
|
|
|
grant execute on function medreport.has_consent_personal_data(uuid)
|
|
to authenticated, anon;
|
|
|
|
-- we allow the authenticated role to execute functions in the medreport schema
|
|
grant usage on schema medreport to authenticated;
|
|
|
|
-- we allow the service_role role to execute functions in the medreport schema
|
|
grant usage on schema medreport to service_role; |