ВТОРОЕ ЗАДАНИЕ

#1 Function that returns all records based on a pattern (example of pattern: part of name, surname, phone number):

CREATE OR REPLACE FUNCTION search_pattern(pattern VARCHAR)
RETURNS TABLE (
    user_id INT,
    first_name VARCHAR,
    last_name VARCHAR,
    phone_number VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM phonebook
    WHERE first_name ILIKE '%' || pattern || '%'
       OR last_name ILIKE '%' || pattern || '%'
       OR phone_number ILIKE '%' || pattern || '%';
END;
$$ LANGUAGE plpgsql;

"""

Этот код создает или заменяет функцию PostgreSQL с именем search_pattern с параметром pattern типа VARCHAR. Функция возвращает таблицу с полями user_id, first_name, last_name и phone_number.

Структура функции:

CREATE OR REPLACE FUNCTION search_pattern(pattern VARCHAR): Создает или заменяет функцию с именем search_pattern и одним параметром pattern типа VARCHAR.
RETURNS TABLE (user_id INT, first_name VARCHAR, last_name VARCHAR, phone_number VARCHAR): Определяет структуру возвращаемой таблицы, которая состоит из полей user_id, first_name, last_name и phone_number.
AS $$: Начало тела функции.
BEGIN: Начало блока кода функции.
RETURN QUERY: Возвращает результат SQL-запроса.
SELECT * FROM phonebook WHERE first_name ILIKE '%' || pattern || '%' OR last_name ILIKE '%' || pattern || '%' OR phone_number ILIKE '%' || pattern || '%': Выполняет SQL-запрос, который выбирает все записи из таблицы phonebook, у которых значение first_name, last_name или phone_number содержит переданный pattern. Используется оператор ILIKE для регистронезависимого поиска с использованием шаблонов.
END;: Окончание блока кода функции.
$$ LANGUAGE plpgsql;: Окончание тела функции и указание языка функции (plpgsql).


ПРИМЕР ИСПОЛЬЗОВАНИЯ:

SELECT * FROM search_pattern('your_pattern');

"""

#2 Create procedure to insert new user by name and phone, update phone if user already exists:

CREATE OR REPLACE PROCEDURE insert_or_update_user(first_name VARCHAR, last_name VARCHAR, phone_number VARCHAR)
AS $$
DECLARE
    existing_user_id INT;
BEGIN
    SELECT user_id INTO existing_user_id FROM phonebook WHERE first_name = first_name AND last_name = last_name;

    IF existing_user_id IS NULL THEN
        INSERT INTO phonebook (first_name, last_name, phone_number) VALUES (first_name, last_name, phone_number);
    ELSE
        UPDATE phonebook SET phone_number = phone_number WHERE user_id = existing_user_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

"""

Этот код создает или заменяет процедуру PostgreSQL с именем insert_or_update_user с параметрами first_name, last_name, и phone_number типа VARCHAR. Процедура вставляет нового пользователя в таблицу phonebook или обновляет существующего пользователя, если он уже существует.

Структура процедуры:

CREATE OR REPLACE PROCEDURE insert_or_update_user(first_name VARCHAR, last_name VARCHAR, phone_number VARCHAR): Создает или заменяет процедуру с именем insert_or_update_user и тремя параметрами first_name, last_name, и phone_number типа VARCHAR.
AS $$: Начало тела процедуры.
DECLARE: Определяет начало блока объявления переменных.
existing_user_id INT;: Объявляет переменную existing_user_id типа INT.
BEGIN: Начало блока кода процедуры.
SELECT user_id INTO existing_user_id FROM phonebook WHERE first_name = first_name AND last_name = last_name;: Выполняет SQL-запрос, который выбирает значение user_id из таблицы phonebook для записи с переданными значениями first_name и last_name. Значение user_id сохраняется в переменную existing_user_id.
IF existing_user_id IS NULL THEN: Если переменная existing_user_id равна NULL (т.е. пользователь с переданными first_name и last_name не найден), то:
a. INSERT INTO phonebook (first_name, last_name, phone_number) VALUES (first_name, last_name, phone_number);: Вставляет нового пользователя с переданными значениями first_name, last_name, и phone_number в таблицу phonebook.
ELSE: В противном случае (если пользователь уже существует):
a. UPDATE phonebook SET phone_number = phone_number WHERE user_id = existing_user_id;: Обновляет значение phone_number для существующего пользователя с найденным значением user_id в таблице phonebook.
END IF;: Окончание блока условного оператора IF.
END;: Окончание блока кода процедуры.
$$ LANGUAGE plpgsql;: Окончание тела процедуры и указание языка процедуры (plpgsql).

ПРИМЕР ИСПОЛЬЗОВАНИЯ:

CALL insert_or_update_user('first_name_value', 'last_name_value', 'phone_number_value');

"""

#3 Create procedure to insert many new users by list of name and phone. Use loop and if statement in stored procedure. Check correctness of phone in procedure and return all incorrect data:


CREATE OR REPLACE FUNCTION validate_phone_number(p_phone_number VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN (p_phone_number ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE PROCEDURE insert_users_with_validation(users_data JSONB)
AS $$
DECLARE
    user_data JSONB;
    incorrect_data JSONB[] DEFAULT '{}';
BEGIN
    FOR user_data IN SELECT * FROM jsonb_array_elements(users_data)
    LOOP
        IF validate_phone_number(user_data->>'phone_number') THEN
            CALL insert_or_update_user(user_data->>'first_name', user_data->>'last_name', user_data->>'phone_number');
        ELSE
            incorrect_data := incorrect_data || user_data;
        END IF;
    END LOOP;

    RAISE NOTICE 'Incorrect data: %', incorrect_data;
END;
$$ LANGUAGE plpgsql;

"""

Этот код создает две функции PostgreSQL: validate_phone_number и insert_users_with_validation. Функция validate_phone_number проверяет, является ли переданный номер телефона корректным, в то время как процедура insert_users_with_validation выполняет вставку пользователей в таблицу phonebook, предварительно проверив корректность номера телефона. Если номер телефона некорректный, он добавляется в массив incorrect_data.

validate_phone_number:
a. Создает функцию с именем validate_phone_number и одним параметром p_phone_number типа VARCHAR.
b. Функция возвращает BOOLEAN значение.
c. Внутри функции используется регулярное выражение для проверки корректности номера телефона.
insert_users_with_validation:
a. Создает процедуру с именем insert_users_with_validation и одним параметром users_data типа JSONB.
b. Внутри процедуры объявляются переменные user_data типа JSONB и incorrect_data типа JSONB[].
c. Процедура выполняет цикл для каждого элемента массива users_data.
d. В каждом шаге цикла проверяется корректность номера телефона с использованием функции validate_phone_number.
e. Если номер телефона корректный, вызывается процедура insert_or_update_user для вставки или обновления пользователя в таблице phonebook.
f. Если номер телефона некорректный, пользователь добавляется в массив incorrect_data.
g. После завершения цикла, выводится уведомление с содержимым массива incorrect_data.

ПРИМЕР ИСПОЛЬЗОВАНИЯ:

CALL insert_users_with_validation('[{"first_name": "John", "last_name": "Doe", "phone_number": "123-456-7890"}, ...]');


"""

#4 Create function to querying data from the tables with pagination (by limit and offset):

CREATE OR REPLACE FUNCTION query_data_with_pagination(limit_val INT, offset_val INT)
RETURNS TABLE (
    user_id INT,
    first_name VARCHAR,
    last_name VARCHAR,
    phone_number VARCHAR
) AS $$
BEGIN
    RETURN QUERY
    SELECT * FROM phonebook
    ORDER BY user_id
    LIMIT limit_val
    OFFSET offset_val;
END;
$$ LANGUAGE plpgsql;

"""

Этот код создаёт функцию PostgreSQL с именем query_data_with_pagination, которая выполняет выборку данных из таблицы phonebook с использованием пагинации. Функция принимает два параметра: limit_val и offset_val, представляющие собой значения ограничения и смещения для пагинации соответственно.

Структура функции:

CREATE OR REPLACE FUNCTION query_data_with_pagination(limit_val INT, offset_val INT): Создаёт или заменяет функцию с именем query_data_with_pagination и двумя параметрами: limit_val типа INT и offset_val типа INT.
RETURNS TABLE: Указывает, что функция будет возвращать таблицу с определёнными полями.
user_id INT, first_name VARCHAR, last_name VARCHAR, phone_number VARCHAR: Определяет столбцы возвращаемой таблицы и их типы.
AS $$: Начало тела функции.
BEGIN: Начало блока кода функции.
RETURN QUERY: Указывает, что функция будет возвращать результат выполнения SQL-запроса.
SELECT * FROM phonebook: Выбирает все записи из таблицы phonebook.
ORDER BY user_id: Сортирует результат по столбцу user_id.
LIMIT limit_val: Ограничивает количество возвращаемых строк значением limit_val.
OFFSET offset_val;: Пропускает первые offset_val строк результата.
END;: Окончание блока кода функции.
$$ LANGUAGE plpgsql;: Окончание тела функции и указание языка функции (plpgsql).

ПРИМЕР ИСПОЛЬЗОВАНИЯ:

SELECT * FROM query_data_with_pagination(10, 0);

"""
#5 Implement procedure to deleting data from tables by username or phone:

CREATE OR REPLACE PROCEDURE delete_data_by_username_or_phone(first_name VARCHAR DEFAULT NULL, phone_number VARCHAR DEFAULT NULL)
AS $$
BEGIN
    DELETE FROM phonebook
    WHERE (first_name IS NOT NULL AND first_name = first_name)
       OR (phone_number IS NOT NULL AND phone_number = phone_number);
END;
$$ LANGUAGE plpgsql;

"""

Этот код создает процедуру PostgreSQL с именем delete_data_by_username_or_phone, которая удаляет записи из таблицы phonebook по имени пользователя или номеру телефона. Процедура принимает два параметра: first_name и phone_number, которые имеют значение по умолчанию NULL.

Структура процедуры:

CREATE OR REPLACE PROCEDURE delete_data_by_username_or_phone(first_name VARCHAR DEFAULT NULL, phone_number VARCHAR DEFAULT NULL): Создает или заменяет процедуру с именем delete_data_by_username_or_phone и двумя параметрами: first_name типа VARCHAR с значением по умолчанию NULL и phone_number типа VARCHAR с значением по умолчанию NULL.

AS $$: Начало тела процедуры.
BEGIN: Начало блока кода процедуры.
DELETE FROM phonebook: Указывает, что процедура будет удалять записи из таблицы phonebook.
WHERE (first_name IS NOT NULL AND first_name = first_name) OR (phone_number IS NOT NULL AND phone_number = phone_number);: Условие, которое определяет, какие записи будут удалены. Если передано значение first_name, удаляются все записи с таким именем. Если передано значение phone_number, удаляются все записи с таким номером телефона.
END;: Окончание блока кода процедуры.
$$ LANGUAGE plpgsql;: Окончание тела процедуры и указание языка процедуры (plpgsql).
Для вызова этой процедуры в SQL-запросе, используйте следующий синтаксис:

ПРИМЕР ИСПОЛЬЗОВАНИЯ:

CALL delete_data_by_username_or_phone('John', NULL);

"""