Объявления
Эта страница переведена при помощи нейросети GigaChat.
Все переменные, используемые в блоке, должны быть объявлены в разделе деклараций блока. (Единственным исключением является то, что переменная цикла FOR, выполняющего итерацию по диапазону целых значений, автоматически объявляется как целочисленная переменная, и точно так же переменная цикла FOR, выполняющего итерацию по результату курсора, автоматически объявляется как переменная записи).
Переменные PL/pgSQL могут иметь любой тип данных SQL, например integer, varchar и char.
Вот несколько примеров объявления переменных:
user_id integer;
quantity numeric(5);
url varchar;
myrow tablename%ROWTYPE;
myfield tablename.columnname%TYPE;
arow RECORD;
Общая синтаксическая структура объявления переменной следующая:
name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];
Предложение DEFAULT, если оно дано, указывает начальное значение, присваиваемое переменной при входе в блок. Если предложение DEFAULT не указано, то переменная инициализируется значением SQL NULL. Параметр CONSTANT предотвращает присвоение значения переменной после ее инициализации, так что ее значение останется постоянным в течение всего блока. Параметр COLLATE задает сопоставление для использования с переменной (см. раздел «Сопоставление переменных PL/pgSQL»). Если указан параметр NOT NULL, назначение нулевого значения приводит к ошибке во время выполнения. Все переменные, объявленные как NOT NULL, должны иметь указанное ненулевое значение по умолчанию. Знак равенства (=) можно использовать вместо совместимого с PL/SQL :=.
Значение по умолчанию оценивается и присваивается переменной каждый раз при входе в блок (не только один раз за вызов функции). Таким образом, например, присвоение now() переменной типа timestamp вызывает установку времени текущего вызова функции, а не времени, когда функция была предварительно скомпилирована.
Примеры:
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;
Объявление параметров функции
Параметры, передаваемые функциям, именуются идентификаторами $1, $2 и т.д. Дополнительно можно объявить псевдонимы для $n имен параметров для повышения читаемости. Затем для ссылки на значение параметра можно использовать псевдоним или числовой идентификатор.
Существует два способа создания псевдонима. Предпочтительный способ – присвоить имя параметру в команде CREATE FUNCTION, например:
CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
BEGIN
RETURN subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;
Другой способ – явно объявить псевдоним, используя синтаксис объявления:
name 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
-- some computations using v_string and index here
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; ей следует присвоить значение во время выполнения функции. Конечное значение параметра – это то, что возвращается. Например, пример налога с продаж также можно было бы сделать следующим образом:
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
Как обсуждалось в разделе «Функции SQL с выходными параметрами», это эффективно создает анонимный тип записи для результатов функции. Если указано предложение 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 следует вместо этого записать переменную для любого выходного параметра; переменная получит результат вызова. См. раздел «Вызов процедуры» для получения дополнительной информации.
Другой способ объявления функции 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 AS s
WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;
Это точно эквивалентно объявлению одного или нескольких OUT параметров и указанию RETURNS SETOF sometype.
Когда тип возврата функции PL/pgSQL объявлен как полиморфный тип (см. раздел «Полиморфные типы»), создается специальный параметр $0. Его тип данных - это фактический тип возврата функции, выведенный из фактических входных типов. Это позволяет функции получить доступ к ее фактическому типу возврата, как показано в разделе «Копирование типов». $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;
Тот же эффект может быть достигнут путем объявления одного или нескольких выходных параметров как полиморфных типов. В этом случае специальный параметр не используется; сами выходные параметры служат той же цели. Например:
CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement,
OUT sum anyelement)
AS $$
BEGIN
sum := v1 + v2 + v3;
END;
$$ LANGUAGE plpgsql;
На практике может оказаться более полезным объявить полиморфную функцию с использованием семейства типов , чтобы произошло автоматическое повышение входных аргументов до общего типа. Например:
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);
Будет работать, автоматически повышая целые входные данные до числовых. Функция, использующая anyelement, потребует приведения трех входных данных к одному типу вручную.
ALIAS
newname ALIAS FOR oldname;
Синтаксис ALIAS является более общим, чем предполагалось в предыдущем разделе: можно объявить псевдоним для любой переменной, а не только для параметров функции. Основное практическое применение этого заключается в назначении другого имени для переменных с предопределенными именами, такими как NEW или OLD внутри триггерной функции.
Примеры:
DECLARE
prior ALIAS FOR old;
updated ALIAS FOR new;
Поскольку ALIAS создает два разных способа назвать один и тот же объект, неограниченное использование может быть запутанным. Лучше всего использовать его только для целей переопределения предопределенных имен.
Наследование типов данных
name table.column%TYPE
name variable%TYPE
%TYPE предоставляет тип данных столбца таблицы или ранее объявленной переменной PL/pgSQL. можно использовать это для объявления переменных, которые будут хранить значения базы данных. Например, предположим, есть столбец под названием user_id в таблице users. Чтобы объявить переменную с тем же типом данных, что и users.user_id, напишите:
user_id users.user_id%TYPE;
Также после %TYPE можно написать массивное оформление, создавая таким образом переменную, которая хранит массив указанного типа:
user_ids users.user_id%TYPE[];
user_ids users.user_id%TYPE ARRAY[4]; -- equivalent to the above
Как и при объявлении колонок таблиц, являющихся массивами, неважно, пишете ли несколько пар скобок или конкретные размеры массива: PostgreSQL рассматривает все массивы данного элементарного типа как один и тот же тип независимо от размерности. (См. раздел 8.15.1.)
Используя %TYPE, не нужно знать тип данных структуры, на которую ссылаетесь, и самое главное, если тип данных ссылочного элемента изменится в будущем (например, изменится тип user_id с integer на real), возможно, не придется изменять определение своей функции.
%TYPE особенно полезен в полиморфных функциях, поскольку необходимые внутренние типы данных могут меняться от одного вызова к другому. Соответствующие переменные можно создать путем применения %TYPE к аргументам функции или заполнителям результата.
Типы кортежей
name table_name%ROWTYPE;
name composite_type_name;
Переменная составного типа называется переменной строки (или переменной типа строки). Такая переменная может содержать целую строку результата запроса к таблице или представлению, если набор столбцов этого запроса соответствует объявленному типу переменной. Отдельные поля значения строки доступны с использованием обычной нотации с точкой, например rowvar.field.
Переменную строки можно объявить того же типа, что и строки существующей таблицы или представления, используя нотацию table_name%ROWTYPE, или она может быть объявлена путем указания имени составного типа. (Поскольку у каждой таблицы есть связанный с ней составной тип с тем же именем, фактически не имеет значения в PostgreSQL, пишете ли %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 ... ;
Типы записей
name RECORD;
Переменные записи похожи на переменные типа строки, но у них нет заранее определенной структуры. Они принимают фактическую структуру строки, которой они назначаются во время команды SELECT или FOR. Подструктура переменной записи может изменяться каждый раз при ее назначении. Следствием этого является то, что до тех пор, пока переменная записи не будет впервые назначена, она не имеет подструктуры, и любая попытка получить доступ к полю в ней приведет к ошибке времени выполнения.
Обратите внимание, что RECORD не является истинным типом данных, а лишь заполнителем. Также следует понимать, что когда функция PL/pgSQL объявляется для возврата типа record, это не совсем та же концепция, что и переменная записи, даже если такая функция может использовать переменную записи для хранения своего результата. В обоих случаях фактическая структура строки неизвестна при написании функции, но для функции, возвращающей record, фактическая структура определяется при синтаксическом анализе вызывающего запроса, тогда как переменная записи может изменять свою структуру строки на лету.
Сопоставление переменных PL/pgSQL
Когда у функции PL/pgSQL есть один или несколько параметров типов данных с возможностью сопоставления, для каждого вызова функции определяется сопоставление в зависимости от сопоставлений, назначенных фактическим аргументам, как описано в разделе «Поддержка сопоставления». Если сопоставление успешно идентифицировано (т.е. среди аргументов нет конфликтов неявных сопоставлений), то все параметры, допускающие сопоставление, рассматриваются как имеющие это сопоставление неявно. Это повлияет на поведение операций, чувствительных к сопоставлению, внутри функции. Например:
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-команде, это заменяет сортировки, связанные со столбцами таблицы, параметрами или локальными переменными, используемыми в выражении.