Оператор FORMULA

Оператор FORMULA - создание свойства, реализующего пользовательскую формулу.

Синтаксис

FORMULA [NULL] [className [valueId]] implList [( paramList )] [NULL]

Где implList определяется как:

[syntaxType1] text1, ..., [syntaxTypeN] textN

А paramList определяется как:

classId1 [paramId1], ..., classIdK [paramIdK]

Описание

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

FORMULA — контекстно-независимый оператор-свойство: он не может стоять внутри выражений. Использовать его можно в правой части =-объявления или анонимно внутри скобок в применении оператора JOIN.

Что именно создаёт оператор — результирующий класс по умолчанию, ослабления обработки NULL и табличный режим — описано в абстракции пользовательской формулы; эта статья описывает только то, как эти варианты записываются.

Параметры

• NULL

  Ключевое слово, которое ослабляет правила обработки NULL по умолчанию у нетабличной формулы (в табличном режиме обе позиции игнорируются). Две формы по нарастанию силы:

  • перед className — объявляет, что свойство может возвращать NULL, даже если все значения параметров не NULL. Без этой формы (и без завершающей ниже) формула обязана для не-NULL аргументов всегда возвращать не-NULL значение — невыполнение этого условия может привести к непредсказуемым результатам.
  • в конце — формула принимает NULL-значения параметров и выполняется над ними; без него NULL-аргумент сразу даёт NULL-результат без обращения к формуле. Эта форма поглощает начальную — раз формула сама видит NULL-аргументы и решает, что вернуть, отдельное объявление о возможности NULL-результата ничего не добавляет.

• className

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

• valueId

  Идентификатор или строковый литерал сразу после className. Задаёт имя колонки-значения в таблице, возвращаемой формулой — обязателен для табличных формул (см. список параметров ниже) и игнорируется для нетабличных. Форма со строковым литералом обычно используется для указания существующей колонки во внешней таблице; форма с идентификатором — для значения, возвращаемого set-returning функцией.

• syntaxType1, ..., syntaxTypeN

  Ключевые слова, определяющие типы диалектов SQL. На данный момент поддерживаются следующие типы:

  • PG - синтаксис PostgreSQL
  • MS - синтаксис MS SQL Server

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

• text1, ..., textN

  Строковые литералы, каждый из которых содержит формулу в SQL-синтаксисе. Параметры формулы подставляются как $1, $2, ... по позиции либо как $paramId, если у параметра есть имя. Нумерация позиционных параметров начинается с 1. Если задано несколько реализаций (по одной на диалект плюс опционально умолчательная), все они должны описывать один и тот же набор параметров — несовпадение арности между ними отклоняется на этапе разбора.

• classId1 ... classIdK

  Классы параметров формулы. Если список параметров опущен, классы параметров берутся из внешнего объявления свойства. Арность результирующего свойства равна большему из K и максимального позиционного индекса, на который ссылается текст формулы; позиционные обращения за пределы K расширяют список автогенерируемыми параметрами.

• paramId1 ... paramIdK

  Имена, по которым текст формулы ссылается на параметры как $paramId вместо позиции; каждое — идентификатор или строковый литерал. Параметр может быть без имени.

  Используемые в тексте формулы параметры передаются как входы в SQL-выражение в момент вычисления; неиспользуемые становятся ключевыми колонками возвращаемой таблицы (см. valueId выше для колонки-значения) и должны совпадать по именам с колонками, которые возвращает SQL-выражение. Единственный неиспользуемый параметр класса INTEGER с именем row — отдельный случай: платформа подставляет для него номера строк через ROW_NUMBER() OVER (), поэтому такая идиома работает с любым табличным SQL-выражением.

Примеры

// свойство с двумя параметрами: округляемым числом и количеством знаков после запятой
round(number, digits) = FORMULA 'round(CAST(($1) as numeric),$2)';

// свойство, преобразовывающее значение, переданное в качестве аргумента, к строковому типу длиной в 15 символов.
toString15(str) = FORMULA BPSTRING[15] 'CAST($1 AS character(15))';

// свойство с двумя различными реализациями для разных диалектов SQL
jumpWorkdays = FORMULA NULL DATE PG 'jumpWorkdays($1, $2, $3)', MS 'dbo.jumpWorkdays($1, $2, $3)';

// обращение к параметрам по имени: $json / $field берутся из внешней сигнатуры
field (JSON json, STRING field) = FORMULA JSON 'jsonb_extract_path($json, $field)';

// явный список параметров с именами для $date / $time
toDateTime = FORMULA DATETIME '$date + $time' (DATE date, TIME time);

// табличная формула: `row` не используется в тексте, поэтому становится ключевой колонкой
// таблицы, возвращаемой jsonb_array_elements; `value` задаёт имя колонки-значения
array (JSON json, INTEGER row) = FORMULA JSON value 'jsonb_array_elements($json)';

// табличная формула с именованной ключевой колонкой: jsonb_each возвращает строки (key, value);
// `key` (неиспользуемый параметр) и `value` (valueId) совпадают с именами колонок этой записи
map (JSON json, STRING key) = FORMULA JSON value 'jsonb_each($json)';

// подключение внешней таблицы: текст формулы — имя таблицы, строковый литерал после
// STRING задаёт имя колонки-значения, а `key0` — ключевая колонка этой внешней таблицы
country (LONG key0) = FORMULA STRING 'country_name' '_auto_country';

// завершающее NULL: формула сама решает, что вернуть, когда аргумент равен NULL
coalesceName = FORMULA TEXT 'COALESCE($1, \'(unknown)\')' (TEXT name) NULL;