Знакомство с PL/pgSQL

PostgreSQL умеет "разговаривать" не только на SQL! Знакомьтесь: PL/pgSQL (Procedural Language/PostgreSQL Structured Query Language), процедурный язык программирования, который встроен в PostgreSQL. Он разработан для упрощения выполнения сложных операций с использованием SQL-запросов и структур управления, таких как условия (IF, CASE) и циклы (LOOP, FOR, WHILE).

PL/pgSQL и SQL — это как автоматизированная сборочная линия и обычный "ручной" инструмент вроде молотка. Как и молоток, SQL — инструмент надёжный. Только вот для каждого удара приходится замахиваться и делать его вручную. Ну а PL/pgSQL — это уже автоматическая сборочная линия: вы один раз задаёте, что и как нужно делать, а дальше всё работает само — выполняет команды, запоминает, проверяет, если что-то пошло не так, и спокойно продолжает дальше. Зачем самому забивать каждый гвоздь, если можно просто наблюдать, как всё делается само? Ну не мечта ли?

Сравнение PL/pgSQL с обычными SQL-запросами

Давайте разберемся, чем PL/pgSQL отличается от простых SQL-запросов.

SQL работает отлично, когда вам нужно выполнить одну конкретную задачу: например, выбрать всех студентов из таблицы или добавить новую строку. Однако, как только вы начинаете сталкиваться с логикой "если это, то то" или с необходимостью повторять те же действия, у SQL начинают возникать проблемы.

Например, представьте, что вам нужно:

1. Проверить, есть ли уже запись о студенте в таблице.
2. Если запись есть — обновить данные.
3. Если записи нет — добавить новую запись.

С помощью обычного SQL вам пришлось бы написать несколько запросов и обрабатывать результат на стороне клиента. С PL/pgSQL всё это можно уместить в одну процедуру, которая выполняется на стороне базы данных.

PL/pgSQL прекрасно себя проявляет в более комплексных проектах. Он позволяет:

• сохранять промежуточные результаты в переменные;
• обрабатывать ошибки, чтобы база данных не "падала" при неожиданностях;
• использовать циклы и условия для выполнения сложных алгоритмов.

Области применения PL/pgSQL

Применение PL/pgSQL очень часто встречается в следующих сценариях:

1. Автоматизация задач. Например, автоматическое создание записей в журнале (log) при каждом обновлении данных.

2. Бизнес-логика. У вас есть правило: каждый студент, у которого больше 5 пропусков, должен автоматически помечаться как "неактивный". Почему бы не поместить это правило в функцию PL/pgSQL?

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

4. Триггеры. PL/pgSQL используется для написания триггеров, которые автоматически выполняют действия при вставке, обновлении или удалении данных.

Пример использования PL/pgSQL

Вот пример функции, которая принимает два числа, складывает их и возвращает результат:

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Объяснение:

• CREATE FUNCTION add_numbers(a INT, b INT) — мы создаем функцию с именем add_numbers, которая принимает два аргумента типа INT (целое число).
• RETURNS INT — указывает, что функция возвращает целое число.
• BEGIN ... END — это основной блок инструкции, где выполняется логика функции.
• RETURN a + b; — результат сложения передается обратно.

Чтобы вызвать эту функцию, нужно использовать обычный SQL-запрос:

SELECT add_numbers(10, 20);

Результат: 30.

Подводные камни и ограничения

PL/pgSQL — мощный инструмент, но с ним нужно быть осторожным:

• Производительность: слишком сложные функции могут замедлить работу базы. Всегда тестируйте и оптимизируйте.
• Тестирование: ошибки в функциях могут быть незаметны, пока вы их не вызовете. Не забудьте про тесты!
• Читаемость: большие функции и процедуры со сложной логикой могут быть трудными для понимания. Старайтесь писать код, который вы сможете понять через месяц.

Теперь, когда вы уже немного знаете, что такое PL/pgSQL, поговорим дальше о его синтаксисе и основных возможностях. Мы научимся создавать блоки кода, объявлять переменные и работать с типами данных в последующих лекциях. Вперёд к программированию на стороне базы данных!

2

Задача

SQL SELF, 49 уровень, 0 лекция

Недоступна

Создание простой функции сложения

Создание простой функции сложения

Открыть