Объявления

Все переменные, используемые в блоке, должны быть объявлены в разделе объявлений этого блока. (Единственное исключение состоит в том, что переменная цикла 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;

Общий синтаксис объявления переменной:

имя [ CONSTANT ] тип [ COLLATE имя_правила_сортировки ] [ NOT NULL ] [ { DEFAULT | := | = } выражение ];

Предложение DEFAULT, если таковое имеется, задает начальное значение, назначенное переменной при вводе блока. Если предложение DEFAULT не указано, переменная инициализируется SQL-значением NULL. Параметр CONSTANT предотвращает присвоение переменной после инициализации, чтобы ее значение оставалось постоянным на протяжении всего блока. Параметр COLLATE задает для этой переменной применяемое правило сортировки (см. подраздел Правило сортировки переменных PL/pgSQL). Если указано NOT NULL, то присвоение значения NULL вызовет ошибку во время выполнения. Все переменные, объявленные как NOT NULL, должны иметь значение по умолчанию, отличное от NULL. Вместо PL/SQL-совместимого сочетания := можно использовать просто знак равенства (=).

Значение переменной по умолчанию вычисляется и присваивается переменной каждый раз, когда вводится блок (а не только один раз за вызов функции). Так, например, присвоение функции now() переменной типа timestamp приведет к тому, что эта переменная будет содержать время текущего вызова функции, а не время, когда функция была предварительно скомпилирована.

Примеры:

quantity integer DEFAULT 32;
url varchar := 'http://mysite.com';
user_id CONSTANT integer := 10;

После объявления переменной ее значение можно использовать в последующих выражениях инициализации в том же блоке, например:

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;

Другой способ — явно объявить псевдоним, используя следующий синтаксис объявления:

имя 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 объявляется с выходными параметрами, им даются имена $n и необязательные псевдонимы точно так же, как обычным входным параметрам. Выходной параметр по сути является переменной, которая начинается с 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 некий_тип.

Когда возвращаемый тип функции 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;

Тот же эффект можно получить, объявив один или несколько выходных параметров как имеющие полиморфный тип. В этом случае специальный параметр $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);

автоматически преобразовав целочисленные аргументы в числовые. В функции, использующей anyelement, потребовалось бы приводить все три аргумента к одному вручную.

ALIAS

новое_имя ALIAS FOR старое_имя;

Синтаксис ALIAS является более общим, чем предполагается в предыдущем подразделе: псевдоним можно объявить для любой переменной, а не только для параметров функции. Основное практическое применение этого состоит в назначении другого имени для переменных с предопределенными именами, например, NEW или OLD в триггерной функции.

Примеры:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

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

Копирование типов

переменная%TYPE

%TYPE предоставляет тип данных переменной или столбца таблицы. Это можно использовать для объявления переменных, которые будут содержать значения базы данных. Например, предположим, что у вас есть столбец с именем user_id в таблице users. Чтобы объявить переменную с тем же типом данных, что и users.user_id вы пишете:

user_id users.user_id%TYPE;

Используя %TYPE, необязательно знать тип данных структуры, на которую вы ссылаетесь, и, самое главное, если тип данных ссылочного элемента в будущем изменится (например, вы поменяете тип user_id с integer на real), возможно, вам не понадобится менять определение функции.

Конструкция %TYPE особенно полезна в полиморфных функциях, поскольку типы данных, необходимые для внутренних переменных, могут меняться от вызова к вызову. Подходящие переменные можно создать путем применения %TYPE к аргументам функции или местозаполнителям результата.

Типы строки

имя имя_таблицы%ROWTYPE;
имя имя_составного_типа;

Переменная составного типа называется переменной строки (или переменной типа строки). Такая переменная может содержать целую строку результата запроса SELECT или FOR, если набор столбцов этого запроса соответствует объявленному типу переменной. Доступ к отдельным полям значения строки осуществляется с помощью обычной нотации с точкой, например rowvar.field.

Переменную строки можно объявить с тем же типом, что и строки существующей таблицы или представления, используя нотацию имя_таблицы%ROWTYPE, либо путем указания имени составного типа. (Поскольку каждая таблица имеет связанный составной тип с тем же именем, в QHB не имеет значения, пишете ли вы %ROWTYPE или нет. Но форма с %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 ... ;

Типы записи

имя 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.

Copyright © KVANTOM LLC, 2019-2025