Функции на языке запросов (SQL)

примечание

Эта страница переведена при помощи нейросети GigaChat.

Функции SQL выполняют произвольный список операторов SQL, возвращая результат последнего запроса в списке. В простом (не множественном) случае будет возвращена первая строка результата последнего запроса. Учтите, что «первая строка» многострочного результата не определена, если не используете ORDER BY. Если последний запрос вообще не возвращает ни одной строки, возвращается значение null.

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

Тело функции SQL должно быть списком операторов SQL, разделенных точками с запятой. Точка с запятой после последнего оператора необязательна. Если функция не объявлена для возврата void, последний оператор должен быть оператором SELECT, или оператором INSERT, UPDATE, DELETE, MERGE, который имеет предложение RETURNING.

Любая коллекция команд на языке SQL может быть объединена и определена как функция. Помимо запросов SELECT, команды могут включать запросы модификации данных (INSERT, UPDATE, DELETE, и MERGE), а также другие команды SQL. Не получится использовать команды управления транзакциями, например, COMMIT, SAVEPOINT, и некоторые служебные команды, такие как VACUUM, в функциях SQL. Однако последняя команда должна быть командой SELECT или иметь предложение RETURNING, которое возвращает то, что указано как тип возвращаемого значения функции. В качестве альтернативы, если хотите определить функцию SQL, которая выполняет действия, но не имеет полезного значения для возврата, можно определить ее как возвращающую void. Например, эта функция удаляет строки с отрицательной заработной платой из таблицы emp:

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------

(1 row)

Также можно написать это как процедуру, тем самым избегая проблемы типа возврата. Например:

CREATE PROCEDURE clean_emp() AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

CALL clean_emp();

В простых случаях, таких как этот, разница между функцией, возвращающей void, и процедурой в основном стилистическая. Тем не менее, процедуры предлагают дополнительную функциональность, такую как управление транзакциями, которая недоступна в функциях. Кроме того, процедуры являются стандартом SQL, тогда как возврат void является расширением PostgreSQL.

Примечание

Весь код SQL-функции анализируется перед его выполнением. Хотя SQL-функция может содержать команды, изменяющие системные каталоги (например, CREATE TABLE), эффекты таких команд не будут видны во время анализа последующих команд функции. Таким образом, например, CREATE TABLE foo (...); INSERT INTO foo VALUES(...); не будет работать так, как задумано, если упаковать его в одну SQL-функцию, поскольку foo еще не существует при разборе команды INSERT. Рекомендуется использовать PL/pgSQL вместо SQL-функции в таких ситуациях.

Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было написано как строковая константа. Обычно наиболее удобно использовать долларовую нотацию (см. Раздел 4.1.2.4) для строковой константы. Если требуется использовать обычный синтаксис одинарной кавычки для строковой константы, то необходимо удвоить одиночные кавычки (') и обратные косые черты (\) (предполагая синтаксис строки с экранированием) в теле функции (см. Раздел 4.1.2.1).

Аргументы функций SQL

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

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

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

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

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

INSERT INTO mytable VALUES ($1);

но это не сработает:

INSERT INTO $1 VALUES (42);

Примечание

Возможность использовать имена для ссылки на аргументы функции SQL была добавлена в PostgreSQL версии 9.2. Функции, которые должны использоваться на более старых серверах, должны использовать нотацию $n.

Функции SQL для базовых типов

Самая простая возможная функция 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

Обратите внимание, что определен псевдоним столбца внутри тела функции для результата функции (с именем 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 для первого аргумента, но это то же самое, что и имя столбца в таблице bank. В команде UPDATE accountno относится к столбцу bank.accountno, поэтому tf1.accountno должен использоваться для ссылки на аргумент. Конечно, можно было бы избежать этого, используя другое имя для аргумента.

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

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 не возвращает точно объявленный тип результата функции, PostgreSQL автоматически преобразует значение в требуемый тип, если это возможно с помощью явного или присваиваемого приведения. В противном случае надо написать явное приведение. Например, предположим, что требуется, чтобы предыдущая функция add_em возвращала тип float8 вместо этого. Достаточно написать:

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

Поскольку integer сумма может быть неявно приведена к float8.

Функции SQL для составных типов

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

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

INSERT INTO emp VALUES ('Bill', 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
------+-------
 Bill |  8400

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

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

Но это использование устарело, так как легко запутаться.

Иногда удобно конструировать составное значение аргумента на лету. Это можно сделать с помощью конструкции 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;

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

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

• Порядок списка выбора в запросе должен быть точно таким же, как и порядок, в котором столбцы появляются в составном типе (названия столбцов не имеют значения для системы).

• Нужно убедиться, что каждый тип выражения может быть приведен к типу соответствующего столбца составного типа. В противном случае получим ошибки, подобные этой:

  ERROR:  return type mismatch in function declared to return emp
  DETAIL:  Final statement returns text instead of point at column 4.

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

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

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)

Второй способ описан более подробно в разделе «Функции SQL в качестве источников таблиц».

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

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

Нотация поля и функциональная нотация эквивалентны.

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

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

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

Функции SQL с выходными параметрами

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

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, показанной в разделе «Функции SQL для базовых типов». Настоящая ценность выходных параметров заключается в том, что они предоставляют удобный способ определения функций, возвращающих несколько столбцов. Например:

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 row)

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

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. Это связано с тем, что PostgreSQL рассматривает только входные параметры для определения сигнатуры вызова функции. Это также означает, что только входные параметры имеют значение при ссылке на функцию для таких целей, как ее удаление. Можно было бы удалить вышеуказанную функцию любой из следующих команд:

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

Процедуры SQL с выходными параметрами

Выходные параметры также поддерживаются в процедурах, но они работают немного иначе, чем функции. В командах 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 нужно записать переменную, которая будет принимать вывод процедуры. См. раздел «Управляющие структуры» для получения подробной информации.

Функции SQL с переменным количеством аргументов

Функции 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 при вызове:

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

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

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

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

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

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

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

Но не эти:

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

Функции SQL со значениями по умолчанию для аргументов

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

Например:

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();  -- fails since there is no default for the first argument
ERROR:  function foo() does not exist

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

Функции SQL в качестве источников таблиц

Все функции 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, возвращающие наборы

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

Эта функция обычно используется при вызове функции в 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. Вот пример использования функции, возвращающей набор значений, для перечисления элементов структуры дерева:

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 listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

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.

Поведение PostgreSQL для функции, возвращающей набор, в списке выборки запроса почти точно такое же, как если бы функция, возвращающая набор, была записана в элементе предложения 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 не имеет фактической боковой зависимости от tab. Это привело бы к другому порядку вывода строки. Функции, возвращающие набор значений, в списке выбора всегда оцениваются так, как если бы они находились внутри вложенного цикла с остальной частью предложения FROM, чтобы функция (ы) была выполнена до завершения перед тем, как будет рассмотрена следующая строка из предложения FROM.

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

Функции, возвращающие наборы значений, могут быть вложены в список выбора, хотя это не допускается в элементах предложения 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 когда-либо будет оценено, он приведет к созданию пяти копий каждой входной строки. Чтобы уменьшить путаницу, такие случаи приводят к ошибке анализа во время синтаксического разбора.

Примечание:

Если последней командой функции является INSERT, UPDATE, DELETE, MERGE с RETURNING, эта команда всегда будет выполнена до завершения, даже если функция не объявлена с SETOF или вызывающий запрос не извлекает все строки результата. Любые дополнительные строки, произведенные предложением RETURNING, молча отбрасываются, но изменения таблицы, указанные в команде, все равно происходят (и завершаются перед возвратом из функции).

Примечание:

До версии PostgreSQL 10 размещение более одной функции, возвращающей набор, в одном списке выбора не работало очень осмысленно, если только они не производили одинаковое количество строк. В противном случае получили бы количество выходных строк, равное наименьшему общему кратному чисел строк, произведенных функциями, возвращающими наборы. Кроме того, вложенные функции, возвращающие наборы, работали не так, как описано выше; вместо этого у функции, возвращающей набор, могло быть самое большее один аргумент, возвращающий набор, и каждая группа функций, возвращающих набор, выполнялась независимо. Также ранее допускалась условная обработка (функции, возвращающие набор, внутри CASE и т.д.), что еще больше усложняло ситуацию. Рекомендуется использовать синтаксис LATERAL при написании запросов, которые должны работать в старых версиях PostgreSQL, поскольку это даст согласованные результаты во всех различных версиях. Если есть запрос, который зависит от условного выполнения функции, возвращающей набор, возможно, сможете исправить его, переместив условный тест в пользовательскую функцию, возвращающую набор. Например,

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;

Эта формулировка будет работать одинаково во всех версиях PostgreSQL.

Функции SQL, возвращающие TABLE

Есть еще один способ объявить функцию, которая возвращает набор, который заключается в использовании синтаксиса 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 - необходимо поместить все выходные столбцы в список TABLE.

Полиморфные функции SQL

Функции SQL могут быть объявлены для приема и возврата полиморфных типов, описанных в разделе «Полиморфные типы». Вот полиморфная функция 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 не является допустимым типом. Без приведения типа получатся ошибки, подобные этой:

ERROR:  could not determine polymorphic type because input has type unknown

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

SELECT make_array(1, 2.5) AS numericarray;
ERROR:  function make_array(integer, numeric) does not exist

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

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 row)

Поскольку правила разрешения общих типов по умолчанию выбирают тип 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)

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

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 с сопоставлением

Когда у функции SQL есть один или несколько параметров типа сопоставляемых данных, для каждого вызова функции определяется сопоставление в зависимости от сопоставлений, назначенных фактическим аргументам, как описано в разделе «Поддержка сопоставления». Если сопоставление успешно идентифицировано (т.е. среди аргументов нет конфликтов неявных сопоставлений), то все параметры, допускающие сопоставление, считаются имеющими это сопоставление неявно. Это повлияет на поведение операций, чувствительных к сопоставлению, внутри функции. Например, используя функцию 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-функция рассматривает свои параметры как имеющие сортировку по умолчанию для их типов данных (что обычно является сортировкой по умолчанию для базы данных, но может быть другим для параметров доменных типов).

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