41.3. Объявления #
Все переменные, используемые в блоке, должны быть определены в секции объявления. (За исключением переменной-счётчика цикла FOR
, которая объявляется автоматически. Для цикла по диапазону чисел автоматически объявляется целочисленная переменная, а для цикла по результатам курсора - переменная типа record
.)
Переменные PL/pgSQL могут иметь любой тип данных SQL, такой как integer
, varchar
, char
.
Примеры объявления переменных:
user_id integer; quantity numeric(5); url varchar; myrow tablename%ROWTYPE; myfield tablename.columnname%TYPE; arow RECORD;
Общий синтаксис объявления переменной:
имя
[ CONSTANT ]тип
[ COLLATEимя_правила_сортировки
] [ NOT NULL ] [ { DEFAULT | := | = }выражение
];
Предложение DEFAULT
, если присутствует, задаёт начальное значение, которое присваивается переменной при входе в блок. Если отсутствует, то переменная инициализируется SQL-значением NULL
. Указание CONSTANT
предотвращает изменение значения переменной после инициализации, таким образом, значение остаётся постоянным в течение всего блока. Параметр COLLATE
определяет правило сортировки, которое будет использоваться для этой переменной (см. Подраздел 41.3.6). Если указано NOT NULL
, то попытка присвоить NULL
во время выполнения приведёт к ошибке. Все переменные, объявленные как NOT NULL
, должны иметь непустые значения по умолчанию. Можно использовать знак равенства (=
) вместо совместимого с PL/SQL :=
.
Значение по умолчанию вычисляется и присваивается переменной каждый раз при входе в блок (не только при первом вызове функции). Так, например, если переменная типа timestamp
имеет функцию now()
в качестве значения по умолчанию, это приведёт к тому, что переменная всегда будет содержать время текущего вызова функции, а не время, когда функция была предварительно скомпилирована.
Примеры:
quantity integer DEFAULT 32; url varchar := 'http://mysite.com'; transaction_time CONSTANT timestamp with time zone := now();
После объявления переменной её значение можно использовать в последующих выражениях инициализации в этом же блоке, например:
DECLARE x integer := 1; y integer := x + 1;
41.3.1. Объявление параметров функции #
Переданные в функцию параметры именуются идентификаторами $1
, $2
и т. д. Дополнительно, для улучшения читаемости, можно объявить псевдонимы для параметров $
. Либо псевдоним, либо цифровой идентификатор используются для обозначения параметра.n
Создать псевдоним можно двумя способами. Предпочтительный способ это дать имя параметру в команде CREATE FUNCTION
, например:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$ BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
Другой способ это явное объявление псевдонима при помощи синтаксиса:
имя
ALIAS FOR $n
;
Предыдущий пример для этого стиля выглядит так:
CREATE FUNCTION sales_tax(real) RETURNS real AS $$ DECLARE subtotal ALIAS FOR $1; BEGIN RETURN subtotal * 0.06; END; $$ LANGUAGE plpgsql;
Примечание
Эти два примера не полностью эквивалентны. В первом случае на subtotal
можно ссылаться как sales_tax.subtotal
, а во втором случае такая ссылка невозможна. (Если бы к внутреннему блоку была добавлена метка, то subtotal
можно было бы дополнить этой меткой.)
Ещё несколько примеров:
CREATE FUNCTION instr(varchar, integer) RETURNS integer AS $$ DECLARE v_string ALIAS FOR $1; index ALIAS FOR $2; BEGIN -- вычисления, использующие v_string и index END; $$ LANGUAGE plpgsql; CREATE FUNCTION concat_selected_fields(in_t sometablename) RETURNS text AS $$ BEGIN RETURN in_t.f1 || in_t.f3 || in_t.f5 || in_t.f7; END; $$ LANGUAGE plpgsql;
Когда функция на PL/pgSQL объявляется с выходными параметрами, им выдаются цифровые идентификаторы $
и для них можно создавать псевдонимы точно таким же способом, как и для обычных входных параметров. Выходной параметр это фактически переменная, стартующая с NULL и которой присваивается значение во время выполнения функции. Возвращается последнее присвоенное значение. Например, функция sales_tax может быть переписана так: n
CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$ BEGIN tax := subtotal * 0.06; END; $$ LANGUAGE plpgsql;
Обратите внимание, что мы опустили RETURNS real
— хотя можно было и включить, но это было бы излишним.
Чтобы вызвать функцию с параметрами OUT
, при вызове функции не указывайте выходные параметры:
SELECT sales_tax(100.00);
Выходные параметры наиболее полезны для возвращения нескольких значений. Простейший пример:
CREATE FUNCTION sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql; SELECT * FROM sum_n_product(2, 4); sum | prod -----+------ 6 | 8
Как обсуждалось в Подразделе 36.5.4, здесь фактически создаётся анонимный тип record
для возвращения результата функции. Если используется предложение RETURNS
, то оно должно выглядеть как RETURNS record
.
Выходные параметры поддерживаются и в процедурах, например:
CREATE PROCEDURE sum_n_product(x int, y int, OUT sum int, OUT prod int) AS $$ BEGIN sum := x + y; prod := x * y; END; $$ LANGUAGE plpgsql;
При вызове процедуры нужно указать все параметры. Вызывая процедуру на чистом SQL, вместо выходных параметров можно указать NULL
:
CALL sum_n_product(2, 4, NULL, NULL); sum | prod -----+------ 6 | 8
Однако в вызове процедуры из PL/pgSQL для каждого выходного параметра нужно указать переменную; эта переменная будет получать результат вызова. За подробностями обратитесь к Подразделу 41.6.3.
Есть ещё способ объявить функцию на PL/pgSQL с использованием RETURNS TABLE
, например:
CREATE FUNCTION extended_sales(p_itemno int) RETURNS TABLE(quantity int, total numeric) AS $$ BEGIN RETURN QUERY SELECT s.quantity, s.quantity * s.price FROM sales s WHERE s.itemno = p_itemno; END; $$ LANGUAGE plpgsql;
Это в точности соответствует объявлению одного или нескольких параметров OUT
и указанию RETURNS SETOF
.некий_тип
Для функции на PL/pgSQL, возвращающей полиморфный тип (см. Подраздел 36.2.5), создаётся специальный параметр $0
. Его тип данных соответствует типу, фактически возвращаемому функцией, который устанавливается на основании фактических типов входных параметров. Это позволяет функции обращаться к фактически возвращаемому типу данных, как показано в Подразделе 41.3.3. Параметр $0
инициализируется в NULL
и его можно изменять внутри функции. Таким образом, его можно использовать для хранения возвращаемого значения, хотя это необязательно. Параметру $0
можно дать псевдоним. В следующем примере функция работает с любым типом данных, поддерживающим оператор +
:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement) RETURNS anyelement AS $$ DECLARE result ALIAS FOR $0; BEGIN result := v1 + v2 + v3; RETURN result; END; $$ LANGUAGE plpgsql;
Такой же эффект получается при объявлении одного или нескольких выходных параметров полиморфного типа. При этом $0
не создаётся; выходные параметры сами используются для этой цели. Например:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement, OUT sum anyelement) AS $$ BEGIN sum := v1 + v2 + v3; END; $$ LANGUAGE plpgsql;
На практике может быть полезнее объявить полиморфную функцию, используя семейство типов anycompatible
, чтобы входные аргументы автоматически сводились к общему типу. Например:
CREATE FUNCTION add_three_values(v1 anycompatible, v2 anycompatible, v3 anycompatible) RETURNS anycompatible AS $$ BEGIN RETURN v1 + v2 + v3; END; $$ LANGUAGE plpgsql;
Показанная функция позволяет выполнить такой вызов:
SELECT add_three_values(1, 2, 4.7);
В данном случае целочисленные аргументы будут автоматически приведены к numeric. В функции же, использующей anyelement
, вам нужно было бы преобразовать все три аргумента к одному вручную.
41.3.2. ALIAS
#
новое_имя
ALIAS FORстарое_имя
;
Синтаксис ALIAS
более общий, чем предполагалось в предыдущем разделе: псевдонимы можно объявлять для любых переменных, а не только для параметров функции. Основная практическая польза в том, чтобы назначить другие имена переменным с предопределёнными названиями, таким как NEW
или OLD
в триггерной функции.
Примеры:
DECLARE prior ALIAS FOR old; updated ALIAS FOR new;
Поскольку ALIAS
даёт два различных способа именования одних и тех же объектов, то его неограниченное использование может привести к путанице. Лучше всего использовать ALIAS
для переименования предопределённых имён.
41.3.3. Наследование типов данных #
имя
таблица
.столбец
%TYPEимя
переменная
%TYPE
Конструкция %TYPE
предоставляет тип данных столбца таблицы или ранее объявленной переменной PL/pgSQL. Её можно использовать для объявления переменных, содержащих значения из базы данных. Например, для объявления переменной с таким же типом, как и столбец user_id
в таблице users
, нужно написать:
user_id users.user_id%TYPE;
Кроме того, после %TYPE
можно записать оформление массива, таким образом создав переменную, содержащую массив значений того типа, на который она ссылается:
user_ids users.user_id%TYPE[]; user_ids users.user_id%TYPE ARRAY[4]; -- равнозначно примеру выше
Так же, как и в случае с объявлением столбцов таблицы, являющихся массивом, неважно, используется ли несколько пар квадратных скобок или конкретная размерность массива, — Postgres Pro рассматривает все массивы заданного типа элемента как массивы одного типа, вне зависимости от размерности. (См. Подраздел 8.15.1.)
Используя %TYPE
, не нужно знать тип данных структуры, на которую вы ссылаетесь. И самое главное, если в будущем тип данных изменится (например: тип данных для user_id
поменяется с integer
на real
), то вам может не понадобиться изменять определение функции.
Использование %TYPE
особенно полезно в полиморфных функциях, поскольку типы данных, необходимые для внутренних переменных, могут меняться от одного вызова к другому. Соответствующие переменные могут быть созданы с применением %TYPE
к аргументам и возвращаемому значению функции.
41.3.4. Типы кортежей #
имя
имя_таблицы
%ROWTYPE
;имя
имя_составного_типа
;
Переменная составного типа называется переменной-кортежем (или переменной типа кортежа). Значением такой переменной может быть целый кортеж, полученный в результате выполнения запроса SELECT
или FOR
, при условии, что набор столбцов запроса соответствует заявленному типу переменной. Доступ к отдельным полям значения кортежа осуществляется как обычно, через точку, например rowvar.field
.
Переменная-кортеж может быть объявлена с таким же типом, как и строка в существующей таблице или представлении, используя нотацию имя_таблицы
%ROWTYPE
; или с именем составного типа. (Поскольку каждая таблица имеет соответствующий составной тип с таким же именем, то на самом деле в Postgres Pro не имеет значения, пишете ли вы %ROWTYPE
или нет. Но использование %ROWTYPE
более переносимо.)
Как и в случае с %TYPE
, после %ROWTYPE
также может следовать оформление массива, которое объявляет переменную, содержащую массив составного типа, на который она ссылается.
Параметры функции могут быть составного типа (строки таблицы). В этом случае соответствующий идентификатор $
будет переменной-кортежем, поля которой можно выбирать, например n
$1.user_id
.
Ниже приведён пример использования составных типов. table1
и table2
это существующие таблицы, имеющие, по меньшей мере, перечисленные столбцы:
CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$ DECLARE t2_row table2%ROWTYPE; BEGIN SELECT * INTO t2_row FROM table2 WHERE ... ; RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7; END; $$ LANGUAGE plpgsql; SELECT merge_fields(t.*) FROM table1 t WHERE ... ;
41.3.5. Тип record
#
имя
RECORD;
Переменные типа record
похожи на переменные-кортежи, но они не имеют предопределённой структуры. Они приобретают фактическую структуру от строки, которая им присваивается командами SELECT
или FOR
. Структура переменной типа record
может меняться каждый раз при присваивании значения. Следствием этого является то, что пока значение не присвоено первый раз, переменная типа record
не имеет структуры и любая попытка получить доступ к отдельному полю приведёт к ошибке во время выполнения.
Обратите внимание, что RECORD
это не подлинный тип данных, а только лишь заполнитель. Также следует понимать, что функция на PL/pgSQL, имеющая тип возвращаемого значения record
, это не то же самое, что и переменная типа record
, хотя такая функция может использовать переменную типа record
для хранения своего результата. В обоих случаях фактическая структура строки неизвестна во время создания функции, но для функции, возвращающей record
, фактическая структура определяется во время разбора вызывающего запроса, в то время как переменная типа record
может менять свою структуру на лету.
41.3.6. Упорядочение переменных PL/pgSQL #
Когда функция на PL/pgSQL имеет один или несколько параметров сортируемых типов данных, правило сортировки определяется при каждом вызове функции в зависимости от правил сортировки фактических аргументов, как описано в Разделе 22.2. Если оно определено успешно (т. е. среди аргументов нет конфликтов между неявными правилами сортировки), то все соответствующие параметры неявно трактуются как имеющее это правило сортировки. Внутри функции это будет влиять на поведение операторов, зависящих от используемого правила сортировки. Рассмотрим пример:
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ BEGIN RETURN a < b; END; $$ LANGUAGE plpgsql; SELECT less_than(text_field_1, text_field_2) FROM table1; SELECT less_than(text_field_1, text_field_2 COLLATE "C") FROM table1;
В первом случае less_than
будет использовать для сравнения общее правило сортировки для text_field_1
и text_field_2
, в то время как во втором случае будет использоваться правило C
.
Кроме того, определённое для вызова функции правило сортировки также будет использоваться для любых локальных переменных соответствующего типа. Таким образом, функция не станет работать по-другому, если её переписать так:
CREATE FUNCTION less_than(a text, b text) RETURNS boolean AS $$ DECLARE local_a text := a; local_b text := b; BEGIN RETURN local_a < local_b; END; $$ LANGUAGE plpgsql;
Если параметров с типами данных, поддерживающими сортировку, нет, или для параметров невозможно определить общее правило сортировки, тогда для параметров и локальных переменных применяются правила, принятые для их типа данных по умолчанию (которые обычно совпадают с правилами сортировки по умолчанию, принятыми для базы данных, но могут отличаться для переменных доменных типов).
Локальная переменная может иметь правило сортировки, отличное от правила по умолчанию. Для этого используется параметр COLLATE
в объявлении переменной, например:
DECLARE local_a text COLLATE "en_US";
Этот параметр переопределяет правило сортировки, которое получила бы переменная в соответствии с вышеуказанными правилами.
И, конечно же, можно явно указывать параметр COLLATE
для конкретных операций внутри функции, если к ним требуется применить конкретное правило сортировки. Например:
CREATE FUNCTION less_than_c(a text, b text) RETURNS boolean AS $$ BEGIN RETURN a < b COLLATE "C"; END; $$ LANGUAGE plpgsql;
Как и в обычной SQL-команде, это переопределяет правила сортировки, связанные с полями таблицы, параметрами и локальными переменными, которые используются в данном выражении.