Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
Начало программирования на PLpgSQL, программные структуры
Начинаем наконец программирование на PGplSQL. Сегодня основное о структурах языка PLpgSQL.
В современном PostgreSQL широко используется алгоритмический язык программирования plpgsql. Чаще название языка записывается как PL/pgSQL. Расшифровывается название как Procedural Language/PostGres Structured Query Language, что можно несколько вольно перевести как «Процедурный Язык Postgres с поддержкой SQL-запросов». В чём значимость этого языка в работе в СУБД PostgreSQL? Самое главное это использование стандартных алгоритмических конструкций с возможностями языка SQL. Таким образом мы можем писать хранимые функции со сложными алгоритмами обработки данных, используя при этом непосредственно в коде возможности языка SQL. При этом вся обработка будет происходить на стороне сервера. Другими словами часть кода информационной системы можно перенести на сторону сервера PostgreSQL.
Условные конструкции в PLpgSQL
Для того, чтобы уяснить как работают функции на языке plpgsql начнём с вычислительных функций, не затрагивающих обработку данных, хранящихся на сервере. Ниже представлена функция возвращающая максимальное значение из трёх.
create function max_three(a integer, b integer, c integer)
returns integer
as
$$
declare
i integer := 0;
begin
if a >= b and a >= c then
i := a;
elsif b >= a and b >= c then
i:=b;
else
i:= c;
end if;
return i;
end;
$$
language 'plpgsql';
Люди, не знакомы с языком plpgsql, но знакомые с каким-либо алгоритмическим языком программирования, легко поймут представленный выше код. Все конструкции знакомы. Всё же прокомментируем представленную хранимую функцию:
1. Напомним читателю, что представленный код представляет собой команду создания хранимой функции, в которой содержится и сам код функции. При выполнении этой команды в текущей базе данных появится объект — хранимая функция.
2. Обращаю внимание на строку language 'plpgsql', которая и указывает, что будет создана функция на указанном языке программирования.
3. Вызов данной функции произвести стандартным способом
select * from max_three(3, 2, 1)
Функция с помощью оператора return возвращает полученное скалярное значение. Заметим, что как и в случае с sql-функциями мы указали тип возвращаемого значения: returns integer.
4. Оператор declare используется, как видим из программы, для объявления типа переменно с её инициализацией.
5. Наконец условный оператор имеет знакомую из многих языков структуру. Заметим только, что все условия в нём заканчиваются ключевым словом then, после которого может идти произвольное количество действий. Есть также ключевое слово else, а вся условная конструкция заканчивается словами end if.
6. Наконец заметим, что сам код функции помещён между операторными скобками begin … end.
В хранимых функциях можно использовать так называемые OUT-параметры (параметр возвращающий значение), что может несколько сократить код функции. Используя OUT-параметр можно переписать предыдущую так
create function max2_three(a integer, b integer, c integer, out d integer)
as
$$
begin
if a >= b and a >= c then
d := a;
elsif b >= a and b >= c then
d:=b;
else
d:= c;
end if;
return;
end;
$$
language 'plpgsql';
Как видим, мы используем параметр d, для возвращения полученного результата, при этом вызов функции остаётся тем-же. При этом тип функции мы не указываем.
Вместо конструкции if...end if можно использовать оператор case
create or replace function max_three2(a integer, b integer, c integer)
returns integer
as
$$
declare
i integer := 0;
begin
case
when a > b and a > c then
i := a;
when b > a and b > c then
i := b;
else
i := c;
end case;
return i;
end;
$$
language 'plpgsql';
Оператор case в данном примере в точности соответствует такому же оператору, применяемому внутри команды select, о котором говорили в статье посвящённом команде select.
Ещё один пример вычислительной хранимой функции интересен тем, что это рекурсивная функция, т. е. функция обращающаяся к самой себе.
create function fact(n integer)
returns integer
as
$$
begin
if (n=0 or n=1) then
return 1;
else
return n*fact(n-1);
end if;
end;
$$
language 'plpgsql';
Вызов функции осуществляется опять же обычным способом, например так
select * fact(6)
Результат будет, разумеется 720.
Циклические конструкции в PLpgSQL
Перейдём теперь к такой классической структуре для любого императивного языка программирования как циклу. Начнём, естественно, с цикла for.
create or replace function sm(a integer[])
returns integer
as
$$
declare s integer := 0;
l integer := array_length(a, 1);
i integer := 1;
begin
for i in 1..l
loop
s := s+a[i];
end loop;
return s;
end;
$$
language 'plpgsql';
Цикл for похож на подобные операторы в других языках. Но есть свои особенности. Параметр цикла i меняется от 1 до l включительно. Позднее мы вернёмся к циклу for уже на другом уровне обработки. В данном примере была поставлена простая задача: функция получает числовой массив и считает сумму элементов массива. Обратим внимание на стандартную в PostgreSQL функцию array_length(a, 1), которая считает количество элементов массива. Вторым параметром помещается размерность массива. Вызов функции осуществляется следующим образом
select * from sm('{1, 2, 3, -1, 100}');
Приведём также ещё две функции, выполняющие действия, аналогичные описанным выше, но с использованием двух других циклов.
И так, следующая функция использует цикл while
create function sm1(a integer[])
returns integer
as
$$
declare s integer := 0;
l integer := array_length(a, 1);
i integer := 1;
begin
while i <= l
loop
s := s+a[i];
i := i+1;
end loop;
return s;
end;
$$
language 'plpgsql';
Как видим формат оператора цикла while ни чем не отличается от аналогичных циклах в других языка программирования. Цикл будет выполняться пока выражение в операторе while будет равно истине.
Оператор loop довольно гибкий инструмент организации повторяющихся действий.
create or replace function sm2(a integer[])
returns integer
as
$$
declare s integer := 0;
l integer := array_length(a, 1);
i integer := 1;
begin
loop
s := s+a[i];
i := i+1;
if i > l then
exit;
end if;
end loop;
return s;
end;
$$
language 'plpgsql';
Как вы, наверное, уже поняли, операторы for и while являются обёртками цикла loop. Как видим, он бесконечен. Выход из него нужно организовывать с помощью условного оператора if и оператора exit, который прерывает выполнение цикла. Можно также использовать и более короткую конструкцию exit when <условие>. Также отметим, что в циклах языка plpgsql можно использовать оператор continue, который, как и в других языках программирования, пропуская всё то, что находится ниже в цикле переходит к заголовку цикла.
В тех случаях, когда нужно перебирать значения из набора не плохо использовать ещё один оператор цикла foreach.
create or replace function smm(a integer[])
returns integer
as
$$
declare
s integer := 0;
x integer;
begin
foreach x in array a
loop
s := s+x;
end loop;
return s;
end;
$$
language 'plpgsql';
Оператор foreach позволяет перебирать значения из указанного набора, в частности из массива.
При работе с PLpgSQL следует учитывать, что встроенные функции, которые мы там используем, это функции PostgreSQL, т. е. изначально предназначенные для запросов. Для тех, кто привык использовать обычный язык программирования и его библиотеки это может показаться необычным. Поэтому всегда нужно сверяться со справочником. Например, функция array_length() в случае, если массив пуст будет возвращать не 0, а null. Это нужно учитывать в программном коде. Пример такого учитывания представлен ниже.
create or replace function smn(a integer[])
returns integer
as
$$
declare
s integer := 0;
l integer;
i integer := 1;
begin
if array_length(a, 1) is null then
l := 0;
else
l := array_length(a, 1);
end if;
for i in 1..l
loop
s := s+a[i];
end loop;
return s;
end;
$$
language 'plpgsql';
Как видим мы учли особенность функции array_length(), так чтобы функции работала корректно и в случае, если передаваемый массив пусть.
Следующая статья по PLpgSQL здесь:
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
