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

  • без ключевого слова — свойство не NULL, когда все его аргументы не NULL. Формула обязана вернуть для них не-NULL значение, а при NULL в любом аргументе — NULL: SQL-функции, возвращающей значение для NULL на входе (concat, COALESCE, string_to_array с NULL-разделителем), нужна явная проверка (CASE WHEN $2 IS NULL THEN NULL ELSE ... END), иначе значение, читаемое как есть, расходится с условием.
  • перед className — формула может возвращать NULL и для не-NULL аргументов: свойство не NULL, когда вычисленное формулой значение не NULL, поэтому формула выполняется и над 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-аргументами, свойство не NULL, когда не NULL хотя бы один аргумент
firstNonNull = FORMULA TEXT 'COALESCE($1, $2)' (TEXT name, TEXT login) NULL;