PL/pgSQL

PL/pgSQL (англ. Procedural Language/PostGres Structured Query Language) — процедурное расширение языка SQL, используемое в СУБД PostgreSQL. Этот язык предназначен для написания функций, триггеров и правил и обладает следующими особенностями:

• добавляет управляющие конструкции к стандарту SQL;
• допускает сложные вычисления;
• может использовать все объекты БД, определенные пользователем;
• прост в использовании.

Преимущества использования

Стандартный SQL используется в PostgreSQL и других реляционных БД как основной язык для создания запросов. Он переносим и прост, как для изучения, так и для использования. Однако слабое его место — в том, что каждая конструкция языка выполняется сервером отдельно. Это значит, что клиентское приложение должно отправлять каждый запрос серверу, получить его результат, определенным образом согласно логике приложения обработать его, посылать следующий запрос и т. д. В случае, если клиент и сервер БД расположены на разных машинах, это может привести к нежелательному увеличению задержек и объема пересылаемых от клиента серверу и наоборот данных.

При использовании PL/pgSQL появляется возможность сгруппировать запросы и вычислительные блоки в единую конструкцию, которая будет размещаться и выполняться на сервере, а клиент будет отправлять запрос на её выполнение и получать результат, минуя все промежуточные пересылки данных назад—вперед, что в большинстве случаев позитивно сказывается на производительности. Также функциональность анонимных блоков позволяет писать запросы не на SQL, а на любом существующем процедурном языке сервера, в том числе pl/pgSQL, без создания хранимых функций на сервере СУБД.

Поддерживаемые типы аргумента и возвращаемого значения

Функции, написанные на PL/pgSQL, могут принимать в качестве аргумента и возвращать как результат значения любого скалярного или составного типа, допустимые для сервера БД, включая определенные пользователем, строковые типы и записи.

Допускаются также аргументы и возвращаемые значения, относящиеся к псевдотипам anyelement, anyarray, anynonarray и anyenum. Благодаря этому появляется возможность определять полиморфные функции, принимаемый и возвращаемый которыми тип может изменяться от вызова к вызову. Функции на PL/pgSQL могут возвращать не одно значение, а целый набор значений, либо вообще не возвращать никакого значения (применяя тип void).

Структура кода на PL/pgSQL

Любая программная конструкция (функция, триггер или правило), написанная на PL/pgSQL, имеет блочную компоновку и выглядит вот так:

[ <<метка>> ]
[ DECLARE
    объявления переменных ]
BEGIN
    тело программы
END [ метка ];
/*  Многострочный
    комментарий */
--  Однострочный комментарий

Как и принято, квадратными скобками выделены фрагменты, которые опциональны и могут не присутствовать в конкретном варианте.

Каждое отдельное объявление и каждая конструкция внутри тела программы должна заканчиваться точкой с запятой. Блок, находящийся внутри другого блока, также должен иметь точку с запятой после завершающего END, однако самое «внешнее» END в точке с запятой не нуждается.

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

Синтаксис языка

Формат объявления переменных

Любая переменная в PL/pgSQL должна быть объявлена перед использованием, неявное объявление не поддерживается; единственным исключением является цикл FOR — в нем переменная цикла по умолчанию объявляется как INTEGER. Возможно сразу же присваивать переменной значение либо объявлять её как константу:

имя [ CONSTANT ] тип [ NOT NULL ] [ { DEFAULT | := } значение ];

Ключевое слово DEFAULT применяется для задания значения по умолчанию, ключевое слово CONSTANT защищает переменную от дальнейшего изменения, определяя её как константу.

Передача аргументов

По умолчанию переданные параметры обозначаются как $1, $2 и так далее. Однако, если желательно увеличить читабельность кода, то можно обозначить эти параметры с помощью псевдонимов. Возможны два способа.

Можно задавать имя параметра непосредственно в определении функции:

CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Иначе, можно объявить псевдоним для параметра непосредственно в разделе объявлений переменных:

CREATE FUNCTION sales_tax(real) RETURNS real AS $$
DECLARE
    subtotal ALIAS FOR $1;
BEGIN
    RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

См. также

• PL/SQL — близкий язык от Oracle
• Другие встраиваемые в PostgreSQL языки:

  PL/Java

  PL/Lua

  PL/Perl

  PL/PHP

  PL/Python

  PL/R

  PL/Ruby

  PL/Scheme

  PL/Sh

  PL/Tcl

  PL/V8

Ссылки

• PL/pgSQL — SQL Procedural Language  (англ.)
• Сергей Супрунов. PostgreSQL: функции и триггеры // Системный администратор. — 2004. — № 10 (23).