Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
SQL-хранимые функции в postgresql
Сервер баз данных PostgreSQL позволяет создавать специальные объекты, называемые хранимые функции. Использование таких функций даёт возможность переносить часть кода на сторону сервера. В стандартной установке сервера можно создавать функции двух типов: SQL-функции, состоящие из обычных sql-запросов и и функции на алгоритмическом языке программирования plpgsql. Также существуют дополнительные библиотеки, которые, в случае их подключения, дают возможность писать хранимые функции на других языках программирования, например C или Python.
Из причин, которые могут побудить перенести часть кода приложения на сторону сервера баз данных, выделим пять:
1. Увеличение скорости обработки данных, хранящихся на сервере. Действительно, не тратится время на перенос команды и промежуточных данных по сети.
2. Возможность написания набора api, который использовать его разными приложениями, тем самым унифицируя процесс обработки данных.
3. Появляется возможность закрыть от прикладного программиста саму структуру данных.
4. Обезопасить часть кода приложения, которая теперь будет расположена на сервере баз данных.
5. Использование хранимых функций позволяет создавать более простые для модернизации системы. Например менять структуру данных, не думая проблемах на стороне клиента.
Примеры SQL-функций
Как я уже сказал выше, sql-функции состоят из стандартных команд языка SQL. Рассмотрим простой пример функции, которая выполняет только одно действие: добавляет к указанной таблице строку, со значением поля, переданным в качестве параметра.
create function addf(nm text) returns void
as
$$
insert into facult (nazvanie) values (nm)
$$
language 'sql';
Запустить функцию, которая не возвращает данных, можно так
select addf('Русского языка')
или так
select * from addf('Немецкого языка')
Обращаю внимание, что при определении функции мы в конце указываем language 'sql', тем самым указывая тип тип функции. Следует также отметить, что полный вариант заголовка командысоздания функции выглядит более длинно: create or replace function имя_функции(параметры). В этом случае, если функция уже существует, она будет замещена новым кодом.
Следующая функция возвращает данные табличного типа: оценки указанного учащегося.
create function stud(nm text)
returns table(fio text, ocenka integer)
as
$$
select a.fio, b.ocenka
from student a inner join ocenki b on a.id=b.id_s
where a.fio=nm
order by b.ocenka
$$
language 'sql';
Вызвать функцию можно командой
select * from stud('Петров')
Как мы видели, функции могут возвращать табличные значения, могут ничего не возвращать. Но можно создавать функции, которые возвращают скалярные значения.
create function getc()
returns bigint
as
$$
select count(*) from student;
$$
language 'sql';
Результат, т. е. Количество строк в таблице student, можно получить, выполнив команду
select * from getc();
По сути результатом будет таблица, состоящая из одного столбца и одной строки.
Ну или ещё один пример функции, которая не обращается к таблицам, а просто выполняет некоторое арифметическое действие.
create function oper(integer, integer, integer )
returns integer
as
$$
select ($1 + $2) * $3
$$
language 'sql';
Результат указанного в функции действия можно получить просто выполнив, например, такой запрос
select * from oper(3, 4, 5);
Обратим внимание на ещё одну особенность последней хранимой функции. Это использование неименованных параметров. Они просто нумеруются $1, $2, $3 и т. д.
Отметим одну очень важную особенность хранимых sql-функций, возвращающих обычные табличные значения. Они ведут себя как обычные запросы или представления. В запросах же, которые находятся внутри функции, можно обращаться к другим подобным функциям, в результате может возникнуть целая иерархия хранимых функций, обращающихся друг к другу. К таким подходам, однако, следует относиться с осторожностью, в противном случае можно получить код, в котором сложно разобраться. Во всяком случае каждая такая функция должна быть хорошо задокументирована.
Ещё один вопрос в связи с sql-функциями в СУБД PostgreSQL. В функции может стоять несколько команд. Приведём пример
create function add(f1 text, f2 text)
returns void
as
$$
insert into facult (nazvanie) values(f1);
insert into facult (nazvanie) values(f2);
$$
language 'sql';
Команды в функции выполняются одна за другой, добавляя в таблицу данные, полученные в качестве параметров.
Конечно, в теле функции вместе с другими командами может стоять и команда select. Однако, если стоит более одной команды select, то вызывающий код получает только последний результат. Для работы работы с несколькими запросами в sql-функциях следует использовать возможности их объединения, например с помощью union. В хранимых функциях на языке plpgsql возможности значительно расширены, в частности относительно возвращения нескольких запросов.
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
