48 lines
1.3 KiB
PL/PgSQL
48 lines
1.3 KiB
PL/PgSQL
DROP FUNCTION IF EXISTS medreport.get_account_members(text);
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.get_account_members(account_slug text)
|
|
RETURNS TABLE(
|
|
id uuid,
|
|
user_id uuid,
|
|
account_id uuid,
|
|
role character varying,
|
|
role_hierarchy_level integer,
|
|
primary_owner_user_id uuid,
|
|
name character varying,
|
|
email character varying,
|
|
personal_code text,
|
|
picture_url character varying,
|
|
created_at timestamp with time zone,
|
|
updated_at timestamp with time zone
|
|
)
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return QUERY
|
|
select
|
|
acc.id,
|
|
am.user_id,
|
|
am.account_id,
|
|
am.account_role,
|
|
r.hierarchy_level,
|
|
a.primary_owner_user_id,
|
|
acc.name,
|
|
acc.email,
|
|
acc.personal_code,
|
|
acc.picture_url,
|
|
am.created_at,
|
|
am.updated_at
|
|
from
|
|
medreport.accounts_memberships am
|
|
join medreport.accounts a on a.id = am.account_id
|
|
join medreport.accounts acc on acc.id = am.user_id
|
|
join medreport.roles r on r.name = am.account_role
|
|
where
|
|
a.slug = account_slug;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.get_account_members (text) to authenticated,
|
|
service_role; |