Доброго здоровья читателям моего канала programmer's notes. Поддерживаем мой канал.
PLpgSQL и SQL
я изложил начало языка plpgsql. Этот язык легко интегрируется с обычным языком SQL, позволяя писать на стороне PostgreSQL полноценный программный с обработкой реляционных данных.
Перейдём теперь к вопросам, связанным с сочетанием алгоритмического языка plpgsql и собственно команд языка SQL. Это важнейшая тема, поскольку именно это и является сутью использования программирования на стороне сервера. Рассмотрим функцию, в которой данные в цикле берутся из результата запроса команды select.
create or replace function all_ocenki()
returns table(id integer, fio text, ocenka integer)
as
$$
declare
rec record;
begin
for rec in execute 'select s.id, s.fio, o.ocenka
from student s inner join ocenki o on s.id=o.id_s'
loop
id = rec.id;
fio = rec.fio;
ocenka = rec.ocenka;
return next;
end loop;
end;
$$
language 'plpgsql';
Сделаем несколько замечаний по поводу записанной функции.
1. Заголовок цикла можно переписать и проще: for rec in select s.id, s.fio, o.ocenka from student s inner join ocenki o on s.id=o.id_s. Однако использование оператора execute предпочтительнее, так как позволяет динамически формировать запросы.
2. Мы определили переменную типа record. Это важный тип, который позволяет присваивать переменной такого типа строки таблицы.
3. Строки между loop и return next позволяют гибко подходить к обработке данных, полученных из строки таблицы. Ведь переменные id, fio, ocenka можно подвергнуть дополнительному преобразованию. Отметим также, что имена этих переменных фактически заданы в операторе returns и именно из них формируется результат.
4. Оператор return next не осуществляет возврат из функции, а фактически формирует построчно результирующую таблицу. Возврат из функции осуществляется по окончанию работы кода, при тогда возвращается и полученная в результате таблица.
5. Обратим внимание, что константа в операторе execute длинная и мы разбили её на две строки. Именно так можно писать многострочную константу типа text.
6. Вызвать функцию можно обычным способом
select * from all_ocenki();
Приведём более сложный пример хранимой функции. Получив значения через входные параметры функция добавляет оценку указанному ученику, а затем выводит все его оценки.
create function so (id integer, id_p integer, id_pred integer, oc integer, d date)
returns table(fio text, ocenka integer)
as
$$
declare
rec record;
begin
-- добавляем оценку
execute 'insert into ocenki (ocenka, dt, id_s, id_p, id_predm)
values ($1, $2, $3, $4, $5)' using oc, d, id, id_p, id_pred;
-- получить оценки
for rec in execute 'select a.fio, b.ocenka
from student a inner join ocenki b on a.id=b.id_s
where a.id=$1' using id
loop
fio := rec.fio;
ocenka := rec.ocenka;
return next;
end loop;
end;
$$
language 'plpgsql';
Приведём некоторые пояснения представленной выше хранимой функции so(). Самый важный момент здесь новое в использовании оператора execute. А именно мы здесь имеем динамически формируемые команды SQL. Для этого используются параметры (параметры в запросе SQL), которые нумеруются как $1, $2, … и т. д. В конце ставится ключевое слово using, в котором по порядку эти параметры указываются. В нашем случае этими параметрами являются входные параметры функции, но можно использовать и локальные переменные. В остальном данная функция аналогично предыдущей функции all_ocenki(),которую мы подробно разбирали. Вызов данной функции осуществляется опять же стандартным способом.
select * from so(3, 2, 3, 1, '2.12.2025');
Перепишем функцию so() с использованием ключевого слова query, тем самым сократив её текст
create function so1 (id integer, id_p integer, id_predm integer, oc integer, d date)
returns table(fio text, ocenka integer)
as
$$
begin
-- добавляем оценку
execute 'insert into ocenki (ocenka, dt, id_s, id_p, id_predm)
values ($1, $2, $3, $4, $5)' using oc, d, id, id_p, id_predm;
-- получить оценки
return query execute 'select cast(a.fio as text), b.ocenka
from student a inner join ocenki b on a.id=b.id_s
where a.id=$1' using id;
end;
$$
language 'plpgsql';
Как видим, текст программы значительно сократился. Действительно использование оператора for для получения табличного значения оправдано, если мы хотим после получения очередной строки хотим ещё как-то преобразовать полученные данные. А если в этом нет необходимости, то проще использовать query. Укажем также на один важный нюанс. Столбец в таблице имеет тип character, а возвращаемое из функции значение у нас имеет тип text. По этой причине в запросе мы осуществляем преобразование типов: cast().
Рассмотрим два важных вопроса, которые так или иначе возникнут, если заниматься программированием на языке PlpgSQL: 1. Можно ли получить значение переменных непосредственно из запроса select? 2. Как возвратить значения нескольких переменных из функции? Один из вариантов решения подобной задачи представлен ниже.
create or replace function getvar(idd integer)
returns table(fio text, nazvanie text)
as
$$
declare
fio text;
nazvanie text;
begin
select a.fio, b.nazvanie into fio, nazvanie
from facult b inner join student a on a.id_f=b.id
where a.id=idd;
return query select fio, nazvanie;
end;
$$
language 'plpgsql';
Мы видим, как, используя ключевое слово into можно присвоить значения переменным. В данном случае мы заведомо знаем, значения будут единственными. Возвратить же данные можно просто используя return query <запрос>. При этом важно, что мы в заголовке указали тип возвращаемого значения как таблица и описали её и не важно, что это только одна строка. Ну, а вызов вполне нам понятен
select * from getvar(1);
Хранимы процедуры в PostgreSQL
Наряду с хранимыми функциями в PostgreSQL есть хранимые процедуры. Они очень похожи на функции. Но они не возвращают значения. Вызываются процедуры с помощью команды call. Процедуры обладают большей возможностью в использовании транзакций, ниже на этой возможности мы остановимся в одной из следующих статей.
Рассмотрим простую хранимую процедуру, которая выполняет две команды insert. От функции она отличается только тем, что в ней отсутствует ключевое слово returns и команды return. Точно также в ней можно использовать переменные и разобранные выше программные структуры (условные конструкции, циклы, объявления переменных).
create or replace procedure test3(nm1 text, nm2 text)
as
$$
begin
execute 'insert into facult (nazvanie) values ($1)' using nm1;
execute 'insert into facult (nazvanie) values ($1)' using nm2;
end;
$$
language 'plpgsql';
Стоит отметить в хранимой процедуре можно вызывать как хранимы функции с использованием команды select, так и другие хранимые процедуры, используя команду call.
Следующая статья по PLpgSQL здесь
Пока всё!
Хорошего программирования. Оставляйте свои комментарии, не забывайте про лайки и подписывайтесь на мой канал programmer's notes.
