mirror of
https://github.com/renee-png/acmcc.git
synced 2026-06-21 01:40:01 +00:00
20 lines
693 B
PL/PgSQL
20 lines
693 B
PL/PgSQL
CREATE OR REPLACE FUNCTION public.get_message_staff_profiles(_current_user_id uuid DEFAULT auth.uid())
|
|
RETURNS TABLE(user_id uuid, full_name text, avatar_url text)
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
SELECT DISTINCT
|
|
ur.user_id,
|
|
p.full_name,
|
|
p.avatar_url
|
|
FROM public.user_roles ur
|
|
LEFT JOIN public.profiles p ON p.user_id = ur.user_id
|
|
WHERE ur.role IN ('admin'::public.app_role, 'manager'::public.app_role)
|
|
AND ur.user_id IS NOT NULL
|
|
AND (_current_user_id IS NULL OR ur.user_id <> _current_user_id)
|
|
ORDER BY p.full_name NULLS LAST, ur.user_id;
|
|
$$;
|
|
|
|
GRANT EXECUTE ON FUNCTION public.get_message_staff_profiles(uuid) TO authenticated; |