Categories: PostgreSQL

plpgsql/

PL/pgSQL — язык программирования, используемый для написания хранимых процедур и триггеров для PostgreSQL . Сказать по правде, впервые увидев код на PL/pgSQL, я испытал ужас. Хотя в коде и угадывались типичные конструкции процедурных языков программирования, выглядел он больно уж загадочно и вообще напоминал код на COBOL. Само собой разумеется, со временем это ощущение у меня прошло. Цель заметки — показать, что кода на PL/pgSQL не нужно бояться, и в целом язык довольно простой.

Сразу начнем с примера кода, создающего 100 временных таблиц:

DO $$
DECLARE
i INTEGER ;
BEGIN
FOR i IN 1 .. 100
LOOP
RAISE NOTICE ‘i = %’ , i;
EXECUTE ( ‘create temp table temp_table_’ || i || ‘(x int);’ ) ;
END LOOP ;
END $$;

Когда нужно просто выполнить код на PL/pgSQL, без объявления процедуры, этот код пишется в do-блоке, например:

DO $$ BEGIN /* тут ваш код */ END $$;

… или:

DO $$ DECLARE /* переменные */ BEGIN /* код */ END $$;

Все, что вы вводите в psql, должно заканчиваться точкой с запятой, поэтому она и стоит в конце. Два знака доллара нужны для того, чтобы пометить, где начинается, и где заканчивается код. В более общем случае можно использовать произвольную метку, начинающуюся и заканчивающуюся знаком доллара, лишь бы эта метка не встречалась в самом коде, например:

DO $ololo$ BEGIN /* тут ваш код */ END $ololo$;

Остальная часть приведенного ранее кода должна быть понятна любому программисту, поэтом перейдем к более сложному примеру:

DO $$
DECLARE
i INTEGER ;
j INTEGER ;
q TEXT ;
BEGIN
FOR i IN 1 .. 2800 LOOP
q = ‘create temp table temp_table_’ || i || ‘(‘ ;
FOR j IN 1 .. 400 LOOP
IF j <> 1 THEN
q = q || ‘,’ ;
END IF ;
q = q || ‘attr_’ || j || ‘ int’ ;
END LOOP ;
q = q || ‘);’ ;
EXECUTE q;
END LOOP ;
END $$;

Эта процедура создает очень много временных таблиц, каждая из которых имеет 400 столбцов. От предыдущего примера код отличается только использованием нескольких переменных, а также использованием условных операторов. Поэтому, думается, у вас не должно возникнуть сложностей с его пониманием.

Теперь рассмотрим такой код:

CREATE OR REPLACE FUNCTION gen_long_string ( len INT ) RETURNS TEXT AS $$
DECLARE
res TEXT : = ‘abcdefghijklmnopqrstuvwxyz’ ;
BEGIN
WHILE LENGTH ( res ) <= len
LOOP
res : = res || res;
END LOOP ;

RETURN SUBSTRING ( res , 1 , len ) ;
END
$$ LANGUAGE ‘plpgsql’ ;

Здесь объявляется процедура, генерирующая строку заданной длины. Помимо объявления процедуры также демонстрируется использование цикла while. Заметьте, что язык процедуры нужно указывать явно, что и делается в последней строке кода. Это связано с тем, что триггеры и хранимые процедуры для PostgreSQL можно писать не только на PL/pgSQL, но и на других языках, в частности, на Perl, Python и JavaScript. Кстати, кавычки в имени языка использовать не обязательно.

В следующем коде приводится пример конструкции select into, а также обработки исключений:

CREATE OR REPLACE FUNCTION pgpro_edition_safe ( ) RETURNS TEXT AS $$
DECLARE
ver TEXT ;
BEGIN
SELECT pgpro_edition ( ) INTO ver;
RETURN ver;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE ‘pgpro_edition() procedure doesn’ ‘t exist’ ;
RETURN ‘standard’ ;
END
$$ LANGUAGE ‘plpgsql’ ;

Если на сервере объявлена процедура с именем pgpro_edition, будет возвращен результат вызова этой процедуры. Если же ее нет, будет выведено предупреждение, и возвращен результат по-умолчанию.

Работа с массивами осуществляется как-то так:

/*
Example:

SELECT byte_array_to_string_array(‘{«\x68656c6c6f»,»\x776f726c64″}’);
byte_array_to_string_array
—————————-
{hello,world}
(1 row)
*/

CREATE OR REPLACE FUNCTION byte_array_to_string_array ( bytes BYTEA [ ] )
RETURNS TEXT [ ] AS $$
DECLARE
strings TEXT [ ] ;
BEGIN
— IF array_length(bytes, 1) IS NULL THEN
IF bytes = ‘{}’ THEN
RETURN ‘{}’ ;
END IF ;

FOR i IN array_lower ( bytes , 1 ) .. array_upper ( bytes , 1 ) LOOP
strings [ i ] : = convert_from ( bytes [ i ] , ‘utf-8’ ) ;
END LOOP ;

RETURN strings;
END
$$ LANGUAGE ‘plpgsql’ IMMUTABLE;

Здесь массив элементов с типом bytea превращается в массив строк. Заметьте, как выполняется проверка на пустой массив. По неизвестным мне причинам для пустого массива процедура array_length вместо нуля возвращает null , отсюда и такой код.

Наконец, рассмотрим последний на сегодня пример:

DO $$
DECLARE
r RECORD ;
cnt INT ;
BEGIN
FOR r IN
SELECT table_name FROM information_schema . TABLES
WHERE table_schema = ‘pg_catalog’ AND table_type ! = ‘VIEW’
ORDER BY table_name DESC
LOOP
EXECUTE ‘select count(*) cnt FROM ‘ || r . table_name INTO cnt;
RAISE NOTICE ‘% %’ , r . table_name , cnt;
END LOOP ;
END $$;

Здесь выводятся все таблицы каталога PostgreSQL, а также количество записей в них. Очень удобный кусок кода, позволяющий определить, к каким изменениям в каталоге приводят те или иные действия. Код демонстрирует работу с типом record, а также итерацию по строкам, являющихся результатом select-запроса.

Как видите, все не так уж и сложно. Увы, в рамках одной заметки не представляется возможным рассказать про весь PL/pgSQL. Если вам хотелось бы изучить этот язык поглубже, могу порекомендовать официальную документацию PostgreSQL , а также книгу PostgreSQL Server Programming . Также не лишено смысла почитывать код расширений для PostgreSQL , ну хотя бы моих же ZSON и pg_protobuf .

Дополнение: Пример использования триггеров в PostgreSQL

admin