SQLize Online / PHPize Online  /  SQLtest Online

create table usr ( id int8 not null, activation_code varchar(255), active boolean not null, email varchar(255), password varchar(255) not null, username varchar(255) not null, primary key (id) ); CREATE OR REPLACE FUNCTION usr_before_insert_or_update() RETURNS TRIGGER AS $$ DECLARE existing_user RECORD; BEGIN -- Приводим email к нижнему регистру, если он указан IF NEW.email IS NOT NULL THEN NEW.email := lower(NEW.email); END IF; -- Проверка на дубликат username SELECT INTO existing_user * FROM usr WHERE username = NEW.username; IF FOUND THEN RAISE EXCEPTION 'Username already exists.'; END IF; -- Устанавливаем active в TRUE по умолчанию, но разрешаем обновление IF TG_OP = 'INSERT' AND NEW.active IS NULL THEN NEW.active := TRUE; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER usr_before_insert_or_update_trigger BEFORE INSERT OR UPDATE ON usr FOR EACH ROW EXECUTE PROCEDURE usr_before_insert_or_update();