PL/pgSQL. Функции и процедуры
Дорогу осилит идущийВ рамках данной статьи познакомимся (крайне поверхностно) с инструментами SQL, которые позволяют выйти за пределы обычных запросов, и писать собственные собственные функции (и другие подпрограммы) для СУБД. Также затронем ряд расширений, которые позволяют в рамках работы с СУБД использовать различные языки программирования.
Сразу отмечу, что эта тема дается больше для ознакомления и понимания смежных для разработчика технологий. Нельзя исключать, что вам придется использовать эту информацию в коммерческой разработке, но это относительно редкая потребность. К тому же, данной статьи для реального использования вряд ли будет достаточно, если исключить наиболее тривиальные задачи (шанс их встретить тоже невелик, но несколько выше).
Функции и процедуры в SQL
Для начала, разберемся с терминологией и понятиями функции и процедуры в SQL.
Функция (в зависимости от контекста - SQL-функция, пользовательская функция) - именованный блок инструкций (кода), возвращающий значение. Можно провести аналогию с методом в Java, у которого тип возвращаемого значения отличен от void.
Процедура (хранимая процедура) - именованный блок инструкций, который не возвращает значения*. Аналогия из Java - void-метод.
* Результат процедуры, на самом деле, можно вернуть через функциональность выходных параметров. Но в рамках статьи этот инструмент разбирать не будем.
И процедура, и функция являются объектами БД, которые единожды компилируются и после этого могут вызываться множество раз. И функции, и процедуры могут принимать входные параметры.
Основные отличия между этими объектами заключаются в наличии возвращаемого значения и в синтаксисе обращения к ним - для вызова функции и процедуры используются разные языковые конструкции.
С некоторыми функциями, которые SQL (или PostgreSQL) предоставляет по умолчанию, мы уже знакомы: например, nextval() или concat().
Кроме того, стоит понимать, что под понятием функции могут скрываться различные по механизму сущности, для описания и использования которых требуется различный синтаксис и имеющие различную специфику применения:
- “Обычные” функции, с которыми мы знакомимся в рамках этой статьи;
- Агрегатные: COUNT(), SUM() и т.д.;
- Оконные. Не затрагиваем в рамках курса.
Функция, как и процедура (дальше будем использовать термин подпрограмма, чтобы обозначить оба этих термина), может быть написана с использованием различных языков. Например:
- SQL. Подпрограмма может из себя представлять набор SQL-запросов;
- C (Си). Язык о котором слышали, наверно, все:)
- Процедурный язык*. СУБД предоставляют различные процедурные языки для написания подпрограмм. В первую очередь, речь идет о PL/SQL (Procedural Language/SQL). Мы сегодня познакомимся с его диалектом для PostgreSQL - PL/pgSQL. Но также могут быть использованы и другие процедурные языки, которые поддерживает СУБД - из коробки или с помощью подключаемых расширений. Так, например, для postgres доступны также PL/Tcl, PL/Perl, PL/Python и пр.;
- Внутренние языки. В данном случае имеется ввиду не какой-то конкретный ЯП, а написание подпрограммы на языке программирования и ее компиляция для использования в PostgreSQL. По сути, это является аналогом нативных методов в Java.
* Си тоже является процедурным языком. Но из-за синтаксически разных подходов к описанию функций используя Си и, скажем, PL/pgSQL его выделяют отдельно.
PL/pgSQL
Данный язык предоставляет синтаксис для описания большинства привычных нам базовых синтаксических конструкций - ветвления, циклов и т.д. В рамках курса мы не будем с ними знакомиться - при желании это можно сделать самостоятельно, но я не вижу в этом смысла до тех пор, пока вы не окажетесь на проекте, где эти знания нужны.
Однако мы рассмотрим базовый синтаксис применения этого языка. Как для не именованных блоков кода (их можно считать функциями, но у них не указывается названия и они не сохраняются в БД для переиспользования), так и для описания тела функции или процедуры.
Блок кода (тоже подпрограмма, к слову) с использованием PL/SQL может понадобиться, например, для упрощения описания запроса или группы запросов (например, в рамках транзакции). В первую очередь, потому что такой блок позволяет определять переменные, которые можно использовать в запросах. Это намного удобнее, чем множество раз дублировать одно и то же значение. Скажем, значение PK для вставки записи и ряда связанных записей, ссылающихся на первую.
Например:
-- Подготовим таблицы для примера
create table t1 (
id bigint primary key,
col bigint
);
create sequence t1_id_seq;
create table t2 (
id bigserial primary key,
t1_id bigint references t1 (id)
);
--Транзакция с PL/pgSQL блоком
begin; -- Открываем транзакцию
do -- Открываем подпрограмму
$$
declare -- Указываем внутренний блок для объявления переменных
id1 bigint = nextval('t1_id_seq'); -- Инициализируем переменную
возвращаемым значением функции
id2 bigint = 10000; -- Инициализируем переменную литералом
begin -- Открываем исполняемую часть подпрограммы
–- вставляем значения с использованием переменных как значений PK:
insert into t1 values
(id1, 1),
(id2, 1);
–- вставляем значения с использованием переменных как значений FK
insert into t2 (t1_id) values
(id1),
(id1),
(id2);
end -- Закрываем исполняемую часть подпрограммы
$$; -- Закрываем подпрограмму
commit; -- Коммитим транзакцию
В целом, все то же самое можно сделать и без явного объявления транзакции - изменения этого блока все равно не применятся, если возникнет ошибка в процессе - данный блок считается атомарным. Но в отличии от транзакции, ошибка в подпрограмме не блокирует выполнение дальнейших запросов, идущих после PL/SQL-блока, что не всегда хорошо.
И еще один пример:
begin;
do
$$
declare
id bigint = (select max(id) + 1 from t1); -- Инициализируем
переменную результатом запроса
begin
insert into t1 values (id, 1);
insert into t2 (t1_id) values
(id),
(id);
end
$$;
commit;
Синтаксис для работы с процедурами
Для демонстрации работы с процедурами, создадим хранимую процедуру, похожую на примеры выше:
create procedure insert_data()
language plpgsql
as $$
declare
id1 bigint = nextval('t1_id_seq');
id2 bigint = nextval('t1_id_seq');
begin
insert into t1 values
(id1, 1),
(id2, 1);
insert into t2 (t1_id) values
(id1),
(id1),
(id2);
end
$$;
Синтаксис создания выглядит как CREATE PROCEDURE, после которого указывается имя процедуры с параметрами в скобках (рассмотрим ниже), язык кода (в нашем случае - PL/pgSQL, прописывается как plpgsql) и после оператора AS - тело подпрограммы.
Вынесем декларируемые операторы в параметры, чтобы можно было передавать их извне. Это позволит убрать нам блок с переменными (но мы можем его оставить при необходимости, а также инициализировать внутренние переменные с использованием параметров):
create procedure insert_data_with_params(id1 bigint, id2 bigint)
language plpgsql
as $$
begin
insert into t1 values
(id1, 1),
(id2, 1);
insert into t2 (t1_id) values
(id1),
(id1),
(id2);
end
$$;
И, наконец, напишем ту же процедуру, но используя SQL - ведь блок с переменными нам не нужен, а другим синтаксисом PL/SQL мы не пользовались:
create procedure insert_data_with_params_sql(id1 bigint, id2 bigint)
language sql
as $$
insert into t1 values
(id1, 1),
(id2, 1);
insert into t2 (t1_id) values
(id1),
(id1),
(id2);
$$;
В данном случае получилось почти эквивалентно предыдущему решению.
Также, мы можем изменять процедуру при необходимости - переименовывать или переносить в другую схему. Синтаксис в целом однотипен с тем, как это выглядит для другие сущностей БД. Например, переименование:
alter procedure insert_data() rename to insert_data_plpgsl;
Обратите внимание, имя процедуры указывается со скобками (и параметрами, если они есть), чтобы избежать неоднозначности, если процедура перегружена.
Удаление тоже выглядит стандартно:
drop procedure insert_data_plpgsl;
И, наконец, вызов процедуры осуществляется через оператор CALL:
call insert_data_plpgsl(); call insert_data_with_params(100, 101); call insert_data_with_params_sql(200, 201);
Данный оператор транзакционен, т.е. CALL-запросы будут корректно обрабатываться внутри транзакции, а ошибка в таком запросе - прервет транзакцию.
Синтаксис для работы с функциями
На базовом уровне здесь все достаточно похоже на процедуры, с поправкой на наличие возвращаемого значения. Напишем собственную функцию для вычисления суммы двух чисел:
create function sum_params(a bigint, b bigint) returns bigint
language plpgsql
as $$
begin
return a + b;
end
$$;
Ключевых изменений - добавился оператор RETURNS с указанием типа возвращаемого значения и оператор RETURN для возвращения результата.
Попробуем написать свой враппер для получения следующего значения последовательности t1_id_seq:
create function next_t1_id_seq() returns bigint
language plpgsql
as $$
begin
return nextval('t1_id_seq');
end
$$;
И, наконец, представим запрос на вставку значений в виде функции. Для успешного выполнения будем возвращать true, в случае ошибки - оставим ошибку, чтобы не вдаваться в нюансы работы с исключениями в PL/SQL:
create function insert_data_f() returns boolean
language plpgsql
as $$
declare
id1 bigint = next_t1_id_seq();
id2 bigint = next_t1_id_seq();
begin
insert into t1 values
(id1, 1),
(id2, 1);
insert into t2 (t1_id) values
(id1),
(id1),
(id2);
return true;
end
$$;
Или проще. Обратите внимание на уже знакомый нам по view синтаксис CREATE OR REPLACE:
create or replace function insert_data_f() returns boolean
language plpgsql
as $$
begin
call insert_data_plpgsl();
return true;
end
$$;
С опциями ALTER все схоже с процедурами:
alter function insert_data_f() rename to insert_data;
То же и с удалением:
drop function insert_data;
И, наконец, вызов функции нам уже знаком - вызвать функцию можно в рамках DML-запросов, указав имя и параметры (если необходимо).
Простейший вариант:
select sum_params(1, 2);
С теорией на сегодня все!
Переходим к практике:
Задача 1
Реализуйте добавление добавление пассажира и двух билетов для него в виде процедуры. id пассажира и билетов должны быть:
Вариант 1: Вшиты в процедуру;
Вариант 2: Передаваться параметрами.
Язык можете выбрать на свой вкус.
Задача 2
Реализуйте примеры из раздела Синтаксис для работы с функциями, используя SQL, вместо PL/pgSQL.
Если что-то непонятно или не получается – welcome в комменты к посту или в лс:)
Канал: https://t.me/ViamSupervadetVadens
Мой тг: https://t.me/ironicMotherfucker
Дорогу осилит идущий!