Spaces:
Sleeping
Sleeping
File size: 1,682 Bytes
6fc3143 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
-- 1. Add missing columns if they don't exist
DO $$
BEGIN
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'full_name') THEN
ALTER TABLE public.users ADD COLUMN full_name text;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'avatar_url') THEN
ALTER TABLE public.users ADD COLUMN avatar_url text;
END IF;
IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name = 'users' AND column_name = 'credits') THEN
ALTER TABLE public.users ADD COLUMN credits integer default 5;
END IF;
END $$;
-- 2. Create the Trigger Function (Updated to be safe)
create or replace function public.handle_new_user()
returns trigger as $$
begin
insert into public.users (id, email, full_name, avatar_url, credits)
values (
new.id,
new.email,
new.raw_user_meta_data->>'full_name',
new.raw_user_meta_data->>'avatar_url',
5
)
ON CONFLICT (id) DO UPDATE SET
email = EXCLUDED.email,
full_name = EXCLUDED.full_name,
avatar_url = EXCLUDED.avatar_url;
return new;
end;
$$ language plpgsql security definer;
-- 3. Create the Trigger
drop trigger if exists on_auth_user_created on auth.users;
create trigger on_auth_user_created
after insert on auth.users
for each row execute procedure public.handle_new_user();
-- 4. Backfill existing users
insert into public.users (id, email, full_name, avatar_url, credits)
select
id,
email,
raw_user_meta_data->>'full_name',
raw_user_meta_data->>'avatar_url',
5
from auth.users
where id not in (select id from public.users);
|