Хранимые процедуры

Подпрограммы — скрипты, которые позволяют создавать пользовательские функции и процедуры, при помощи которых можно управлять структурой данных и так далее.

Процедурные расширения — это расширения, которые позволяют пользователю писать и запускать серверные процедуры на разных языках программирования в БД.

Что будем использовать:

1. SQL
2. PLPGSQL

SQL - это основной язык, он использует для работы с реляционными БД (DDL, DML, DQL, TCL, DCL)

Plpqsql - основывается на sql, что позволяет делать:

1. Переменные
2. Управляющие конструкции
3. Обработчики ошибок
4. Функции
5. Процедуры
6. Триггеры

Общий вид объявления: language Проц_расш

Пример: language PLpgsql;

language sql;

Postgres поддерживает разные процедурные расширения, и каждое расширение добавляет функционал.

Виды:

• PL/Python - позволяет писать функции и процедуры на языке python в БД
• PL/Perl - позволяет писать функции и процедуры на языке perl в БД
• PL/TCL - позволяет писать функции и процедуры на языке tcl в БД
• PL/Java - позволяет писать функции и процедуры на языке java в БД
• PL/Ruby - позволяет писать функции и процедуры на языке ruby в БД
• PL/V8 - позволяет писать функции и процедуры на языке javascript в БД

Преимущества:

1. Гибкость - использование языков для спец задач или их управления
2. Производительность — логика подпрограмм может быть прописана на самом сервере, что позволяет уменьшить время отклика от сервера.
3. Мощь - можно использовать библиотеки данных языков

Виды подпрограмм:

1. Анонимные(безымянные)
2. Функции
3. Процедуры

Анонимные подпрограммы

Анонимные подпрограммы — это блоки, которые можно запускать сразу после написания. В анонимной подпрограмме можно выводить информацию и выполнять действия.

Общий вид:

do
$$
declare
a text;
b text = 'world';
begin
	a = 'hello';
	raise notice '%,%',a,b;
end;
$$

do - объявления начала безымянной подпрограммы

$$ — (экранирование) разделительный строковой литерал; внутри баксов можно указывать текст. Если указан текст, то он должен быть в начале и в конце.

declare

declare — используется для объявления переменных (локальных переменных) в PL/pgSQL внутри тела или перед телом.

Общий вид declare:

1. ИНДФ тип_данных
2. ИНДФ тип_данных = 'значение'

Пример:

declare
a int = 3;
b varchar = 'ajebdh';
c char(30);

В лок_пер можем ставить огр на столбец

Общий вид declare:

1. ИНДФ тип_данных ОГР = 'знач'; ИНДФ тип_данных ОГР;
2. ИНДФ ОГР тип_данных = 'знач'; ИНДФ ОГР тип_данных;

Пример:

do $$
declare
asd int not null = 0;
das constant text = 42;
begin
	raise notice '%,%',asd,das;
end; $$

begin end

begin end — это блок кода PL/pgSQL, в котором находятся SQL-запросы и управляющие конструкции.

Общий вид begin end:

begin
	Тело;
end;

begin
	begin
		Тело;
	end;
end;

raise

raise — используется для вывода сообщения или генерации ошибок внутри функции, процедуры и так далее.

Общий вид raise:

Begin
	raise вид_raise '%',лок_пер;
End;

% — используется для того чтобы вместо % подставить локальную переменную.

Виды raise:

1. Debug
2. Log
3. Notice
4. Warning
5. Exception

raise debug

Debug — это способ вывести отладочное сообщение (do, function, procedure), подходит для временной отладки кода, чтобы не засорять логи в notice и warning.

Пример:

do $$
Begin
	raise debug 'Знач. Переменной = %',42;
	raise notice 'это на debug';
End; $$

Для включения debug: set client_min_messages = debug; Для выключения debug: set clienr_min_messages = notice

Уровни debug:

1. debug1 (наименее подробный)
2. debug2
3. debug3
4. debug4
5. debug5 (максимально подробно) Дефолт raise debug = debug1

raise log

raise log — используется для записи сообщений в журнал сервера.

Особенности:

1. Сообщения уровня log попадают в серверный лог (в отличие от notice и debug, которые используются на клиентской части).
2. Логи клиентской части обычно не видит, но можно включать
3. Используется в plpgsql для системных сообщений для сохранения в логах

Пример:

do $$
user_id int = 144;
Begin
	raise log 'обновляем данных пользователя id = %', user_log;
End; $$

Для чего использовать:

1. Для важных событий
2. Для аудита действий
3. Для диагностики

raise notice

Особенности:

1. Сообщение видно клиенту
2. НЕ ПРЕРЫВАЕТ ВЫПОЛНЕНИЕ КОДА

Общий вид:

raise notice 'Шаблон',лок_пер...;

Пример:

do $$
declare
a int;
b text = 'FFF';
Begin
	raise notice '%',b;
	a = 10;
	a = a * 5;
	raise notice '%',a;
End; $$

% - объявления лок_пер в шаблоне

%% - знак процента

Когда использовать:

1. Для отладки
2. Для информационных сообщений

warning

Для вывода предупреждающих сообщения пользователю, но без остановки кода

Особенности:

1. Сообщение warning видно клиенту
2. Не прерывает код
3. Пишется в серверные логи

Пример:

do $$
begin
	raise warning 'Мы все уммм';
	raise notice 'Мы все Живём';
end; $$

Когда использовать: Для логирования определенных действий

exception

Это оператор которые вызывает генерацию ошибки

Особенности:

1. При выполнении кода Подпрограммы, дойдя до exception подпрограмма падает в ошибку
2. Сообщение показывается клиенту и падает в логи
3. Можно менять сообщение оригинальных ошибок

Пример:

do $$
Begin
	raise exception 'Упс';
End; $$

Вложение в анонимных функциях

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

Пример:

do $$
<<Внеш_блок>>
Declare
a text = 'jejej';
Begin
	<<Внутр_блок>>
	Declare
	a int = 100;
	Begin
		Raise notice '%, %', Внеш_блок.a, Внутр_блок.a
	End;
End; $$

do $$
Declare
a char(30) = (select s.фамилия from s where s.номер_поставщика = 's1')
Begin
	insert into gr632_test11
	values(a)
End; $$