Функции, используемых в базах данных SQL
Сведения о категориях встроенных функций, которые можно использовать с базами данных SQL. Вы можете использовать встроенные функции или создавать собственные пользовательские функции.
Агрегатные функции
Агрегатные функции выполняют вычисление на наборе значений и возвращают одиночное значение. Они допускаются в списке выбора или в предложении HAVING инструкции SELECT. Агрегатную функцию можно использовать в сочетании с предложением GROUP BY для статистических вычислений на основе категорий строк. Используйте предложение OVER для вычисления статистического значения на основе определенного диапазона значений. Предложение OVER не может следовать за агрегатными функциями GROUPING и GROUPING_ID.
Все агрегатные функции являются детерминированными. Это означает, что они всегда возвращают одинаковый результат для одинаковых входных значений. Дополнительные сведения см. в разделе Deterministic and Nondeterministic Functions.
Аналитические функции
Аналитические функции вычисляют статистическое значение на основе группы строк. Однако, в отличие от агрегатных функций, аналитические функции могут возвращать несколько строк для каждой группы. Аналитические функции можно использовать для вычисления скользящих средних, промежуточных итогов, процентных долей или первых N результатов в группе.
Функции поразрядной операции
Область применения: SQL Server 2022 (16.x)
Функции поразрядной операции позволяют обрабатывать и хранить данные более эффективно, чем при использовании отдельных битов. Дополнительные сведения см. в статье Функции поразрядной операции.
Ранжирующие функции
Ранжирующие функции возвращают ранжирующее значение для каждой строки в секции. В зависимости от используемой функции значения некоторых строк могут совпадать. Ранжирующие функции являются недетерминированными.
Функции наборов строк
Функции наборов строк возвращают объект, который можно использовать так же, как табличные ссылки в инструкции SQL.
Скалярные функции
Обрабатывают и возвращают одиночное значение. Скалярные функции можно применять везде, где выражение допустимо.
Категории скалярных функций
| Категория функции | Description |
|---|---|
| Функции конфигурации | Возвращают сведения о текущей конфигурации. |
| Функции преобразования | Поддержка приведения и преобразования типов данных. |
| Функции работы с курсорами | Возвращают сведения о курсорах. |
| Функции и типы данных даты и времени | Выполняют операции над исходными значениями даты и времени, возвращают строковые и числовые значения, а также значения даты и времени. |
| Функции Graph | Выполняют операции для преобразования в символьные представления узлов графа и граничных идентификаторов. |
| Функции JSON | Проверяют, запрашивают или изменяют данные JSON. |
| Логические функции | Выполнение логических операций. |
| Математические функции | Выполняют вычисления, основанные на числовых значениях, переданных функции в виде аргументов, и возвращают числовые значения. |
| Функции метаданных | Возвращают сведения о базах данных и объектах баз данных. |
| Функции безопасности | Возвращают данные о пользователях и ролях. |
| Строковые функции | Выполняют операции со строковым (char или varchar) входным значением и возвращают строковое или числовое значение. |
| Системные функции | Выполняйте операции и возвращайте сведения о значениях, объектах и параметрах в экземпляре SQL Server. |
| Системные статистические функции | Возвращают статистические сведения о системе. |
| Функции обработки текста и изображений | Выполняют операции над текстовыми или графическими исходными значениями или столбцами и возвращают сведения о значении. |
Детерминизм функций
Встроенные функции SQL Server являются детерминированными или недетерминированными. Функция является детерминированной, если для определенных входных значений она каждый раз возвращает один и тот же результат. Функция является недетерминированной, если она возвращает различные результаты даже для одних и тех же исходных значений. Дополнительные сведения см. в статье Детерминированные и недетерминированные функции.
Параметры сортировки функций
Функции, в которые вводится символьная строка и которые выдают ее, используют параметры сортировки входной строки для строки вывода.
Функции, которые обрабатывают несимвольные исходные данные и выдают символьную строку, применяют при выводе параметры сортировки по умолчанию для текущей базы данных.
Функции, обрабатывающие в качестве исходных данных несколько символьных строк и возвращающие символьную строку, задают параметры сортировки для строки вывода по правилам очередности параметров сортировки. Дополнительные сведения см. в разделе Очередность параметров сортировки (Transact-SQL).
Ограничения
Сведения об ограничениях типов функций и платформ см. в статье CREATE FUNCTION.
См. также
- CREATE FUNCTION (Transact-SQL)
- Детерминированные и недетерминированные функции
- Использование хранимых процедур (многомерные выражения)
Типы данных и функции даты и времени (Transact-SQL)
В разделах этой статьи представлен обзор всех типов данных и функций даты и времени Transact-SQL.
- Типы данных даты и времени
- Функции даты и времени
- Функции, возвращающие значения системной даты и времени
- Функции, возвращающие компоненты даты и времени
- Функции, возвращающие значения даты и времени из их компонентов
- Функции, возвращающие значения разности даты и времени
- Функции, изменяющие значения даты и времени
- Функции, устанавливающие или возвращающие функции формата сеанса
- Функции, проверяющие значения даты и времени
Типы данных даты и времени
Типы данных даты и времени Transact-SQL перечислены в следующей таблице:
Тип данных Формат Диапазон Точность Объем памяти (в байтах) Определяемая пользователем точность в долях секунды Смещение часового пояса time чч:мм:сс[.ннннннн] От 00:00:00.0000000 до 23:59:59.9999999 100 наносекунд от 3 до 5 Да Нет date ГГГГ-ММ-ДД От 0001-01-01 до 31.12.99 1 день 3 Нет Нет smalldatetime ГГГГ-ММ-ДД чч:мм:сс От 01.01.1900 до 06.06.2079 1 минута 4 нет Нет datetime ГГГГ-ММ-ДД чч:мм:сс[.ннн] От 01.01.1753 до 31.12.9999 0,00333 секунды 8 Нет Нет datetime2 ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 100 наносекунд От 6 до 8 Да Нет datetimeoffset ГГГГ-ММ-ДД чч:мм:сс[.ннннннн] [+|-]чч:мм От 0001-01-01 00:00:00.0000000 до 9999-12-31 23:59:59.9999999 (время в формате UTC) 100 наносекунд От 8 до 10 Да Да Тип данных Transact-SQL rowversion не относится к типам данных даты и времени. Тип данных timestamp является устаревшим синонимом rowversion.
Функции даты и времени
В следующих таблицах приводятся функции даты и времени Transact-SQL. Дополнительные сведения о детерминизме см. в статье Детерминированные и недетерминированные функции.
Функции, возвращающие значения системной даты и времени
Transact-SQL наследует все значения системной даты и времени от операционной системы компьютера, на котором работает экземпляр SQL Server.
Высокоточные функции системной даты и времени
Начиная с SQL Server 2008 (10.0.x) ядро СУБД получает значения даты и времени с помощью API Windows GetSystemTimeAsFileTime(). Точность зависит от физического оборудования и версии Windows, в которой запущен экземпляр SQL Server. Точность возвращаемых значений этого API-интерфейса задана равной 100 нс. Точность может быть определена с помощью метода GetSystemTimeAdjustment() API-интерфейса Windows.
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм SYSDATETIME SYSDATETIME ( ) Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение не содержит смещение часового пояса. datetime2(7) Недетерминированная SYSDATETIMEOFFSET SYSDATETIMEOFFSET ( ) Возвращает значение типа datetimeoffset(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение содержит смещение часового пояса. datetimeoffset(7) Недетерминированная SYSUTCDATETIME SYSUTCDATETIME ( ) Возвращает значение типа datetime2(7), которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Функция возвращает значения даты и времени в формате UTC. datetime2(7) Недетерминированная Функции системной даты и времени меньшей точности
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм CURRENT_TIMESTAMP CURRENT_TIMESTAMP Возвращает значение типа datetime, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение не содержит смещение часового пояса. datetime Недетерминированная GETDATE GETDATE ( ) Возвращает значение типа datetime, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Возвращаемое значение не содержит смещение часового пояса. datetime Недетерминированная GETUTCDATE GETUTCDATE ( ) Возвращает значение типа datetime, которое содержит дату и время компьютера, на котором запущен экземпляр SQL Server. Функция возвращает значения даты и времени в формате UTC. datetime Недетерминированная Функции, возвращающие компоненты даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATE_BUCKET DATE_BUCKET ( datepart, number, date, origin ) Возвращает значение даты и времени, соответствующее началу каждого контейнера даты и времени, из временной метки, определенной параметром origin или исходным значением по умолчанию 1900-01-01 00:00:00.000 , если параметр orgin не указан. Тип возвращаемого значения зависит от типа аргумента, переданного в параметре date. Недетерминированная DATENAME DATENAME ( datepart, date ) Возвращает строку символов, представляющую указанную часть datepart заданного типа date. nvarchar Недетерминированная DATEPART DATEPART ( datepart, date ) Возвращает целое число, представляющее указанную часть datepart заданного типа date. int Недетерминированная DATETRUNC DATETRUNC ( datepart, date ) Эта функция возвращает входную дату date, усеченную до указанной части datepart. Тип возвращаемого значения зависит от типа аргумента, переданного в параметре date. Недетерминированная DAY DAY ( date ) Возвращает целое число, представляющее часть дня указанного типа date. int Детерминированный MONTH MONTH ( date ) Возвращает целое число, представляющее часть месяца указанного типа date. int Детерминированный YEAR YEAR ( date ) Возвращает целое число, представляющее часть года указанного типа date. int Детерминированный Функции, возвращающие значения даты и времени из их компонентов
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATEFROMPARTS DATEFROMPARTS ( year, month, day ) Возвращает значение date, соответствующее указанному числу, месяцу и году. date Детерминированный DATETIME2FROMPARTS DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision) Возвращает значение datetime2, соответствующее указанной дате и времени с заданной точностью. datetime2(precision) Детерминированный DATETIMEFROMPARTS DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds) Возвращает значение datetime, соответствующее указанной дате и времени. datetime Детерминированный DATETIMEOFFSETFROMPARTS DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision) Возвращает значение datetimeoffset для указанных даты и времени с указанными смещением и точностью. datetimeoffset(precision) Детерминированный SMALLDATETIMEFROMPARTS SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute ) Возвращает значение smalldatetime, соответствующее указанной дате и времени. smalldatetime Детерминированный TIMEFROMPARTS TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) Возвращает значение time, соответствующее указанному времени с заданной точностью. time(precision) Детерминированный Функции, возвращающие значения разности даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATEDIFF DATEDIFF ( datepart, startdate, enddate ) Возвращает количество границ даты или времени datepart, пересекающихся между двумя указанными датами. int Детерминированный DATEDIFF_BIG DATEDIFF_BIG ( datepart, startdate, enddate ) Возвращает количество границ даты или времени datepart, пересекающихся между двумя указанными датами. bigint Детерминированный Функции, изменяющие значения даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм DATEADD DATEADD (datepart, number, date ) Возвращает новое значение datetime, добавляя интервал к указанной части datepart заданной даты date. Тип данных аргумента date Детерминированный EOMONTH EOMONTH ( start_date [, month_to_add ] ) Возвращает последний день месяца, содержащего указанную дату, с необязательным смещением. Тип возвращаемого значения — это тип аргумента start_date или тип данных date. Детерминированный SWITCHOFFSET SWITCHOFFSET (DATETIMEOFFSET, time_zone) Функция SWITCHOFFSET изменяет смещение часового пояса для значения DATETIMEOFFSET и сохраняет значение UTC. Значение datetimeoffset с точностью в долях секунд, заданной в аргументе DATETIMEOFFSET Детерминированный TODATETIMEOFFSET TODATETIMEOFFSET (expression, time_zone) TODATETIMEOFFSET преобразует значение типа datetime2 в значение типа datetimeoffset. Функция TODATETIMEOFFSET преобразует значение datetime2 в местное время для указанного time_zone. Значение datetimeoffset с точностью в долях секунд, заданной в аргументе datetime Детерминированный Функции, устанавливающие или возвращающие функции формата сеанса
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм @@DATEFIRST @@DATEFIRST Возвращает текущее значение параметра SET DATEFIRST для сеанса. tinyint Недетерминированная SET DATEFIRST SET DATEFIRST < number | @number_var > Устанавливает первый день недели в виде числа от 1 до 7. Неприменимо Неприменимо SET DATEFORMAT SET DATEFORMAT < format | @format_var > Задает порядок составляющих даты (месяц/день/год) для ввода данных типа datetime или smalldatetime. Неприменимо Неприменимо @@LANGUAGE @@LANGUAGE Возвращает название использующегося в настоящий момент языка. @@LANGUAGE не является функцией даты или времени. Однако на данные, выводимые функциями даты, могут повлиять настройки языка. Неприменимо Неприменимо SET LANGUAGE SET LANGUAGE < [ N ] ‘language‘ | @language_var > Устанавливает языковую среду сеанса и системных сообщений. SET LANGUAGE не является функцией даты или времени. Однако на данные, выводимые функциями даты, влияет параметр языка. Неприменимо Неприменимо sp_helplanguage sp_helplanguage [ [ = ] ‘language‘ ] Возвращает сведения о формате даты всех поддерживаемых языков. sp_helplanguage не является хранимой процедурой даты или времени. Однако на данные, выводимые функциями даты, влияет параметр языка. Неприменимо Неприменимо Функции, проверяющие значения даты и времени
Функция Синтаксис Возвращаемое значение Тип возвращаемых данных Детерминизм ISDATE ISDATE ( expression ) Определяет, является ли входное выражение типа datetime или smalldatetime допустимым значением даты или времени. int Функция ISDATE детерминирована, только если используется совместно с функцией CONVERT и если заданный параметр стиля CONVERT не равен 0, 100, 9 или 109. Дата и время — см. также
Статья Описание FORMAT Возвращает значение в указанных формате и культуре (не обязательно). Для выполнения форматирования значения даты, времени и чисел с учетом локали в виде строк используется функция FORMAT. Функции CAST и CONVERT (Transact-SQL) Предоставляет сведения о преобразовании значений даты и времени в строковые литералы и обратно, а также в другие форматы даты и времени. Написание инструкций Transact-SQL, адаптированных к международному использованию Предоставляет рекомендации относительно переносимости баз данных и приложений баз данных, использующих инструкции Transact-SQL, с одного языка на другой или в многоязычную среду. Скалярные функции ODBC (Transact-SQL) Предоставляет сведения о скалярных функциях ODBC, которые могут использоваться в инструкциях Transact-SQL. К ним относятся функции даты и времени ODBC. AT TIME ZONE (Transact-SQL) Обеспечивает преобразование часовых поясов. См. также
- Функции
- Типы данных (Transact-SQL)
Обратная связь
Были ли сведения на этой странице полезными?
Оконные функции SQL простым языком с примерами
Сразу хочется отметить, что данная статья написана исключительно для людей, начинающих свой путь в изучении SQL и оконных функций. Здесь могут быть не разобраны сложные применения функций и могут не использоваться сложные формулировки определений — все написано максимально простым языком для базового понимания.
P.S. Если автор что-то не разобрал и не написал, значит он посчитал это не обязательным в рамках этой статьи)))
Для примеров будем использовать небольшую таблицу, которая показывает оценки учеников по разным предметам. В БД табличка выглядит следующим образом
--создание таблицы create table student_grades ( name varchar, subject varchar, grade int); -- наполнение таблицы данными insert into student_grades ( values ('Петя', 'русский', 4), ('Петя', 'физика', 5), ('Петя', 'история', 4), ('Маша', 'математика', 4), ('Маша', 'русский', 3), ('Маша', 'физика', 5), ('Маша', 'история', 3)); --запрос всех данных из таблицы select * from student_grades;
SQL часто используется для вычислений в данных различных метрик или агрегаций значений по измерениям. Помимо функций агрегации для этого широко используются оконные функции.
Оконная функция в SQL — функция, которая работает с выделенным набором строк (окном, партицией) и выполняет вычисление для этого набора строк в отдельном столбце.
Партиции (окна из набора строк) — это набор строк, указанный для оконной функции по одному из столбцов или группе столбцов таблицы. Партиции для каждой оконной функции в запросе могут быть разделены по различным колонкам таблицы.
В чем заключается главное отличие оконных функций от функций агрегации с группировкой?
При использовании агрегирующих функций предложение GROUP BY сокращает количество строк в запросе с помощью их группировки.
При использовании оконных функций количество строк в запросе не уменьшается по сравнении с исходной таблицей.
Порядок расчета оконных функций в SQL запросе
Сначала выполняется команда выборки таблиц, их объединения и возможные подзапросы под командой FROM.
Далее выполняются условия фильтрации WHERE, группировки GROUP BY и возможная фильтрация c HAVING
Только потом применяется команда выборки столбцов SELECT и расчет оконных функций под выборкой.
После этого идет условие сортировки ORDER BY, где тоже можно указать столбец расчета оконной функции для сортировки.
Здесь важно уточнить, что партиции или окна оконных функций создаются после разделения таблицы на группы с помощью команды GROUP BY, если эта команда используется в запросе.
Синтаксис оконных функций
Синтаксис оконных функций вне зависимости от их класса будет так или иначе состоять из идентичных команд.
Оконные функции можно прописывать как под командой SELECT, так и в отдельном ключевом слове WINDOW, где окну дается алиас (псевдоним), к которому можно обращаться в SELECT выборке.
Классы Оконных функций
Множество оконных функций можно разделять на 3 класса:
- Агрегирующие (Aggregate)
- Ранжирующие (Ranking)
- Функции смещения (Value)
Агрегирующие:
Можно применять любую из агрегирующих функций — SUM, AVG, COUNT, MIN, MAX
select name, subject, grade, sum(grade) over (partition by name) as sum_grade, avg(grade) over (partition by name) as avg_grade, count(grade) over (partition by name) as count_grade, min(grade) over (partition by name) as min_grade, max(grade) over (partition by name) as max_grade from student_grades;
Ранжирующие:
В ранжирующих функция под ключевым словом OVER обязательным идет указание условия ORDER BY, по которому будет происходить сортировка ранжирования.
ROW_NUMBER() — функция вычисляет последовательность ранг (порядковый номер) строк внутри партиции, НЕЗАВИСИМО от того, есть ли в строках повторяющиеся значения или нет.
RANK() — функция вычисляет ранг каждой строки внутри партиции. Если есть повторяющиеся значения, функция возвращает одинаковый ранг для таких строчек, пропуская при этом следующий числовой ранг.
DENSE_RANK() — то же самое что и RANK, только в случае одинаковых значений DENSE_RANK не пропускает следующий числовой ранг, а идет последовательно.
select name, subject, grade, row_number() over (partition by name order by grade desc), rank() over (partition by name order by grade desc), dense_rank() over (partition by name order by grade desc) from student_grades;
Про NULL в случае ранжирования:
Для SQL пустые NULL значения будут определяться одинаковым рангом
Функции смещения:
Это функции, которые позволяют перемещаясь по выделенной партиции таблицы обращаться к предыдущему значению строки или крайним значениям строк в партиции.
LAG() — функция, возвращающая предыдущее значение столбца по порядку сортировки.
LEAD() — функция, возвращающая следующее значение столбца по порядку сортировки.
На простом примере видно, как можно в одной строке получить текущую оценку, предыдущую и следующую оценки Пети в четвертях.
--создание таблицы create table grades_quartal ( name varchar, quartal varchar, subject varchar, grade int); --наполнение таблицы данными insert into grades_quartal ( values ('Петя', '1 четверть', 'физика', 4), ('Петя', '2 четверть', 'физика', 3), ('Петя', '3 четверть', 'физика', 4), ('Петя', '4 четверть', 'физика', 5) ); --запрос всех данных из таблицы select * from grades_quartal;
select name, quartal, subject, grade, lag(grade) over (order by quartal) as previous_grade, lead(grade) over (order by quartal) as next_grade from grades_quartal;
FIRST_VALUE()/LAST_VALUE() — функции возвращающие первое или последнее значение столбца в указанной партиции. В качестве аргумента указывает столбец, значение которого нужно вернуть. В оконной функции под словом OVER обязательное указание ORDER BY условия.
В следующей версии статьи разберем отдельно такое понятие как фрейм окна функции или window frame и рассмотрим на простых примерах как он используется.
Какой функции нет в sql
SQL-функции выполняют произвольный список операторов SQL и возвращают результат последнего запроса в списке. В простом случае (не с множеством) будет возвращена первая строка результата последнего запроса. (Помните, что понятие « первая строка » в наборе результатов с несколькими строками определено точно, только если присутствует ORDER BY .) Если последний запрос вообще не вернёт строки, будет возвращено значение NULL.
Кроме того, можно объявить SQL-функцию как возвращающую множество (то есть, несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип , либо объявив её с указанием RETURNS TABLE( столбцы ) . В этом случае будут возвращены все строки результата последнего запроса. Подробнее это описывается ниже.
Тело SQL-функции должно представлять собой список SQL-операторов, разделённых точкой с запятой. Точка с запятой после последнего оператора может отсутствовать. Если только функция не объявлена как возвращающая void , последним оператором должен быть SELECT , либо INSERT , UPDATE или DELETE с предложением RETURNING .
Любой набор команд на языке SQL можно скомпоновать вместе и обозначить как функцию. Помимо запросов SELECT , эти команды могут включать запросы, изменяющие данные ( INSERT , UPDATE и DELETE ), а также другие 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)
Примечание
Прежде чем начинается выполнение команд, разбирается всё тело SQL-функции. Когда SQL-функция содержит команды, модифицирующие системные каталоги (например, CREATE TABLE ), действие таких команд не будет видимо на стадии анализа последующих команд этой функции. Так, например, команды CREATE TABLE foo (. ); INSERT INTO foo VALUES(. ); не будут работать, как ожидается, если их упаковать в одну SQL-функцию, так как foo не будет существовать к моменту разбору команды INSERT . В подобных ситуациях вместо SQL-функции рекомендуется использовать PL/PgSQL .
Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно для этого удобнее всего заключать строковую константу в доллары (см. Подраздел 4.1.2.4). Если вы решите использовать обычный синтаксис с заключением строки в апострофы, вам придётся дублировать апострофы ( ' ) и обратную косую черту ( \ ) (предполагается синтаксис спецпоследовательностей) в теле функции (см. Подраздел 4.1.2.1).
36.4.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 .
36.4.2. Функции SQL с базовыми типами
Простейшая возможная функция 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 integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno; SELECT 1; $$ LANGUAGE SQL;
Пользователь может выполнить эту функцию, чтобы дебетовать счёт 17 на 100 долларов, так:
SELECT tf1(17, 100.0);
В этом примере мы выбрали имя accountno для первого аргумента, но это же имя имеет столбец в таблице bank . В команде UPDATE имя accountno относится к столбцу bank.accountno , так для обращения к аргументу нужно записать tf1.accountno . Конечно, мы могли бы избежать этого, выбрав другое имя для аргумента.
На практике обычно желательно получать от функции более полезный результат, чем константу 1, поэтому более реалистично такое определение:
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer 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 integer AS $$ UPDATE bank SET balance = balance - debit WHERE accountno = tf1.accountno RETURNING balance; $$ LANGUAGE SQL;
36.4.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 указание имя_таблицы .* выбирает всю текущую строку таблицы как составное значение. На строку таблицы можно сослаться и просто по имени таблицы, например так:
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: function declared to return emp returns varchar instead of text at column 1Ту же функцию можно определить другим способом:
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.4.7.
Когда используется функция, возвращающая составной тип, может возникнуть желание получить из её результата только одно поле (атрибут). Это можно сделать, применяя такую запись:
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.4.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.4.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. Это объясняется тем, что 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 являются входными, но обрабатывается специальным образом, как описано далее.
36.4.5. Функции 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 соответствуют одно или несколько вхождений типа его элемента, но не его собственного типа.
Но иногда бывает полезно передать функции с переменными параметрами уже подготовленный массив; особенно когда одна функция с переменными параметрами хочет передавать свой массив параметров другой. Также это более безопасный способ вызывать такую функцию, существующую в схеме, где могут создавать объекты недоверенные пользователи; см. Раздел 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.4.6. Функции 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(); -- не работает из-за отсутствия значения по умолчанию для первого аргумента ERROR: function foo() does not exist
(ОШИБКА: функция foo() не существует) Вместо ключевого слова DEFAULT можно использовать знак = .
36.4.7. Функции 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.4.8. Функции 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 , описываемое в Подразделе 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 .
Примечание
Если последняя команда функции — INSERT , UPDATE или DELETE с RETURNING , эта команда будет всегда выполняться до завершения, даже если функция не объявлена с указанием SETOF или вызывающий запрос не выбирает все строки результата. Все дополнительные строки, выданные предложением RETURNING , просто игнорируются, но соответствующие изменения в таблице всё равно произойдут (и будут завершены до выхода из функции).
Примечание
Ключевая проблема использования функций, возвращающих множества, в списке выборки, а не в предложении FROM , заключается в том, что при вызове в одном списке выборки нескольких таких функций, результат будет не вполне разумным. (На самом деле, если вы сделаете это, вы получите выходные строки в количестве, равном наименьшему общему кратному чисел строк, которые будут выданы всеми функциями, возвращающими множества.) Синтаксис LATERAL даёт более ожидаемые результаты при вызове нескольких таких функций и поэтому рекомендуется использовать его.
36.4.9. Функции SQL , возвращающие таблицы ( TABLE )
Есть ещё один способ объявить функцию, возвращающую множества, — использовать синтаксис 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 .
36.4.10. Полиморфные функции SQL
Функции SQL могут быть объявлены как принимающие и возвращающие полиморфные типы anyelement , anyarray , anynonarray , anyenum и anyrange . За более подробным объяснением полиморфизма функций обратитесь к Подразделу 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 row)Обратите внимание на приведение типа 'a'::text , определяющее, что аргумент имеет тип text . Оно необходимо, если аргумент задаётся просто строковой константой, так как иначе он будет воспринят как имеющий тип unknown , а массив типов unknown является недопустимым. Без этого приведения вы получите такую ошибку:
ERROR: could not determine polymorphic type because input has type "unknown"(ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип "unknown")
Функция с полиморфными аргументами может иметь фиксированный тип результата, однако обратное не допускается. Например:
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 function returning a polymorphic type must have at least one polymorphic argument.
(ОШИБКА: не удалось определить тип результата; ПОДРОБНОСТИ: Функция, возвращающая полиморфный тип, должна иметь минимум один полиморфный аргумент.)
Полиморфизм можно применять и с функциями, имеющими выходные аргументы. Например:
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 | (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.4.11. Функции 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 считает, что им назначено правило сортировки по умолчанию для их типа данных (обычно это то же правило сортировки, что определено по умолчанию для базы данных, но оно может быть и другим для параметров доменных типов).
Поведение сортируемых параметров можно воспринимать как ограниченную форму полиморфизма, применимую только к текстовым типам данных.
Пред. Наверх След. 36.3. Пользовательские функции Начало 36.5. Перегрузка функций