Оператор FORMULA

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

Синтаксис

FORMULA [NULL] [className [valueId]] [syntaxType1] text1, ..., [syntaxTypeN] textN [(classId1 [paramId1], ..., classIdK [paramIdK])] [NULL]

Описание

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

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

Параметры

• 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 [paramId1], ..., classIdK [paramIdK]

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

  Используемые в тексте формулы параметры передаются как входы в 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;