---

create table clients (id serial primary key,login varchar(50),pass varchar(50),email varchar(50),last_enter timestamp,login_error varchar(50),error_date timestamp);
insert into clients (id, login, pass, email, last_enter, login_error, error_date) values (26, '41is', '3609c0105b8b9640fbb58939b92ed9da', '41is', '2023-09-05 14:14:05', null, null), (28, 'test04', 'dff092fd096eb595ad5f4c4fbfb8b8f2f', 'test04@mail.ru', '2024-02-04 22:12:46', 'autarization error', '2024-02-04 22:33:12'), (29, 'test05', '11c9efa639a13c650ccf081500383', 'test05@mail.ru', '2024-02-04 22:34:06', null, null);
__________________________________________________________________________________
create or replace procedure sp_auth(p_login varchar(50), p_password varchar(50), p_email varchar(50)) language plpgsql as $$ declare v_client_id int; v_correct_password varchar(50); begin select id, pass into v_client_id, v_correct_password from clients where login = p_login; if v_client_id is null then insert into clients (login, pass, email, last_enter, login_error, error_date)  values (p_login, v_input_hash, p_email, current_timestamp, null, null); raise notice 'Регистрация успешна для пользователя: %', p_login; elseif v_correct_password = p_password then update clients set last_enter = current_timestamp, login_error = null, error_date = null where id = v_client_id; raise notice 'Авторизация успешна для пользователя: %', p_login; else update clients set login_error = 'Autarization error', error_date = current_timestamp where id = v_client_id; raise exception 'Ошибка: неверный пароль для пользователя: %', p_login;; end if; end $$;
-- Вызов процедуры авторизации
CALL sp_auth_or_register_client('test09', 'my_pass', 'test09@mail.ru');
_________________________________________________________________________________________
create or replace procedure sp_change ( p_old_last_name varchar(50), p_first_name varchar(50), p_new_last_name varchar(50)) language plpgsql as $$ declare v_user_exists boolean; begin select (select 1 from clients where last_name = p_old_last_name and first_name = p_first_name) into v_user_exists; if v_user_exists then update clients set last_name = p_new_lasrt_name where last_name = p_old_last_name and first_name = p_first_name; raise notice 'Фамилия успешно изменена с % на % для пользователя %', p_old_last_name, p_new_last_name, p_first_name; raise exception 'Пользователь с параметрами (% %) не найден', p_old_last_name, p_first_name; end if; end $$;
-- Пример успешного вызова
CALL sp_change_last_name('Иванова', 'Анна', 'Янова');
-- Пример вызова, если пользователя нет в базе данных
CALL sp_change_last_name('Петрова', 'Ольга', 'Сидорова');
________________________________________________________________________________________