В теле SQL-функции на ее аргументы можно ссылаться как по именам, так и по номерам. Примеры обоих способов приведены ниже.

Чтобы использовать обращение по имени, укажите имя аргумента при его объявлении, а затем используйте это имя в теле функции. Если имя аргумента совпадает с именем какого-либо столбца в SQL-команде внутри функции, приоритет будет иметь имя столбца. Чтобы переопределить это поведение, добавьте к имени аргумента имя самой функции в качестве квалификатора, то есть function_name.argument_name. (Если это приведет к конфликту с квалифицированным именем столбца, приоритет снова будет иметь имя столбца. Избежать неоднозначности можно, выбрав для таблицы в SQL-команде другой псевдоним.)

В рамках устаревшего подхода с нумерацией обращение к аргументам осуществляется при помощи синтаксиса $n: $1 относится к первому входному аргументу, $2 ко второму и так далее. Данный механизм будет работать независимо от того, было ли объявлено имя для конкретного аргумента.

Если аргумент имеет составной тип данных, то точечная нотация, например, argname.fieldname или $1.fieldname, может использоваться для доступа к атрибутам данного аргумента. Кроме того, может потребоваться уточнить имя аргумента именем функции, чтобы сделать обращение по имени аргумента однозначным.

Аргументы SQL-функции могут использоваться только в качестве значений данных, но не в качестве идентификаторов. Например, корректным будет следующий вариант:

INSERT INTO mytable VALUES ($1);

но следующий вариант работать не будет:

INSERT INTO $1 VALUES (42);

Самая простая SQL функция не имеет аргументов и просто возвращает базовый тип данных, такой как integer:

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS result;
$$ LANGUAGE SQL;

-- Alternative syntax for string literal:
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS result;
' LANGUAGE SQL;

SELECT one();

 one
-----
   1

Обратите внимание, что внутри тела функции для возвращаемого результата был определён псевдоним столбца (с использованием типа данных name result), однако данный псевдоним не виден вне контекста функции. Следовательно, результат помечается one вместо result.

Определить следующие функции почти так же просто SQL функции, которые принимают базовые типы данных в качестве аргументов:

CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
    SELECT x + y;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

В качестве альтернативы можно отказаться от использования имен аргументов и указывать их номера:

CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

Ниже приведен пример более полезной функции, которая может применяться для дебетования банковского счета:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT 1;
$$ LANGUAGE SQL;

Пользователь может вызвать данную функцию для дебетования счета 17 на сумму $100.00 следующим образом:

SELECT tf1(17, 100.0);

В данном примере для первого аргумента было выбрано имя accountno для первого аргумента, но это имя совпадает с именем столбца в банк таблице. Внутри UPDATE команды, accountno это имя ссылается на столбец bank.accountno, поэтому tf1.accountno должно использоваться для обращения к аргументу. Разумеется, этой ситуации можно избежать, выбрав для аргумента другое имя.

На практике от функции обычно требуется более полезный результат, чем константа 1, поэтому более вероятное определение выглядит так:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT balance FROM bank WHERE accountno = tf1.accountno;
$$ LANGUAGE SQL;

эта функция корректирует баланс и возвращает его новое значение. То же самое можно сделать одной командой, используя RETURNING:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

Если последнее SELECT или RETURNING предложение в SQL функции возвращает значение, тип данных которого не совпадает в точности с объявленным типом результата, Digital Q.DataBase система автоматически выполнит приведение значения к требуемому типу данных, если это возможно посредством неявного приведения или приведения при присваивании. В противном случае необходимо использовать явное приведение типов. Например, если требуется, чтобы предыдущая функция add_em функция должна возвращать тип данных float8 instead. В этом случае достаточно написать

CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

поскольку integer тип данных суммы может быть неявно приведен к float8. (Подробнее о приведении типов см. Глава 2.7 или CREATE CAST for more about casts.)

При написании функции с аргументами составных типов необходимо указывать не только порядковый номер аргумента, но и требуемый атрибут (поле) этого аргумента. Например, предположим, что emp — это таблица, содержащая данные о сотрудниках, название которой также является именем составного типа для каждой строки этой таблицы. Ниже представлена функция, double_salary вычисляющая, какой была бы заработная плата сотрудника при ее удвоении:

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

INSERT INTO emp VALUES ('Билл', 4200, 45, '(2,1)');

CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Билл |  8400

Обратите внимание на использование синтаксиса $1.salary для выбора одного поля из значения строки-аргумента. Также обратите внимание на то, как в вызывающей SELECT команде используется table_name.* для выбора всей текущей строки таблицы в качестве составного значения. На строку таблицы также можно сослаться, используя только имя таблицы, например:

SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

однако такое использование не рекомендуется, так как оно может привести к путанице. (См. Раздел 2.5.16.5 для получения подробных сведений об этих двух вариантах записи составного значения строки таблицы.)

Иногда бывает удобно сформировать составное значение аргумента непосредственно в процессе выполнения. Это можно сделать с помощью ROW конструкции. Например, можно скорректировать данные, передаваемые в функцию:

SELECT name, double_salary(ROW(name, salary*1.1, age, cubicle)) AS dream
    FROM emp;

Также можно создать функцию, возвращающую составной тип данных. Ниже приведен пример функции, которая возвращает одну emp строку:

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 с ключевым словом AS age,
        point '(2,2)' с ключевым словом AS cubicle;
$$ предложение LANGUAGE SQL;

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

При определении функции следует обратить внимание на два важных аспекта:

Эту же функцию можно определить иным способом:

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

Здесь мы определили функцию, SELECT которая возвращает всего один столбец соответствующего составного типа. В данной ситуации такой подход не дает особых преимуществ, но в некоторых случаях это удобная альтернатива — например, если нам нужно получить результат путем вызова другой функции, возвращающей требуемое составное значение. Другой пример: если мы пишем функцию, которая должна возвращать домен на базе составного типа, а не просто составной тип, ее всегда необходимо определять как возвращающую один столбец, так как не существует способа принудительно выполнить приведение типа для всей строки результата целиком.

Эту функцию можно вызвать напрямую, использовав ее в выражении:

SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

или вызвав ее как табличную функцию:

SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

Второй способ более подробно описан в Раздел 5.1.5.8.

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

SELECT (new_emp()).name;

 name
------
 None

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

SELECT new_emp().name;
ERROR:  syntax error at or near "."
LINE 1: SELECT new_emp().name;
                        ^

Еще один вариант — использование функциональной записи для извлечения атрибута:

SELECT name(new_emp());

 name
------
 None

Как поясняется в Раздел 2.5.16.5, использование имен полей и функциональная запись являются эквивалентными.

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

CREATE FUNCTION getname(emp) RETURNS text AS $$
    SELECT $1.name;
$$ LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

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

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT x + y'
LANGUAGE SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

Данный вариант по существу не отличается от версии функции add_em описанной в Раздел 5.1.5.2. Практическая значимость выходных параметров заключается в том, что они предоставляют удобный способ определения функций, возвращающих несколько столбцов. Например:

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT x + y, x * y'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462
(1 строка)

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

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

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

Учтите, что при вызове такой функции с помощью SQL-команд выходные параметры не включаются в список фактических аргументов. Это обусловлено тем, что Digital Q.DataBase учитывает только входные параметры для определения сигнатуры вызова функции. Это также означает, что при указании ссылки на функцию, например для её удаления, значение имеют только входные параметры. Вышеуказанную функцию можно удалить любой из следующих команд:

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);

Параметры функции могут быть помечены как IN (по умолчанию), OUT, INOUT, либо ключевое слово VARIADIC. Параметр типа INOUT служит одновременно входным параметром (частью списка аргументов вызова) и выходным параметром (частью результирующего типа записи). ключевое слово VARIADIC параметры являются входными, но обрабатываются особым образом, согласно приведённому ниже описанию.

Выходные параметры также поддерживаются в процедурах, но они работают несколько иначе, чем в функциях. В CALL командах выходные параметры должны быть включены в список аргументов. Например, процедуру для операции дебет банковского счета, упомянутую ранее, можно было бы написать следующим образом:

CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tp1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

Для вызова этой процедуры необходимо передать аргумент, соответствующий OUT этому параметру. Как правило, вызов записывается так: значение NULL:

CALL tp1(17, 100.0, NULL);

Если вы указываете иное значение, оно должно быть выражением, которое неявно приводится к объявленному типу данных этого параметра, аналогично входным параметрам. Однако следует учитывать, что такое выражение не будет вычисляться.

При вызове процедуры из PL/pgSQL, вместо указания значение NULL необходимо использовать переменную, в которую будет записан выходной результат процедуры. См. Раздел 5.6.6.3 для получения подробных сведений.

SQL функции могут быть объявлены как принимающие переменное число аргументов, при условии, что все «необязательный» аргументы имеют один и тот же тип данных. Необязательные аргументы передаются в функцию в виде массива. Функция объявляется путем пометки последнего параметра с помощью ключевое слово VARIADIC; данный параметр должен иметь тип массива. Например:

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);
 mleast
--------
     -1
(1 row)

Фактически все фактические аргументы, начиная с указанной ключевое слово VARIADIC позиции, объединяются в одномерный массив, как если бы было указано

SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- doesn't work

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

Иногда бывает полезно передать уже сформированный массив в функцию с ключевым словом VARIADIC; это особенно удобно, когда одна функция с ключевым словом VARIADIC должна передать свой параметр-массив другой функции. Кроме того, это единственный безопасный способ вызова функции с ключевым словом VARIADIC в схеме, где непривилегированным пользователям разрешено создавать объекты; см. Раздел 2.7.3. Это можно сделать, указав ключевое слово VARIADIC в вызове функции:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

Это предотвращает развертывание вариативного параметра функции в его базовый тип данных, что позволяет аргументу-массиву сопоставляться обычным образом. ключевое слово VARIADIC может быть указан только для последнего фактического аргумента при вызове функции.

Указание ключевого слова VARIADIC ключевое слово VARIADIC при вызове также является единственным способом передать пустой массив в функцию с переменным количеством аргументов, например:

SELECT mleast(VARIADIC ARRAY[]::numeric[]);

Простой вызов SELECT mleast() не будет работать, так как параметр VARIADIC должен соответствовать как минимум одному фактическому аргументу. (При необходимости можно определить вторую функцию с тем же именем mleast, не имеющую параметров, если требуется разрешить такие вызовы.)

Параметры элементов массива, сформированные из параметра VARIADIC, не имеют собственных имен. Это означает, что вызвать функцию с переменным количеством аргументов при помощи именованных аргументов нельзя (Раздел 2.1.3), за исключением случаев, когда указано ключевое слово VARIADIC ключевое слово VARIADIC. Например, следующая команда будет работать:

SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);

но не эти команды:

SELECT mleast(arr => 10);
SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);

Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются в тех случаях, когда вызов функции выполняется с недостаточным количеством фактических аргументов. Поскольку аргументы могут быть опущены только в конце списка фактических аргументов, все параметры, следующие за параметром со значением по умолчанию, также должны иметь значения по умолчанию. (Хотя использование именованной нотации аргументов позволило бы смягчить это ограничение, оно по-прежнему соблюдается для обеспечения корректной работы позиционной нотации). Независимо от её использования, данная возможность требует соблюдения мер предосторожности при вызове функций в базах данных, где пользователи могут не доверять друг другу; см. Раздел 2.7.3.

Например:

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS $$
    SELECT $1 + $2 + $3;
$$;

SELECT foo(10, 20, 30);
 foo
-----
  60
(1 row)

SELECT foo(10, 20);
 foo
-----
  33
(1 row)

SELECT foo(10);
 foo
-----
  15
(1 row)

SELECT foo();  -- завершается ошибкой, так как для первого аргумента не задано значение по умолчанию
ОШИБКА:  функция foo() не существует

Символ = также может использоваться вместо ключевого слова DEFAULT.

Все SQL-функции могут использоваться в предложении FROM предложение запроса, однако это особенно полезно для функций, возвращающих составные типы. Если функция определена для возврата базового типа данных, табличная функция формирует таблицу, состоящую из одного столбца. Если функция определена как возвращающая составной тип, табличная функция формирует по одному столбцу для каждого атрибута этого типа.

Ниже приведен пример:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

Как показано в данном примере, со столбцами результата функции можно работать точно так же, как со столбцами обычной таблицы.

Обратите внимание, что функция возвратила только одну строку. Это объясняется тем, что не было указано ключевое слово SETOF. Этот механизм описан в следующем разделе.

Когда SQL-функция объявляется как возвращающая тип данных SETOF sometype, последний запрос функции выполняется до конца, и каждая возвращаемая им строка становится элементом результирующего набора.

Данная функциональность обычно используется при вызове функции в предложении FROM FROM В этом случае каждая строка, возвращаемая функцией, становится строкой таблицы, представленной в запросе. Например, если предположить, что таблица foo имеет то же содержимое, что и в примере выше, и выполняется команда:

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

В результате будет получено следующее:

 fooid | foosubid | fooname
-------+----------+---------
     1 |        1 | Joe
     1 |        2 | Ed
(2 rows)

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

CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)

Ключевым моментом здесь является то, что необходимо указать RETURNS SETOF record чтобы указать, что функция возвращает несколько строк вместо одной. Если предусмотрен только один выходной параметр, укажите тип данных этого параметра вместо record.

Зачастую результат запроса целесообразно формировать путем многократного вызова функции, возвращающей набор данных, когда параметры для каждого вызова выбираются из строк таблицы или подзапроса. Для этого рекомендуется использовать LATERAL ключевое слово, которое описано в Раздел 2.4.2.1.5. Ниже приведен пример использования функции, возвращающей набор данных, для получения списка элементов древовидной структуры:

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;

SELECT * FROM listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

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

Функции, возвращающие набор данных, также могут быть вызваны в списке SELECT запроса. Для каждой строки, генерируемой самим запросом, вызывается функция, возвращающая набор данных, и для каждого элемента результирующего набора функции формируется выходная строка. Предыдущий пример также можно реализовать с помощью следующих запросов:

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 строки)

SELECT name, listchildren(name) FROM nodes;
  тип данных name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

В последнем примере SELECT, обратите внимание, что выходные строки отсутствуют для Child2, Child3и т. д. Это происходит потому, что функция listchildren возвращает пустой набор данных для этих аргументов, поэтому результирующие строки не формируются. Данное поведение аналогично результату внутреннего соединения с функцией при использовании LATERAL синтаксиса.

Поведение Digital Q.DataBase для функции, возвращающей набор данных в списке выборки запроса, практически идентично случаю, когда функция, возвращающая набор данных, указана в LATERAL FROM предложении. Например,

SELECT x, generate_series(1,5) AS g FROM tab;

почти эквивалентно

SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;

Результат был бы идентичным, за исключением того, что в данном конкретном примере планировщик мог бы поместить g во внешнюю часть соединения вложенным циклом, так как g не имеет фактической зависимости LATERAL от tab. Это привело бы к иному порядку строк в выходном наборе данных. Функции, возвращающие набор данных, в списке выбора SELECT всегда вычисляются так, будто они находятся во внутренней части соединения вложенным циклом с остальной частью FROM предложения, что обеспечивает выполнение функций до завершения перед переходом к следующей строке FROM рассматривается предложение.

Если в списке выбора SELECT запроса указано несколько функций, возвращающих набор данных, поведение будет аналогично помещению этих функций в один LATERAL ROWS FROM( ... ) FROMэлемент предложения. Для каждой строки базового запроса формируется выходная строка, содержащая первый результат каждой функции, затем выходная строка со вторым результатом и так далее. Если некоторые функции, возвращающие набор данных, выводят меньше строк, чем другие, то вместо отсутствующих данных подставляются значения NULL; таким образом, общее количество строк, сформированных для одной базовой строки, соответствует результату функции с наибольшим количеством возвращаемых строк. Таким образом, функции, возвращающие набор данных, выполняются «синхронно» до тех пор, пока все они не будут исчерпаны, после чего выполнение продолжается для следующей исходной строки.

Функции, возвращающие набор данных, могут быть вложенными в список выбора, хотя это не допускается в FROMэлементах предложения. В таких случаях каждый уровень вложенности рассматривается отдельно, как если бы он представлял собой отдельный LATERAL ROWS FROM( ... ) элемент. Например, в команде

SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;

функции, возвращающие набор данных srf2, srf3, и srf5 будут выполняться синхронно для каждой строки отношения tab, а затем функции srf1 и srf4 будут применяться синхронно к каждой строке, возвращаемой функциями нижестоящего уровня.

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

SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;

Может показаться, что это должно привести к пятикратному повторению тех входных строк, которые имеют x > 0, и к однократному повторению тех, для которых это условие не выполняется; но на самом деле, поскольку функция generate_series(1, 5) будет выполнена в неявном LATERAL FROM элементе списка до того, как CASE выражение будет вычислено, она создаст пять повторений для каждой входной строки. Во избежание путаницы в подобных случаях выдается ошибка на этапе синтаксического анализа.

SELECT x, CASE WHEN y > 0 THEN generate_series(1, z) ELSE 5 END FROM tab;

CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int)
  RETURNS SETOF int AS $$
BEGIN
  IF cond THEN
    RETURN QUERY SELECT generate_series(start, fin);
  ELSE
    RETURN QUERY SELECT els;
  END IF;
END$$ LANGUAGE plpgsql;

SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;

Существует еще один способ объявить функцию как возвращающую набор данных, который заключается в использовании синтаксиса RETURNS TABLE(columns). Это эквивалентно использованию одного или нескольких выходных OUT параметров с одновременным определением функции как возвращающей SETOF record (или SETOF тип данных одного выходного параметра, если это применимо). Данная нотация определена в последних версиях стандарта SQL и, следовательно, может быть более переносимой, чем использование SETOF.

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

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

При использовании нотации OUT или INOUT не допускается указывать явные выходные параметры RETURNS TABLE notation — все выходные столбцы должны быть перечислены в списке TABLE list.

SQL функции могут быть объявлены как принимающие и возвращающие полиморфные типы, описанные в Раздел 5.1.2.5. Ниже приведена полиморфная функция, make_array которая формирует массив из двух элементов произвольных типов данных:

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

Обратите внимание на использование приведения типов 'a'::text для указания того, что аргумент имеет тип данных text. Это необходимо, если аргумент представляет собой просто строковый литерал, так как в противном случае он будет восприниматься как тип данных unknown, а массив из элементов типа данных unknown не является допустимым типом. Без приведения типов возникнет ошибка, подобная следующей:

ОШИБКА: не удалось определить полиморфный тип, так как входной аргумент имеет тип данных unknown

Если функция make_array объявлена так, как описано выше, необходимо указать два аргумента в точности одного и того же типа данных; система не будет пытаться разрешить какие-либо различия в типах данных. Таким образом, например, следующий вызов не будет работать:

SELECT make_array(1, 2.5) AS numericarray;
ОШИБКА: функция make_array(integer, numeric) не существует

Альтернативный подход заключается в использовании «общих» семейство полиморфных типов, которое позволяет системе определить подходящий общий тип данных:

CREATE FUNCTION make_array2(anycompatible, anycompatible)
RETURNS anycompatiblearray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array2(1, 2.5) AS numericarray;
 numericarray
--------------
 {1,2.5}
(1 строка)

Поскольку правила определения общего типа данных по умолчанию выбирают тип данных text в случаях, когда типы всех входных аргументов не определены, это также работает:

SELECT make_array2('a', 'b') AS textarray;
 textarray
-----------
 {a,b}
(1 row)

Допускается использование полиморфных аргументов с фиксированным типом возвращаемого значения, но обратное не допускается. Например:

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.

Механизм полиморфизма может применяться в функциях, имеющих выходные аргументы. Например:

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

Полиморфизм также может использоваться в функциях с ключевым словом VARIADIC. Например:

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);
 anyleast
----------
       -1
(1 row)

SELECT anyleast('abc'::text, 'def');
 anyleast
----------
 abc
(1 row)

CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
    SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;

SELECT concat_values('|', 1, 4, 2);
 concat_values
---------------
 1|4|2
(1 row)

Когда SQL-функция имеет один или несколько параметров типов данных, поддерживающих правила сортировки, для каждого вызова функции определяется правило сортировки в зависимости от правил сортировки, назначенных фактическим аргументам, как описано в Раздел 3.8.2. Если правило сортировки успешно определено (т. е. отсутствуют конфликты неявных правил сортировки между аргументами), то все сопоставимые параметры неявно рассматриваются как имеющие это правило сортировки. Это повлияет на работу операций внутри функции, чувствительных к правилам сортировки. Например, при использовании функции anyleast , описанной выше, результат команды

SELECT anyleast('abc'::text, 'ABC');

будет зависеть от правила сортировки, используемого в базе данных по умолчанию. В C локали результатом будет ABC, но во многих других локалях это будет abc. Применяемое правило сортировки можно переопределить, добавив предложение COLLATE к любому из аргументов, например:

SELECT anyleast('abc'::text, 'ABC' COLLATE "C");

Если же необходимо, чтобы функция работала с конкретным правилом сортировки независимо от параметров вызова, добавьте COLLATE предложения в определении функции по мере необходимости. Данная версия anyleast всегда будет использовать en_US локаль для сравнения строк:

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

Однако следует учитывать, что это приведет к ошибке при применении к типу данных, не поддерживающему правило сортировки.

Если среди фактических аргументов не удается определить общее правило сортировки, SQL-функция обрабатывает свои параметры как имеющие правило сортировки по умолчанию для их типов данных (обычно это правило сортировки базы данных по умолчанию, но для параметров типов доменов оно может быть иным).

Поведение параметров, поддерживающих правила сортировки, можно рассматривать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.