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

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

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

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

Любой набор команд на языке SQL можно собрать вместе и определить как функцию. Помимо запросов SELECT эти команды могут включать запросы, изменяющие данные (INSERT, UPDATE, DELETE и MERGE), а также другие команды SQL. (В функциях SQL нельзя использовать команды управления транзакциями, например COMMIT, SAVEPOINT, и некоторые служебные команды, например VACUUM). Однако последней командой должна быть 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, являются расширением QHB.

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

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

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

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

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

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

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

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

INSERT INTO mytable VALUES ($1);

а это не будет работать:

INSERT INTO $1 VALUES (42);

Функции SQL c базовыми типами

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

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

-- Альтернативный синтаксис строковой константы:
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;

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

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

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

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

Функции 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 использует указание имя_таблицы.*, чтобы выбрать текущую строку таблицы как составное значение. На строку таблицы также можно сослаться, используя только имя таблицы, например, так:

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.
-- ОШИБКА: возвращаемый тип не совпадает в функции, объявленной как возвращающая тип emp
-- ДЕТАЛИЗАЦИЯ: Последний оператор возвращает text вместо point в столбце 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 c базовыми типами. Реальная ценность выходных параметров в том, что они предоставляют удобный способ определения функций, возвращающих несколько столбцов. Например,

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

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]);    -- это не сработает

Однако вы не можете написать так — или, по крайней мере, это не будет соответствовать определению этой функции. Параметр, помеченный как 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();  -- не работает, потому что для первого аргумента нет значения по умолчанию
ERROR:  function foo() does not exist
-- ОШИБКА: функция foo() не существует

Вместо ключевого слова 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, описанное в подразделе Подзапросы 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.

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

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

Функции SQL, возвращающие таблицы

Есть еще один способ объявить функцию как возвращающую множество — использовать синтаксис RETURNS TABLE(столбцы). Это равнозначно использованию одного или нескольких параметров 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;

С записью RETURNS TABLE не допускается использование явных параметров OUT или INOUT — все выходные столбцы следует записать в списке 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"
-- ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип «unknown»

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

SELECT make_array(1, 2.5) AS numericarray;
ERROR:  function make_array(integer, numeric) does not exist
-- ОШИБКА: функции 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 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.
-- ОШИБКА: не удалось определить тип данных результата
-- ДЕТАЛИЗАЦИЯ: Для результата типа anyelement требуется хотя бы один аргумент типа anyelement, anyarray, anynonarray, anyenum или 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 будет воспринимать свои параметры как имеющие правило сортировки по умолчанию для их типов данных (которое обычно является правилом сортировки по умолчанию для базы данных, но может отличаться для параметров доменных типов).

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

Copyright © KVANTOM LLC, 2019-2025