36.5. Функции языка запросов (SQL)#
36.5. Функции языка запросов (SQL)
- 36.5.1. Аргументы для функций SQL
- 36.5.2. SQL Функции на базовых типах
- 36.5.3. SQL Функции на составных типах
- 36.5.4. SQL Функции с выходными параметрами
- 36.5.5. SQL Процедуры с выходными параметрами
- 36.5.6. SQL Функции с переменным числом аргументов
- 36.5.7. SQL Функции с Значениями По Умолчанию для Аргументов
- 36.5.8. SQL Функции в качестве источников таблиц
- 36.5.9. SQL Функции, возвращающие наборы
- 36.5.10. SQL Функции, возвращающие
TABLE
- 36.5.11. Полиморфные функции SQL
- 36.5.12. SQL Функции с правилами сортировки
SQL функции выполняют произвольный список SQL-запросов, возвращая результат последнего запроса в списке.
В простом (не множественном) случае будет возвращена первая строка результата последнего запроса.
(Имейте в виду, что “первая строка” множественного результата не определена, если вы не используете ORDER BY
).
Если последний запрос не возвращает ни одной строки, будет возвращено значение null.
В качестве альтернативы, SQL-функция может быть объявлена для возврата набора (то есть, нескольких строк), указав тип возвращаемого значения функции как SETOF
, или эквивалентно, объявив его как sometype
RETURNS TABLE(
. В этом случае возвращаются все строки результата последнего запроса. Дополнительные детали приведены ниже.
columns
)
Тело SQL-функции должно быть списком SQL-операторов, разделенных точкой с запятой. Точка с запятой после последнего оператора является необязательной. Если функция не объявлена с возвращаемым типом void
, то последним оператором должен быть SELECT
, или INSERT
, UPDATE
или DELETE
с предложением 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).
36.5.1. Аргументы для функций SQL
Аргументы SQL-функции могут быть ссылками в теле функции с использованием либо имен, либо номеров. Примеры обоих методов приведены ниже.
Чтобы использовать имя, объявите аргумент функции с именем и затем просто напишите это имя в теле функции. Если имя аргумента совпадает с именем столбца в текущей SQL-команде внутри функции, предпочтение отдается имени столбца. Чтобы переопределить это, укажите имя аргумента с именем самой функции, то есть
. (Если это противоречит полному имени столбца, снова побеждает имя столбца. Вы можете избежать неоднозначности, выбрав другой псевдоним для таблицы внутри SQL-команды).
function_name
.argument_name
В старом числовом подходе аргументы обозначаются с использованием синтаксиса
$
: n
$1
относится к первому входному
аргументу, $2
- ко второму и так далее. Это будет работать
независимо от того, был ли конкретный аргумент объявлен с именем.
Если аргумент является составным типом, то можно использовать точечную нотацию, например,
или argname
.fieldname
$1.
, чтобы получить доступ к атрибутам аргумента. Опять же, возможно потребуется квалифицировать имя аргумента с именем функции, чтобы сделать форму с именем аргумента однозначной.
fieldname
Аргументы SQL-функций могут использоваться только в качестве значений данных, а не в качестве идентификаторов. Таким образом, например, это разумно:
INSERT INTO mytable VALUES ($1);
но это не сработает:
INSERT INTO $1 VALUES (42);
Примечание
Возможность использовать имена для ссылки на аргументы SQL-функций была добавлена в PostgreSQL 9.2. Функции, которые будут использоваться в старых серверах, должны использовать обозначение $
.
n
36.5.2. 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
. Конечно, мы могли бы избежать этого, используя другое имя для аргумента.
На практике, вероятно, было бы желательно получить более полезный результат от функции, чем постоянное значение 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 не возвращает точно тот тип результата, который объявлен для функции, Tantor SE автоматически преобразует значение в требуемый тип, если это возможно с помощью неявного или присваиваемого преобразования. В противном случае необходимо написать явное преобразование. Например, предположим, что мы хотим, чтобы предыдущая функция add_em
возвращала тип float8
. Достаточно написать
CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$ SELECT $1 + $2; $$ LANGUAGE SQL;
поскольку сумма типа integer
может быть неявно приведена к типу float8
.
(См. Глава 10 или CREATE CAST
для получения дополнительной информации о приведении типов).
36.5.3. 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)';
но такое использование устарело, так как легко запутаться. (См. Раздел 8.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;
В этом примере мы указали каждый из атрибутов с постоянным значением, но вместо этих констант можно было бы использовать любые вычисления.
Обратите внимание на две важные вещи при определении функции:
Порядок выборки в запросе должен точно соответствовать порядку, в котором столбцы появляются в составном типе. (Называние столбцов, как мы сделали выше, не имеет значения для системы).
Мы должны убедиться, что тип каждого выражения может быть приведен к типу соответствующего столбца составного типа. В противном случае мы получим ошибки, подобные этим:
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)
Второй способ описан более подробно в Раздел 36.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
Как объясняется в Раздел 8.16.5, обозначение поля и функциональное обозначение эквивалентны.
Еще один способ использования функции, возвращающей составной тип, заключается в передаче результата другой функции, которая принимает правильный тип строки в качестве входного параметра:
CREATE FUNCTION getname(emp) RETURNS text AS $$ SELECT $1.name; $$ LANGUAGE SQL; SELECT getname(new_emp()); getname --------- None (1 row)
36.5.4. 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
,
показанной в Раздел 36.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 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. Это происходит потому, что Tantor SE учитывает только входные параметры для определения сигнатуры вызова функции. Это также означает, что только входные параметры имеют значение при ссылке на функцию для целей, таких как удаление ее. Можно удалить вышеуказанную функцию с помощью любого из
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
являются входными параметрами, но обрабатываются особым образом, как описано ниже.
36.5.5. 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
вы должны записать переменную,
которая будет получать вывод процедуры. См. Раздел 41.6.3 для получения подробной информации.
36.5.6. 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
, соответствует одному или более вхождениям его элементного типа, а не его собственному типу.
Иногда полезно иметь возможность передать уже созданный массив вариативной функции; это особенно удобно, когда одна вариативная функция хочет передать свой массивный параметр другой функции. Кроме того, это единственный безопасный способ вызвать вариативную функцию, найденную в схеме, которая позволяет ненадежным пользователям создавать объекты; см. Раздел 10.3. Вы можете сделать это, указав VARIADIC
в вызове:
SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);
Это предотвращает развертывание вариативного параметра функции в его
тип элемента, тем самым позволяя значению аргумента массива соответствовать
нормально. VARIADIC
может быть присоединен только к последнему
фактическому аргументу вызова функции.
Указание VARIADIC
в вызове также является единственным способом
передачи пустого массива в вариативную функцию, например:
SELECT mleast(VARIADIC ARRAY[]::numeric[]);
Просто написание SELECT mleast()
не сработает, потому что вариативный параметр должен соответствовать хотя бы одному фактическому аргументу.
(Вы можете определить вторую функцию с именем mleast
, без параметров, если хотите разрешить такие вызовы).
Параметры элемента массива, сгенерированные из вариативного параметра, считаются не имеющими собственных имен. Это означает, что невозможно вызвать вариативную функцию, используя именованные аргументы (Раздел 4.3), за исключением случаев, когда вы указываете VARIADIC
. Например, это будет работать:
SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);
но не эти:
SELECT mleast(arr => 10); SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);
36.5.7. SQL Функции с Значениями По Умолчанию для Аргументов
Функции могут быть объявлены с значениями по умолчанию для одного или нескольких входных аргументов. Значения по умолчанию вставляются каждый раз, когда функция вызывается с недостаточным количеством фактических аргументов. Поскольку аргументы могут быть не указаны только из конца списка фактических аргументов, все параметры после параметра со значением по умолчанию также должны иметь значения по умолчанию. (Хотя использование именованной нотации аргументов может позволить расслабить это ограничение, оно все равно применяется, чтобы позиционная нотация аргументов работала разумно). Независимо от того, используете ли вы это или нет, эта возможность создает необходимость в предосторожности при вызове функций в базах данных, где некоторые пользователи не доверяют другим пользователям; см. Раздел 10.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(); -- fails since there is no default for the first argument ERROR: function foo() does not exist
Знак =
также может использоваться вместо ключевого слова DEFAULT
.
36.5.8. 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
. Об этом будет рассказано в следующем разделе.
36.5.9. SQL Функции, возвращающие наборы
Когда SQL-функция объявляется с возвращаемым значением SETOF
, окончательный запрос функции
выполняется до конца, и каждая строка, которую он
выводит, возвращается в качестве элемента результирующего набора.
sometype
Эта функция обычно используется при вызове функции в предложении 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
, которое описано в разделе Раздел 7.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 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
.
Поведение Tantor SE для функции,
возвращающей набор значений в выборке запроса, практически идентично поведению, если бы эта функция была написана в элементе
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
-предложения. Для каждой строки из базового запроса создается выходная строка, используя первый результат каждой функции, затем выходная строка с использованием второго результата и так далее. Если некоторые функции, возвращающие наборы значений, производят меньше результатов, чем другие, вместо отсутствующих данных подставляются значения null, так что общее количество строк, выданных для одной базовой строки, такое же, как для функции, возвращающей наибольшее количество результатов. Таким образом, функции, возвращающие наборы значений, выполняются “параллельно”, пока все они не будут исчерпаны, а затем выполнение продолжается с следующей базовой строки.
Set-returning функции могут быть вложены в выборку, хотя это не разрешено в элементах предложения 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
с 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;
Эта формулировка будет работать одинаково во всех версиях Tantor SE.
36.5.10. 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
.
36.5.11. Полиморфные функции SQL
SQL функции могут быть объявлены для принятия и возвращения полиморфных типов, описанных в Раздел 36.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
не является допустимым типом.
Без приведения типа вы получите ошибки вроде таких:
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
Альтернативным подходом является использование семейства полиморфных типов “common”, которые позволяют системе попытаться определить подходящий общий тип:
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)
36.5.12. SQL Функции с правилами сортировки
Когда у SQL-функции есть один или несколько параметров с сортируемыми типами данных, для каждого вызова функции определяется правило сортировки в зависимости от правил сортировки, назначенных фактическим аргументам, как описано в разделе Раздел 23.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-функция обрабатывает свои параметры как имеющие правило сортировки по умолчанию для их типов данных (которая обычно является правилом сортировки по умолчанию для базы данных, но может отличаться для параметров типов доменов).
Поведение сортируемых параметров можно рассматривать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.