create or replace function anon_insert_copy_groups_to_anon_user()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS $$
declare
anon_user_id integer = new.user_id;
master_user_id integer = new.master_user_id;
active boolean = new.active;
created_at timestamp = new.created_at;
updated_at timestamp = new.updated_at;
BEGIN
insert into group_users
(group_id, user_id, created_at, updated_at, owner, notification_level, first_unread_pm_at)
select group_users.group_id, anon_user_id , current_timestamp, current_timestamp, false, 3 as notification_level, current_timestamp
from group_users
where group_users.user_id=master_user_id
and group_users.group_id not in (select group_id from group_users where group_users.user_id=anon_user_id)
and group_users.group_id not in (1, 2, 3); -- 管理者、モデレーター、スタッフを除く!
return new;
end;
$$;
-- トリガーを作成する
CREATE TRIGGER anon_insert_trigger
AFTER INSERT ON anonymous_users
FOR EACH ROW
EXECUTE FUNCTION anon_insert_copy_groups_to_anon_user();