Kirubel-Kibru-Yaltopia
89440985f1
Some checks failed
Deploy to Cloudflare Workers / deploy (push) Has been cancelled
52 lines
1.6 KiB
PL/PgSQL
52 lines
1.6 KiB
PL/PgSQL
-- Run in Supabase SQL Editor if signup returns 500 / "Database error saving new user"
|
|
-- (Same as migration 20250524000008_fix_signup_trigger.sql)
|
|
|
|
DO $$ BEGIN
|
|
CREATE TYPE portal_role AS ENUM ('league_master', 'manager');
|
|
EXCEPTION
|
|
WHEN duplicate_object THEN NULL;
|
|
END $$;
|
|
|
|
ALTER TABLE public.profiles
|
|
ADD COLUMN IF NOT EXISTS portal_role portal_role NOT NULL DEFAULT 'manager';
|
|
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path = public
|
|
AS $$
|
|
DECLARE
|
|
v_role portal_role := 'manager';
|
|
v_name TEXT;
|
|
BEGIN
|
|
v_name := COALESCE(
|
|
NULLIF(trim(NEW.raw_user_meta_data->>'display_name'), ''),
|
|
split_part(COALESCE(NEW.email, 'user'), '@', 1)
|
|
);
|
|
|
|
IF NEW.raw_user_meta_data IS NOT NULL
|
|
AND (NEW.raw_user_meta_data->>'portal_role') IN ('league_master', 'manager') THEN
|
|
v_role := (NEW.raw_user_meta_data->>'portal_role')::portal_role;
|
|
END IF;
|
|
|
|
INSERT INTO public.profiles (id, display_name, portal_role)
|
|
VALUES (NEW.id, v_name, v_role)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
display_name = COALESCE(EXCLUDED.display_name, profiles.display_name),
|
|
portal_role = COALESCE(EXCLUDED.portal_role, profiles.portal_role);
|
|
|
|
RETURN NEW;
|
|
EXCEPTION
|
|
WHEN undefined_column THEN
|
|
INSERT INTO public.profiles (id, display_name)
|
|
VALUES (NEW.id, v_name)
|
|
ON CONFLICT (id) DO UPDATE SET
|
|
display_name = COALESCE(EXCLUDED.display_name, profiles.display_name);
|
|
RETURN NEW;
|
|
WHEN OTHERS THEN
|
|
RAISE WARNING 'handle_new_user failed for user %: %', NEW.id, SQLERRM;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|