Преобразование типов

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

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

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

Обзор преобразования типов

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

Сканер/анализатор QHB разделяет лексические элементы на пять основных категорий: целые числа, числа, отличные от целых, строки, идентификаторы и ключевые слова. Константы большинства не числовых типов сначала классифицируются как строки. В определении языка SQL допускается указывать имена типов в строках, и этот механизм можно применять в QHB, чтобы направить анализатор по верному пути. К примеру, запрос:

SELECT text 'Origin' AS "label", point '(0,0)' AS "value";

 label  | value
--------+-------
 Origin | (0,0)
(1 row)

содержит две строковые константы, типа text и типа point. Если для строковой константы не указан тип, то изначально ей присваивается фиктивный тип unknown, который может быть разрешен на более поздних этапах, как описано ниже.

Существует четыре основных структурных компонента SQL, которым требуются различные правила преобразования типов в анализаторе QHB:

Вызовы функций

Система типов QHB во многом сформирована вокруг обширного набора функций. У функций может быть один или несколько аргументов. Поскольку QHB позволяет перегружать функции, само по себе имя функции не является уникальным идентификатором вызываемой функции; анализатор должен выбрать правильную функцию, исходя из типов данных переданных аргументов.

Операторы

QHB допускает выражения с префиксными (с одним аргументом) и инфиксными (с двумя аргументами) операторами. Как и функции, операторы можно перегружать, поэтому с ними тоже существует проблема выбора правильного оператора.

Сохранение значений

Операторы SQL INSERT и UPDATE помещают результаты выражений в таблицу. Выражения в операторе должны соответствовать типам целевых столбцов и, возможно, преобразовываться в них.

UNION, CASE и связанные конструкции

Поскольку все результаты запроса объединяющего оператора SELECT должны оказаться в одном наборе столбцов, типы результатов каждого предложения SELECT должны согласовываться и преобразовываться в единый набор. Аналогично результирующие выражения конструкции CASE должны преобразовываться в общий тип, чтобы выражение CASE в целом имело известный выходной тип. Схожим образом, для некоторых других конструкций, например ARRAY[] и функций GREATEST и LEAST, требуется определение общего типа для нескольких подвыражений.
.

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

Дополнительная эвристика, предоставляемая анализатором, позволяет использовать улучшенное определение корректного приведения в группах типов, допускающих неявные приведения. Типы данных разделяются на несколько базовых категорий типов, включающих boolean, numeric, string, bitstring, datetime, timespan, geometric, network и пользовательские типы. (Полный список см. в таблице Коды typcategory; однако обратите внимание, что также имеется возможность создать свои категории типов.) В каждой категории может быть один или несколько предпочитаемых типов, которые будут иметь преимущество при выборе возможных типов. Тщательно отбирая предпочитаемые типы и доступные неявные приведения, можно добиться того, что неоднозначные выражения (с несколькими возможными решениями при синтаксическом анализе) будут разрешаться подходящим способом.

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

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

  • Если запросу не нужно неявное преобразование типа, у анализатора и исполнителя не должно возникать лишних издержек. То есть, если запрос хорошо сформулирован и типы уже совпадают, этот запрос должен выполняться без дополнительной обработки в анализаторе и без лишних вызовов неявных преобразований.

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

Операторы

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

Разрешение типа оператора

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

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

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

    1. Если один аргумент при вызове двоичного оператора имеет тип unknown, то для данной проверки предположить, что он имеет тот же тип, что и другой аргумент. При вызове оператора с двумя аргументами unknown или префиксного оператора с одним аргументом unknown совпадение на этом шаге найдено не будет.

    2. Если один аргумент при вызове двоичного оператора имеет тип unknown, а другой — тип домена, проверить, есть ли оператор, принимающий конкретно базовый тип домена с обеих сторон; если таковой имеется, использовать его.

  3. Искать наиболее подходящий.

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

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

    3. Просмотреть всех кандидатов и оставить операторы с наиболее точно совпадающими входными типами. Оставить всех кандидатов, если ни у одного нет точных совпадений. Если остается только один кандидат, использовать его; в противном случае перейти к следующему шагу.

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

    5. Если какие-либо входные аргументы имеют тип unknown, проверить категории типов, принимаемых в этих позициях аргументов оставшимися кандидатами. Для каждой позиции выбрать категорию string, если какой-либо кандидат принимает эту категорию. (Эта склонность к строкам обоснована, поскольку литерал типа unknown выглядит как строка.) Иначе, если все оставшиеся кандидаты принимают одну категорию типов, выбрать эту категорию; в противном случае констатировать неудачу, поскольку сделать верный выбор без дополнительных подсказок нельзя. Затем отбросить кандидатов, не принимающих выбранную категорию типов. Далее, если какой-либо кандидат принимает предпочитаемый тип в этой категории, отбросить кандидатов, принимающих для этого аргумента не предпочитаемые типы. Оставить всех кандидатов, если ни один не прошел эти проверки. Если остается только один кандидат, использовать его; в противном случае перейти к следующему шагу.

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

Ниже мы приводим несколько примеров.

Пример 1. Разрешение типа для оператора квадратного корня

В стандартном каталоге определен только один оператор квадратного корня (префиксный |/) и он принимает аргумент типа double precision. В данном выражении запроса лексический анализатор изначально присваивает аргументу тип integer:

SELECT |/ 40 AS "square root of 40";
 square root of 40
-------------------
 6.324555320336759
(1 row)

Поэтому синтаксический анализатор преобразует тип этого операнда, и запрос становится равнозначен:

SELECT |/ CAST(40 AS double precision) AS "square root of 40";

Пример 2. Разрешение типа для оператора конкатенации строк

Строкоподобный синтаксис используется при работе со строковыми типами и со сложными типами расширений. Строки с неуказанным типом сопоставляются с наиболее подходящими операторами-кандидатами.

Пример с одним неуказанным аргументом:

SELECT text 'abc' || 'def' AS "text and unknown";

 text and unknown
------------------
 abcdef
(1 row)

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

Ниже приведена конкатенация двух значений неуказанных типов:

SELECT 'abc' || 'def' AS "unspecified";

 unspecified
-------------
 abcdef
(1 row)

В этом случае изначально нет подсказки, какой тип использовать, поскольку в данном запросе никакие типы не указаны. Поэтому синтаксический анализатор просматривает всех операторов-кандидатов и находит операторов, принимающих аргументы категорий string и bit-string. Поскольку категория string является предпочтительной (при наличии), выбирается она, а затем в качестве конкретного типа для разрешения литералов неизвестного типа используется предпочитаемый для строк тип text.

Пример 3. Разрешение типа для оператора абсолютного значения и отрицания

В каталоге операторов QHB для префиксного оператора @ имеется несколько записей, реализующих операции абсолютного значения для различных числовых типов данных. Одна из этих записей предназначена для типа float8, который является предпочитаемым типом в категории числовых типов. Таким образом, столкнувшись с входным значением типа unknown, QHB будет использовать эту запись:

SELECT @ '-4.5' AS "abs";
 abs
-----
 4.5
(1 row)

Здесь система неявно разрешила литерал неизвестного типа как имеющий тип float8, прежде чем применить выбранный оператор. Мы можем удостовериться, что был применен именно float8, а не какой-то другой тип:

SELECT @ '-4.5e500' AS "abs";

ERROR:  "-4.5e500" is out of range for type double precision

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

SELECT ~ '20' AS "negation";

ERROR:  operator is not unique: ~ "unknown"
HINT:  Could not choose a best candidate operator. You might need to add
explicit type casts.
-- ОШИБКА: оператор не уникален: ~ "unknown"
-- ПОДСКАЗКА: Не удалось выбрать наилучшую кандидатуру оператора. Возможно, вам
-- нужно добавить явные приведения типов.

Это происходит из-за того, что система не может решить, какой из нескольких возможных операторов ~ следует предпочесть. Мы можем помочь ей, добавив явное приведение:

SELECT ~ CAST('20' AS int8) AS "negation";

 negation
----------
      -21
(1 row)

Пример 4. Разрешение типа для оператора включения в массив

Ниже приведен еще один пример разрешения оператора с одним известным и одним неизвестным аргументами:

SELECT array[1,2] <@ '{1,2,3}' as "is subset";

 is subset
-----------
 t
(1 row)

В каталоге операторов QHB имеется несколько записей для инфиксного оператора <@, но только два из них могут принять целочисленный массив слева: оператор включения массива (anyarray <@ anyarray) и оператор включения диапазона (anyelement <@ anyrange). Поскольку ни один из этих полиморфных псевдотипов (см. раздел Псевдотипы) не считается предпочитаемым, синтаксический анализатор не может разрешить неоднозначность, исходя из этого. Однако в шаге 3.6 говорится, что он должен предположить, что литерал неизвестного типа имеет тот же тип, что и другой аргумент, то есть тип целочисленного массива. Теперь соответствовать могут только два оператора, поэтому выбирается оператор включения массива. (Если бы был выбран оператор включения диапазона, мы бы получили ошибку, поскольку у строки нет подходящего формата, чтобы быть диапазонным литералом.)

Пример 5. Нестандартный оператор с типом домена

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

CREATE DOMAIN mytext AS text CHECK(...);
CREATE FUNCTION mytext_eq_text (mytext, text) RETURNS boolean AS ...;
CREATE OPERATOR = (procedure=mytext_eq_text, leftarg=mytext, rightarg=text);
CREATE TABLE mytable (val mytext);

SELECT * FROM mytable WHERE val = 'foo';

В этом запросе не будет использоваться нестандартный оператор. Синтаксический анализатор сначала проверит, имеется ли оператор mytext = mytext (шаг 2.1), которого здесь нет, затем он рассмотрит базовый тип домена text и проверит, имеется ли оператор text = text (шаг 2.2), который здесь есть, так что он разрешит литерал типа unknown как text и использует оператор text = text. Единственный способ добиться применения нестандартного оператора — это явное приведение литерала:

SELECT * FROM mytable WHERE val = text 'foo';

чтобы оператор mytext = text был найден сразу, в соответствии с правилом точного совпадения. Если дело доходит до правил наилучшего соответствия, они активно дискриминируют операторы типов домена. Если бы они этого не делали, подобный оператор спровоцировал бы слишком много ошибок, связанных с неоднозначностью оператора, поскольку правила приведения всегда рассматривают домен как приводимый к базовому типу и наоборот, и поэтому оператор домена считался бы применимым во всех одинаковых случаях, где используется одноименный оператор с базовым типом.

Функции

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

Разрешение типа функции

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

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

    2. Если функция объявлена с параметром-массивом VARIADIC и при вызове не используется ключевое слово VARIADIC, то функция обрабатывается так, как если бы параметр-массив был заменен одним или несколькими вхождениями параметров типа его элементов (число соответствует количеству аргументов при вызове). После такого расширения по фактическим типам аргументов эта функция может совпасть с некоторой функцией с постоянным количеством аргументов. В этом случае используется функция, находящаяся в пути поиска раньше, а если эти две функции находятся в одной схеме, то предпочитается функция с постоянным количеством аргументов.
      Это создает угрозу безопасности при вызове с указанием полного имени1 любой функции с переменным количеством аргументов, найденной в схеме, где недоверенным пользователям разрешено создавать объекты. Злонамеренный пользователь может захватить контроль и выполнять произвольные функции SQL, как будто их выполняете вы. Использование вместо этого вызова с ключевым словом VARIADIC устраняет эту угрозу. Однако для вызовов с параметрами VARIADIC "any" зачастую отсутствует равнозначная формулировка, содержащая ключевое слово VARIADIC. Чтобы такие вызовы выполнялись безопасно, создание объектов в схеме функции должно разрешаться только доверенным пользователям.

    3. Функции, имеющие для параметров значения по умолчанию, считаются совпадающими с любым вызовом, в котором опущено ноль или более позиций параметров, где возможны значения по умолчанию. Если вызову соответствует больше одной такой функции, используется та, которая находится в пути поиска раньше. Если в одной схеме имеются две или более таких функций с идентичными типами параметров в позициях без значений по умолчанию (что возможно, если у них разные наборы параметров, устанавливаемых по умолчанию), система не сможет выбрать предпочитаемую и выдаст ошибку «неоднозначный вызов функции», если лучшее соответствие для вызова найти невозможно.
      Это создает угрозу безопасности при вызове с указанием полного имени1 любой функции, найденной в схеме, где недоверенным пользователям разрешено создавать объекты. Злонамеренный пользователь может создать функцию с именем уже существующей, продублировав параметры этой функции и добавив новые со значениями по умолчанию. Это исключит новые вызовы к исходной функции. Во избежание этой угрозы помещайте функции в схемы, где создавать объекты разрешено только доверенным пользователям.

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

  3. Если точное совпадение не найдено, проверить не похож ли вызов функции на запрос на специальный тип преобразования. Это происходит, если вызов функции содержит всего один аргумент, а имя функции совпадает с именем (внутренним) некоторого типа данных. Более того, аргумент функции должен быть либо литералом неизвестного типа, либо значением типа, двоично-приводимого к именованному типу данных, либо значением типа, который можно преобразовать в именованный тип данных, применив функции ввода/вывода этого типа (то есть преобразовав в стандартный строковый тип или из него). Когда эти условия выполняются, вызов функции обрабатывается как форма спецификации CAST. 2

  4. Искать наиболее подходящий.

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

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

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

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

    5. Если какие-либо входные аргументы имеют тип unknown, проверить категории типов, принимаемых в этих позициях аргументов оставшимися кандидатами. Для каждой позиции выбрать категорию string, если какой-либо кандидат принимает эту категорию. (Эта склонность к строкам обоснована, поскольку литерал типа unknown выглядит как строка.) Иначе, если все оставшиеся кандидаты принимают одну категорию типов, выбрать эту категорию; в противном случае констатировать неудачу, поскольку сделать верный выбор без дополнительных подсказок нельзя. Затем отбросить кандидатов, не принимающих выбранную категорию типов. Далее, если какой-либо кандидат принимает предпочитаемый тип в этой категории, отбросить кандидатов, принимающих для этого аргумента не предпочитаемые типы. Оставить всех кандидатов, если ни один не прошел эти проверки. Если остается только один кандидат, использовать его; в противном случае перейти к следующему шагу.

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

Обратите внимания, что правила «наилучшего соответствия» для разрешения типа операторов и функций одинаковы. Ниже мы приводим несколько примеров.

Пример 6. Разрешение типа аргумента для функции округления

Существует только одна функция round, принимающая два аргумента: первый типа numeric, а второй — integer. Поэтому в следующем запросе первый аргумент типа integer автоматически преобразуется в numeric:

SELECT round(4, 4);

 round
--------
 4.0000
(1 row)

На самом деле этот запрос трансформируется анализатором в:

SELECT round(CAST (4 AS numeric), 4);

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

SELECT round(4.0, 4);

Пример 7. Разрешение функции с переменным количеством параметров

CREATE FUNCTION public.variadic_example(VARIADIC numeric[]) RETURNS int
  LANGUAGE sql AS 'SELECT 1';
CREATE FUNCTION

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

SELECT public.variadic_example(0),
       public.variadic_example(0.0),
       public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example
------------------+------------------+------------------
                1 |                1 |                1
(1 row)

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

CREATE FUNCTION public.variadic_example(numeric) RETURNS int
  LANGUAGE sql AS 'SELECT 2';
CREATE FUNCTION

CREATE FUNCTION public.variadic_example(int) RETURNS int
  LANGUAGE sql AS 'SELECT 3';
CREATE FUNCTION

SELECT public.variadic_example(0),
       public.variadic_example(0.0),
       public.variadic_example(VARIADIC array[0.0]);
 variadic_example | variadic_example | variadic_example
------------------+------------------+------------------
                3 |                2 |                1
(1 row)

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

Пример 8. Разрешение типа для функции извлечения подстроки

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

SELECT substr('1234', 3);

 substr
--------
     34
(1 row)

Если строка объявляется как имеющая тип varchar, как может произойти, если она поступает из таблицы, анализатор попытается преобразовать ее в тип text:

SELECT substr(varchar '1234', 3);

 substr
--------
     34
(1 row)

Этот запрос трансформируется синтаксическим анализатором и по сути становится:

SELECT substr(CAST (varchar '1234' AS text), 3);

Примечание
Анализатор узнает из каталога pg_cast, что типы text и varchar двоично-совместимы, что означает, что один тип можно передать функции, принимающей другой, не выполняя физического преобразования. Таким образом, в этом случае преобразование типов на самом деле не добавляется.

И если функция вызывается с аргументом типа integer, анализатор попытается преобразовать его в text:

SELECT substr(1234, 3);
ERROR:  function substr(integer, integer) does not exist
HINT:  No function matches the given name and argument types. You might need
to add explicit type casts.
-- ОШИБКА: функция substr(integer, integer) не существует
-- ПОДСКАЗКА: Данному имени и типам аргументов не соответствует ни одна функция.
-- Возможно, вам понадобится добавить явные приведения типов.

Этот вызов не работает, поскольку для integer нет неявного приведения к text. Однако явное приведение сработает:

SELECT substr(CAST (1234 AS text), 3);

 substr
--------
     34
(1 row)

Хранимое значение

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

Преобразование типа для хранимого значения

  1. Проверить точное соответствие целевому типу.

  2. Если это не так, попытаться преобразовать выражение в целевой тип. Это возможно, если в каталоге pg_cast (см. CREATE CAST) зарегистрировано приведение присваивания между двумя типами. Иначе, если выражение является литералом неизвестного типа, содержимое этой строки будет передано на вход процедуре преобразования для целевого типа.

  3. Проверить, имеется ли приведение размера для целевого типа. Приведение размера — это приведение типа к самому себе. Если такое приведение найдено в каталоге pg_cast, применить его к выражению, прежде чем сохранить в целевом столбце. Функция, реализующая такое приведение, всегда принимает дополнительный параметр типа integer, в котором передается значение atttypmod целевого столбца (обычно это его объявленная длина, хотя интерпретация atttypmod варьирует в зависимости от типа данных), и может принимать третий параметр типа boolean, который показывает, является ли приведение явным или неявным. Функция приведения отвечает за применение любой семантики, связанной с длиной, например, проверку размера или усечение.

Пример 9. Преобразование типа хранения character

Следующий оператор показывает, что хранимое значение правильно подгоняется под размер целевого столбца, объявленного как character(20):

CREATE TABLE vv (v character(20));
INSERT INTO vv SELECT 'abc' || 'def';
SELECT v, octet_length(v) FROM vv;

          v           | octet_length
----------------------+--------------
 abcdef               |           20
(1 row)

В действительности здесь происходит следующее: два неизвестных литерала по умолчанию разрешаются как тип text, позволяя оператору || разрешиться как оператор конкатенации значений text. Затем результат оператора с типом text преобразуется в тип bpchar («blank-padded char» (символы, дополненные пробелами), внутреннее имя типа данных character) в соответствии с типом целевого столбца. (Поскольку преобразование из text в bpchar является двоично-приводимым, это преобразование не добавляет реальный вызов функции.) Наконец, в системном каталоге находится функция изменения размера bpchar(bpchar, integer, boolean), которая применяется к результату оператора и длине столбца хранения. Эта типоспецифичная функция осуществляет требуемую проверку длины и добавление заполняющих пробелов.

UNION, CASE и связанные конструкции

SQL-конструкции UNION должны сочетать возможно неодинаковые типы, чтобы превратить их в единый результирующий набор. Алгоритм разрешения применяется независимо для каждого выходного столбца запроса на объединение. Конструкции INTERSECT и EXCEPT разрешают неодинаковые типы тем же способом, что и UNION. Тот же алгоритм используется и в некоторых других конструкциях, включая CASE, ARRAY, VALUES и функции GREATEST и LEAST, для сочетания составляющих их выражений и выбора результирующего типа данных.

Разрешение типов для UNION, CASE и связанных конструкций

  1. Если все входные данные одного типа и это не unknown, разрешить как этот тип.

  2. Если какое-либо из входных значений имеет тип домена, на всех последующих шагах обращаться с ним как с имеющим базовый тип домена. 3

  3. Если все входные данные имеют тип unknown, разрешить их как тип text (предпочитаемый тип категории string). В противном случае входные значения unknown для остальных правил игнорируются.

  4. Если входные данные известных типов не относятся к одной категории типов, констатировать неудачу.

  5. Выбрать первый известный тип входных данных в качестве типа-кандидата, затем рассмотреть все остальные известные типы данных в порядке слева направо. 4 Если тип-кандидат можно неявно преобразовать в другой тип, но не наоборот, выбрать другой тип в качестве нового типа-кандидата. Затем продолжить рассматривать оставшиеся входные значения. Если на каком-либо шаге этого процесса выбирается предпочитаемый тип, прекратить дальнейшее рассмотрение значений.

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

Ниже мы приводим несколько примеров.

Пример 10. Разрешение типа с недостаточным определением в объединении

SELECT text 'a' AS "text" UNION SELECT 'b';

 text
------
 a
 b
(2 rows)

Здесь литерал 'b' неизвестного типа будет разрешен как тип text.

Пример 11. Разрешение типа в простом объединении

SELECT 1.2 AS "numeric" UNION SELECT 1;

 numeric
---------
       1
     1.2
(2 rows)

Литерал 1.2 имеет тип numeric, а целочисленное значение 1 можно неявно привести к типу numeric, поэтому используется этот тип.

Пример 12. Разрешение типа в объединении с переносом

SELECT 1 AS "real" UNION SELECT CAST('2.2' AS REAL);

 real
------
    1
  2.2
(2 rows)

Здесь тип real нельзя неявно привести к integer, но integer можно неявно привести к real, поэтому результирующий тип объединения разрешается как real.

Пример 13. Разрешение типа во вложенном объединении

SELECT NULL UNION SELECT NULL UNION SELECT 1;

ERROR:  UNION types text and integer cannot be matched
-- ОШИБКА: типы text и integer в UNION сопоставить нельзя

Эта ошибка возникает из-за того, что QHB обращается с множественными UNION как с вложенным множеством попарных операций; то есть для нее эта запись — то же самое, что

(SELECT NULL UNION SELECT NULL) UNION SELECT 1;

Внутренний UNION разрешается как выдающий тип text, в соответствии с приведенными выше правилами. Затем внешний UNION получает на вход типы text и integer, что и приводит к вышеуказанной ошибке. Эту проблему можно устранить, сделав так, чтобы самый левый UNION получил на вход хотя бы одно значение желаемого результирующего типа.

Операции INTERSECT и EXCEPT также разрешаются попарно. Однако другие конструкции, описанные в этом разделе, рассматривают все входные данные сразу за один шаг процедуры разрешения.

Выходные столбцы SELECT

Правила, описанные в предыдущих разделах, обеспечивают присвоение типов данных, отличных от unknown, во всех выражениях в запросах SQL, за исключением литералов с неуказанными типом, имеющих вид простых выходных столбцов команды SELECT. Например, в запросе

SELECT 'Hello World';

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

Когда SELECT является одной из ветвей конструкции UNION (либо INTERSECT или EXCEPT) или когда она находится внутри INSERT ... SELECT, это правило не применяется, поскольку приоритет имеют правила, описанные в предыдущих разделах. В первом случае тип литерала с неуказанным типом можно взять из другой ветви UNION, а во втором — из целевого столбца.

В этом контексте списки RETURNING воспринимаются так же, как выходные списки SELECT.

1

Эта угроза не возникает, если имя не дополнено схемой, поскольку путь поиска, содержащий схемы, в которых недоверенным пользователям разрешено создавать объекты, не является шаблоном безопасного использования схем.

2

Этот шаг нужен для поддержки спецификаций приведений в стиле функции в случаях, когда на самом деле функции приведения нет. Если функция приведения есть, она для удобства называется именем выходного типа, и поэтому не требует особого подхода. Дополнительные комментарии см. на справочной странице команды CREATE CAST.

3

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

4

По историческим причинам CASE обрабатывает свое предложение ELSE (если таковое имеется) как «первое» входное значение, а предложения THEN рассматриваются после него. Во всех остальных случаях «слева направо» означает порядок, в котором выражения расположены в тексте запроса.

Copyright © KVANTOM LLC, 2019-2023