Типы данных и их использование в PL/pgSQL: INTEGER, TEXT, BOOLEAN, RECORD

В этой лекции мы рассмотрим, какие типы данных поддерживаются в PL/pgSQL, и научимся эффективно работать с ними. Основное внимание будет уделено четырём типам данных:

• INTEGER для работы с числами.
• TEXT для обработки строк.
• BOOLEAN для работы с логическими значениями.
• RECORD для работы с динамическими структурами данных.

Каждый тип данных мы рассмотрим через примеры, чтобы вы могли видеть их применение на практике.

Поддерживаемые типы данных в PL/pgSQL

PL/pgSQL поддерживает все типы данных, которые знакомы вам из PostgreSQL. От простых числовых (INTEGER, NUMERIC) до более сложных, таких как массивы и JSONB. Давайте пробежимся по основным.

1. Примитивные типы:

   • INTEGER, BIGINT, FLOAT, NUMERIC — числовые типы.
   • TEXT, CHAR, VARCHAR — текстовые.
   • BOOLEAN — логический тип данных.

2. Сложные типы:

   • RECORD — для работы с динамическими наборами данных.
   • ROWTYPE — для работы с типами строк таблицы.
   • Массивы и JSON — будут разбираться позже в курсе.

Работа с типом INTEGER

INTEGER — это один из самых распространённых типов данных. Он используется для хранения целых чисел. В PL/pgSQL этим типом вы можете пользоваться для выполнения расчётов, работы с идентификаторами записей и проверки условий.

Пример: подсчет количества записей

Допустим, у нас есть таблица students, и мы хотим узнать, сколько студентов записано в базе.

DO $$
DECLARE
    total_students INTEGER; -- Переменная для хранения количества студентов
BEGIN
    SELECT COUNT(*) INTO total_students FROM students; -- Сохраняем результат запроса в переменную
    RAISE NOTICE 'Количество студентов: %', total_students; -- Выводим сообщение
END;
$$;

Важные моменты при работе с INTEGER:

• В PL/pgSQL присвоение переменной значения осуществляется через ключевое слово INTO.
• Если вы попытаетесь сохранить в INTEGER дробное значение, получите ошибку. Для таких случаев используйте тип NUMERIC или FLOAT.

Работа с типом TEXT

TEXT используется для хранения строковых данных. Он пригодится, когда нужно работать с именами, описаниями или любыми другими текстами.

Пример: вывод имен студентов

В этом примере мы выведем имена всех студентов из таблицы students.

DO $$
DECLARE
    student_name TEXT; -- Переменная для имени студента
BEGIN
    FOR student_name IN SELECT name FROM students LOOP
        RAISE NOTICE 'Имя студента: %', student_name; -- Выводим каждое имя
    END LOOP;
END;
$$;

Полезные функции для работы с TEXT:

• UPPER() и LOWER() — преобразование к верхнему/нижнему регистру.
• CONCAT() — объединение строк.
• LENGTH() — длина строки.

Например:

DO $$
DECLARE
    full_name TEXT;
BEGIN
    full_name := CONCAT('Алекс', ' Мин'); -- Объединяем строки
    RAISE NOTICE 'Полное имя: %', UPPER(full_name); -- Печатаем имя в верхнем регистре
END;
$$;

Работа с типом BOOLEAN

BOOLEAN отвечает за хранение значений логического типа: TRUE, FALSE и NULL. Этот тип данных особенно полезен при проверке условий и фильтрации данных.

Пример: проверка активности студента

Допустим, у вас есть таблица students с колонкой is_active, которая показывает, активен ли студент.

DO $$
DECLARE
    is_active BOOLEAN; -- Переменная для хранения значения активности
BEGIN
    SELECT is_active INTO is_active FROM students WHERE id = 1; -- Получаем значение из таблицы
    IF is_active THEN
        RAISE NOTICE 'Студент активен!';
    ELSE
        RAISE NOTICE 'Студент НЕ активен.';
    END IF;
END;
$$;

Важные моменты при работе с BOOLEAN:

• Логические значения можно использовать напрямую в условиях IF и WHILE.
• Значение NULL в логике считается "неопределённым", поэтому стоит учитывать это при проверках.

Работа с типом RECORD

RECORD — это мощный тип данных, который используется для хранения строк данных без заранее определённой структуры. Он особенно полезен, когда вы работаете с результатами SQL-запросов, которые возвращают несколько колонок.

Пример: обход всех записей таблицы

В следующем примере мы обходим все записи таблицы students и выводим имя и ID каждого студента.

DO $$
DECLARE
    student RECORD; -- Динамический тип для хранения строки данных
BEGIN
    FOR student IN SELECT id, name FROM students LOOP
        RAISE NOTICE 'ID: %, Имя: %', student.id, student.name; -- Обращаемся к колонкам записи
    END LOOP;
END;
$$;

Важные моменты при работе с RECORD:

• Переменные типа RECORD заполняются только внутри цикла или при использовании запроса SELECT INTO.
• Вызывать колонки можно через record.column_name.

Типы данных ROWTYPE для работы с таблицами

Если вы хотите сохранить запись из таблицы целиком (и при этом хотите чёткую типизацию), можно использовать тип ROWTYPE. Он автоматически наследует структуру строки таблицы.

Пример: работа с типом ROWTYPE

DO $$
DECLARE
    student students%ROWTYPE; -- Переменная со структурой строки таблицы students
BEGIN
    SELECT * INTO student FROM students WHERE id = 1; -- Загружаем данные строки в переменную
    RAISE NOTICE 'Имя студента: %, Курс: %', student.name, student.course;
END;
$$;

Различия между RECORD и ROWTYPE

Практический пример

Давайте напишем функцию, которая возвращает количество активных студентов и их имена.

CREATE FUNCTION active_students_report() RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT id, name FROM students WHERE is_active = TRUE;
END;
$$ LANGUAGE plpgsql;

Вызов функции:

SELECT * FROM active_students_report();

Ошибки при работе с типами данных

Иногда работа с данными приводит к ошибкам. Вот несколько распространённых моментов:

• Ошибка типа: попытка записать строку в переменную INTEGER (например, my_var := 'abc';).
• Использование NULL там, где ожидается TRUE или FALSE.
• Неправильное использование RECORD без инициализации.

Как избежать ошибок:

• Всегда явно задавайте тип переменных.
• Проверяйте типы данных колонок в таблицах перед записью.
• Используйте команды отладки, такие как RAISE NOTICE.

Теперь вы знаете, как работать с типами данных INTEGER, TEXT, BOOLEAN и RECORD в PL/pgSQL. Данное знание позволит вам создавать более сложные и мощные программы на языке процедурного программирования PostgreSQL.