PostgreSQL UDF

YQL предоставляет возможность доступа к функциям и типам данных PostgreSQL.

Имена PostgreSQL типов в YQL получаются добавлением префикса Pg к исходному имени типа.
Например PgVarchar, PgInt4, PgText. Имена pg типов (как и вообще всех типов) в YQL являются case-insensitive. На данный момент поддерживаются все простые типы данных из PostgreSQL, а также массивы.

Если исходный тип является типом массива (в PostgreSQL такие типы начинаются с подчеркивания: _int4 - массив 32-битных целых), то имя типа в YQL тоже начинается с подчеркивания – _PgInt4.

Литералы

Строковые и числовые литералы Pg типов можно создавать с помощью специальных суффиксов (аналогично простым строковым и числовым литералам).

Целочисленные литералы

Литералы с плавающей точкой

Строковые литералы

Пример:

SELECT
    1234p,             -- pgint4
    0x123pb,         -- pgint8
    "тест"pt,         -- pgtext
    123e-1000pn; -- pgnumeric
;

Литерал массива

Для построения литерала массива используется функция PgArray:

SELECT
    PgArray(1p, NULL ,2p) -- {1,NULL,2}, тип _int4
;

Конструктор литералов произвольного типа

Литералы всех типов (в том числе и Pg типов) могут создаваться с помощью конструктора литералов со следующей сигнатурой:
Имя_типа(<строковая константа>).

Напрмер:

DECLARE $foo AS String;
SELECT
    PgInt4("1234"), -- то же что и 1234p
    PgInt4(1234),   -- в качестве аргумента можно использовать литеральные константы
    PgInt4($foo),   -- и declare параметры
    PgBool(true),
    PgInt8(1234),
    PgDate("1932-01-07"),
;

Также поддерживается встроенная функция PgConst со следующей сигнатурой: PgConst(<строковое значение>, <тип>).
Такой способ более удобен для кодогенерации.

Например:

SELECT
    PgConst("1234", PgInt4), -- то же что и 1234p
    PgConst("true", PgBool)
;

Для некоторых типов в функции PgConst можно указать дополнительные модификаторы. Возможные модификаторы для типа pginterval перечислены в документации PostgreSQL.

SELECT
    PgConst(90, pginterval, "day"), -- 90 days
    PgConst(13.45, pgnumeric, 10, 1); -- 13.5
;

Операторы

Операторы PostgreSQL (унарные и бинарные) доступны через встроенную функцию PgOp(<оператор>, <операнды>):

SELECT
    PgOp("*", 123456789987654321pn, 99999999999999999999pn), --  12345678998765432099876543210012345679
    PgOp('|/', 10000.0p), -- 100.0p (квадратный корень)
    PgOp("-", 1p), -- -1p
    -1p,           -- унарный минус для литералов работает и без PgOp
;

Оператор приведения типа

Для приведения значения одного Pg типа к другому используется встроенная функция PgCast(<исходное значение>, <желаемый тип>):

SELECT
    PgCast(123p, PgText), -- преобразуем число в строку
;

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

SELECT
    PgCast('90'p, pginterval, "day"), -- 90 days
    PgCast('13.45'p, pgnumeric, 10, 1); -- 13.5
;

Преобразование значений Pg типов в значения YQL типов и обратно

Для некоторых Pg типов возможна конвертация в YQL типы и обратно. Конвертация осуществляется с помощью встроенных функций
FromPg(<значение Pg типа>) и ToPg(<значение YQL типа>):

SELECT
    FromPg("тест"pt), -- Just(Utf8("тест")) - pg типы всегда nullable
    ToPg(123.45), -- 123.45pf8
;

Список псевдонимов типов PostgreSQL при их использовании в YQL

Ниже приведены типы данных YQL, соответствующие им логические типы PostgreSQL и названия типов PostgreSQL при их использовании в YQL:

Таблица соответствия типов ToPg

Таблица соответствия типов данных YQL и PostgreSQL при использовании функции ToPg:

Таблица соответствия типов FromPg

Таблица соответствия типов данных PostgreSQL и YQL при использовании функции FromPg:

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

Чтобы вызвать PostgreSQL функцию, необходимо добавить префикс Pg:: к ее имени:

SELECT
    Pg::extract('isodow'p,PgCast('1961-04-12'p,PgDate)), -- 3pn (среда) - работа с датами до 1970 года
    Pg::generate_series(1p,5p), -- [1p,2p,3p,4p,5p] - для функций-генераторов возвращается ленивый список
;

Существует также альтернативный способ вызова функций через встроенную функцию PgCall(<имя функции>, <операнды>):

SELECT
    PgCall('lower', 'Test'p), -- 'test'p
;

При вызове функции, возвращающей набор pgrecord, можно распаковать результат в список структур, используя функцию PgRangeCall(<имя функции>, <операнды>):

SELECT * FROM
    AS_TABLE(PgRangeCall("json_each", pgjson('{"a":"foo", "b":"bar"}')));
    --- 'a'p,pgjson('"foo"')
    --- 'b'p,pgjson('"bar"')
;

Вызов агрегационных PostgreSQL функций

Чтобы вызвать агрегационную PostgreSQL функцию, необходимо добавить префикс Pg:: к ее имени:

SELECT
Pg::string_agg(x,','p)
FROM (VALUES ('a'p),('b'p),('c'p)) as a(x); -- 'a,b,c'p

SELECT
Pg::string_agg(x,','p) OVER (ORDER BY x)
FROM (VALUES ('a'p),('b'p),('c'p)) as a(x); -- 'a'p,'a,b'p,'a,b,c'p
;

Также можно использовать агрегационную PostgreSQL функцию для построения фабрики агрегационных функций с последующим применением в AGGREGATE_BY:

$agg_max = AggregationFactory("Pg::max");

SELECT
AGGREGATE_BY(x,$agg_max)
FROM (VALUES ('a'p),('b'p),('c'p)) as a(x); -- 'c'p

SELECT
AGGREGATE_BY(x,$agg_max) OVER (ORDER BY x),
FROM (VALUES ('a'p),('b'p),('c'p)) as a(x); -- 'a'p,'b'p,'c'p

В этом случае вызов AggregationFactory принимает только имя функции с префиксом Pg::, а все аргументы функции передаются в AGGREGATE_BY.

Если в агрегационной функции не один аргумент, а ноль или два и более, необходимо использовать кортеж при вызове AGGREGATE_BY:

$agg_string_agg = AggregationFactory("Pg::string_agg");

SELECT
AGGREGATE_BY((x,','p),$agg_string_agg)
FROM (VALUES ('a'p),('b'p),('c'p)) as a(x); -- 'a,b,c'p

SELECT
AGGREGATE_BY((x,','p),$agg_string_agg) OVER (ORDER BY x)
FROM (VALUES ('a'p),('b'p),('c'p)) as a(x); -- 'a'p,'a,b'p,'a,b,c'p

Логические операции

Для выполнения логических операций используются функции PgAnd, PgOr, PgNot:

SELECT
    PgAnd(PgBool(true), PgBool(true)), -- PgBool(true)
    PgOr(PgBool(false), null), -- PgCast(null, pgbool)
    PgNot(PgBool(true)), -- PgBool(false)
;