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

Еще несколько примеров:

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

Как описано в Раздел 5.1.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, вместо этого для любого выходного параметра следует указать переменную; переменная получит результат вызова. См. Раздел 5.6.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 функция объявлена с полиморфным типом данных (см. Раздел 5.1.2.5), специальный параметр $0 создаётся. Его типом данных является фактический тип возвращаемого значения функции, выведенный на основе фактических типов входных параметров. Это позволяет функции обращаться к своему фактическому типу возвращаемого значения, как показано в Раздел 5.6.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 потребует явного приведения трех входных параметров к одному и тому же типу вручную.

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

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

Примеры:

DECLARE
  prior ALIAS FOR old;
  updated ALIAS FOR new;

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

имя таблица.столбец%TYPE
имя переменная%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];  -- эквивалентно приведенному выше

Как и при объявлении столбцов таблицы, являющихся массивами, не имеет значения, указывается ли несколько пар квадратных скобок или конкретные размеры массива: Digital Q.DataBase рассматривает все массивы данного типа элементов как один и тот же тип данных независимо от их размерности. (См. Раздел 2.5.15.1.)

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

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

имя table_name%ROWTYPE;
имя composite_type_name;

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

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

имя RECORD;

Переменные типа record подобны переменным строкового типа, но они не имеют заранее определенной структуры. Они принимают фактическую структуру той строки, которая присваивается им в процессе выполнения SELECT или FOR команды. Структура переменной типа record может меняться при каждом присваивании. Как следствие, до первого присваивания переменная типа record не имеет структуры, и любая попытка обращения к её полю приведет к ошибке времени выполнения.

Следует отметить, что RECORD не является полноценным типом данных, а служит лишь заполнителем. Также следует учитывать, что когда PL/pgSQL функция объявляется как возвращающая тип record, это понятие не совсем идентично переменной типа record, хотя такая функция может использовать переменную типа record для хранения своего результата. В обоих случаях фактическая структура строки неизвестна на момент написания функции, однако для функции, возвращающей record фактическая структура определяется при разборе вызывающего запроса, в то время как переменная типа record может менять структуру строк динамически.

Если PL/pgSQL функция имеет один или несколько параметров типов данных, поддерживающих правила сортировки, для каждого вызова функции определяется правило сортировки в зависимости от правил, назначенных фактическим аргументам, как описано в Раздел 3.8.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.