Все параметры, передаваемые в функции, именуются с помощью идентификаторов $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;

Несколько дополнительных примеров:

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 объявляется с выходными параметрами, выходным параметрам присваиваются имена $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

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

В вызове процедуры все параметры должны быть указаны. Для выходных параметров можно указать NULL при вызове процедуры из обычного SQL:

CALL sum_n_product(2, 4, NULL, NULL);
 sum | prod
-----+------
   6 |    8

Однако, при вызове процедуры из PL/pgSQL, вместо этого следует использовать переменную для любого выходного параметра; переменная будет получать результат вызова. См. Раздел 40.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 AS s
                 WHERE s.itemno = p_itemno;
END;
$$ LANGUAGE plpgsql;

Это точно эквивалентно объявлению одного или нескольких параметров OUT и указанию RETURNS SETOF sometype.

Когда возвращаемый тип функции PL/pgSQL объявлен как полиморфный тип (см. Раздел 35.2.5), создается специальный параметр $0. Его тип данных - фактический возвращаемый тип функции, определенный на основе фактических типов входных параметров. Это позволяет функции получить доступ к ее фактическому возвращаемому типу, как показано в Раздел 40.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);

будет работать, автоматически преобразуя целочисленные входные данные в числовые. Функция, использующая anyelement, потребует от вас вручную привести три входных значения к одному типу.

newname ALIAS FOR oldname;

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

Примеры:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

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

variable%TYPE

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

user_id users.user_id%TYPE;

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

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

name table_name%ROWTYPE;
name composite_type_name;

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

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