Несколько типичных примеров использования PL/pgSQL
9 августа 2017
PL/pgSQL — язык программирования, используемый для написания хранимых процедур и триггеров для PostgreSQL. Сказать по правде, впервые увидев код на PL/pgSQL, я испытал ужас. Хотя в коде и угадывались типичные конструкции процедурных языков программирования, выглядел он больно уж загадочно и вообще напоминал код на COBOL. Само собой разумеется, со временем это ощущение у меня прошло. Цель заметки — показать, что кода на PL/pgSQL не нужно бояться, и в целом язык довольно простой.
Сразу начнем с примера кода, создающего 100 временных таблиц:
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-блоке, например:
… или:
Все, что вы вводите в psql, должно заканчиваться точкой с запятой, поэтому она и стоит в конце. Два знака доллара нужны для того, чтобы пометить, где начинается, и где заканчивается код. В более общем случае можно использовать произвольную метку, начинающуюся и заканчивающуюся знаком доллара, лишь бы эта метка не встречалась в самом коде, например:
Остальная часть приведенного ранее кода должна быть понятна любому программисту, поэтом перейдем к более сложному примеру:
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 столбцов. От предыдущего примера код отличается только использованием нескольких переменных, а также использованием условных операторов. Поэтому, думается, у вас не должно возникнуть сложностей с его пониманием.
Теперь рассмотрим такой код:
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, а также обработки исключений:
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, отсюда и такой код.
Наконец, рассмотрим последний на сегодня пример:
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
Метки: PostgreSQL, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.