Расширение SQL

В следующих разделах мы обсудим, как вы можете расширить язык запросов SQL, добавив:

Как работает расширяемость

QHB хорошо расширяем, потому что его работа основана на каталоге. Если вы знакомы со стандартными системами реляционных баз данных, вы знаете, что они хранят информацию о базах данных, таблицах, столбцах и т. д. в так называемых системных каталогах (иногда называемых словарём базы данных). Каталоги доступны пользователю в виде таблиц, похожих на любые другие таблицы, но СУБД хранит в них свою внутреннюю бухгалтерию. Одним из ключевых отличий между QHB и большинством систем баз данных является то, что QHB хранит гораздо больше информации в своих каталогах: не только информацию о таблицах и столбцах, но также информацию о всех существующих типах данных, функциях, методах доступа и так далее. Эти таблицы могут быть изменены пользователем, а поскольку QHB основывает свою работу на этих таблицах, то это означает, что QHB может быть расширен пользователями. Для сравнения, большинство систем баз данных могут быть расширены только путем изменения жестко закодированных процедур в их исходном коде или путем загрузки модулей, специально написанных поставщиком СУБД.

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

Система типов QHB

Типы данных QHB можно разделить на базовые, контейнерные, доменные типы и псевдотипы.

Базовые типы

Базовые типы, такие как integer, реализуются ниже уровня языка SQL (на низкоуровневом языке, например, C, Rust). Они обычно соответствуют тому, что называют абстрактными типами данных.

Встроенные базовые типы описаны в главе Типы данных.

Перечисления (enum) могут рассматриваться как подкатегория базовых типов. Но они могут быть созданы с использованием только SQL, без какого-либо низкоуровневого программирования. Обратитесь к разделу Перечисляемые типы за дополнительной информацией.

Контейнерные типы

QHB имеет три «контейнерных» типа, т.е. типа, который содержат внутри себя несколько значений другого типа. Это массивы, составные типы и диапазоны.

Массивы могут содержать несколько значений одного типа. Тип массива создается автоматически для каждого базового, составного, диапазонного и доменного типов. А вот массивов массивов нет. С точки зрения системы типов, многомерные массивы не отличаются от одномерных. Обратитесь к разделу Массивы за дополнительной информацией.

Составные типы(composite type), или кортежи — такой тип имеет одна строка таблицы. Для каждой таблицы автоматически создаётся тип её строки (его имя совпадает с именем таблицы), но составной тип может существовать и без привязки к таблице, его можно создать командой CREATE TYPE. Составной тип — это набор именованых полей некоторых других типов. Значением составного типа является строка или кортеж. Обратитесь к разделу Составные типы за дополнительной информацией.

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

Доменные типы

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

Псевдо-типы

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

Полиморфные типы

Пять псевдотипов, представляющих особый интерес: anyelement, anyarray, anynonarray, anyenum и anyrange, которые в совокупности называются полиморфными типами. Любая функция, объявленная с использованием этих типов, называется полиморфной функцией. Полиморфная функция может работать со многими различными типами данных, причем конкретный тип выводится из данных, фактически переданных ей в конкретном вызове.

Полиморфные аргументы и результаты разрешаются в конкретный тип данных при разборе запроса, вызывающего полиморфную функцию. Они связаны друг с другом следующим образом: хотя anyelement может быть любого типа, все anyelement в конкретной функции должны быть того же типа. Аргументы и результаты, объявленные как anyarray/anyrange должны быть массивом/диапазоном, базовый тип (тип элементов) которых такой же, как и anyelement других аргументов.

anynonarray обрабатывается точно так же, как anyelement, но добавляет дополнительное ограничение, что фактический тип не должен быть типом массива. anyenum обрабатывается точно так же, как anyelement, но добавляет дополнительное ограничение, что фактический тип должен быть типом enum.

Таким образом, когда несколько аргументов объявлены с полиморфным типом, выбирается только 1 конкретный тип, и все anyelement/anyenum/anynonarray будут этого типа, а все anyarray будут массивами этого типа. Например, функция, объявленная как equal(anyelement, anyelement) будет принимать любые два входных значения, но одинакового типа. Функция equal2(anyelement, anyenum) тоже принимает два аргумента одинакового типа, а значит оба из них должны быть одинаковыми перечислениями (enum).

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

my_subscript(anyarray, integer) returns anyelement

Это объявление требует, чтобы первый аргумент был массивом и позволяет анализатору выводить правильный тип результата из фактического типа первого аргумента. А если объявить функцию как f(anyarray) returns anyenum, то она будет принимать только массивы перечисляемых типов.

Функция с переменным числом аргументов (которая принимает переменное число аргументов, см. Функции SQL с переменным числом аргументов) тоже может быть полиморфной: для этого надо объявить ее последний параметр как VARIADIC anyarray. При сопоставлении аргументов и определения фактического типа такая функция ведет себя так же, как если бы вы записали соответствующее число параметров типа anynonarray.

Пользовательские функции

В QHB есть четыре вида функций:

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

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

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

Пользовательские процедуры

Процедура — это объект базы данных, похожий на функцию. Различия следующие:

  • процедура не может возвращать значения (функция тоже может не возвращать, если не хочет);

  • функции вызываются как часть запроса или вообще любого DML, а процедура вызывается явно с помощью оператора CALL, также есть API для вызова именно процедуры, отдельное от API для выполнения произвольного DML;

  • в процедуре можно начать/зафиксировать/откатить транзакцию;

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

Рекомендации по созданию пользовательских функций в оставшейся части этой главы также применимы к процедурам, за исключением того, что надо использовать команду CREATE PROCEDURE, нет возвращаемого значения и некоторые другие опции, такие как STRICT, неприменимы.

Функции и процедуры вместе также называются рутинами (ROUTINE). Существуют команды ALTER ROUTINE и DROP ROUTINE, которые можно применить, даже когда неизвестно, процедура это или функция. А команды CREATE ROUTINE нет, нужно выбрать, что именно хотите создать.

Функции на языке запросов (SQL)

Функции SQL выполняют произвольный список операторов SQL, возвращая результат последней выборки (SELECT) в списке. Если функция не помечена как возвращающая множество (SETOF), то будет возвращена только первая строка результата последней выборки. (Если не задан ORDER BY, то порядок строк не определён, и первая строка может выбираться недетерминировано.) Если последний запрос вообще не вернул строк, то результатом функции будет NULL. Также последняя команда может быть модификацией (INSERT, UPDATE или DELETE) с указанием RETURNING, опять же будет возвращена одна первая строка.

Второй вариант — функции, возвращающие SETOF sometype или, что тоже самое, TABLE(columns). В этом случае возвращаются все строки результата последней выборки (или модификации с указанием RETURNING). Более подробная информация приведена ниже.

Тело функции SQL должно быть списком операторов SQL, разделенных точкой с запятой. Точка с запятой после последнего оператора является необязательной. Если не объявлено, что функция возвращает void, последним оператором должен быть SELECT или INSERT, UPDATE или DELETE с указанием RETURNING.

Любой набор команд на языке SQL можно собрать вместе и объявить функцией. Помимо выборок SELECT, команды могут включать модификации (INSERT, UPDATE и DELETE), а также другие команды SQL. (В SQL-функциях вы не можете использовать команды управления транзакциями, например, COMMIT, SAVEPOINT и некоторые служебные команды, например, VACUUM). Однако последняя команда должна быть командой SELECT или иметь указание RETURNING, и что вернёт эта команд, то и будет возвращаемым значением функции. Можно сделать функцию, которая что-то делает, но ничего не возвращает, в этом случае объявите её как возвращающую void. Например, эта функция удаляет строки с отрицательными зарплатами из таблицы emp:

CREATE FUNCTION clean_emp() RETURNS void AS '
    DELETE FROM emp
        WHERE salary < 0;
' LANGUAGE SQL;

SELECT clean_emp();

 clean_emp
-----------
(1 row)

Замечание
Тело SQL-функции анализируется целиком, прежде чем выполнить что-либо. Хотя функция SQL может содержать команды, которые изменяют системные каталоги (например, CREATE TABLE), эффекты таких команд не будут видны во время парсинга последующих команд в функции. Так, например, если функция начинается так:
CREATE TABLE foo (...);
INSERT INTO foo VALUES(...);
— она не запустится, потому что она содержит обращение к несуществующей таблице foo, а таблица не создастся, потому что функция невалидна целиком и не запускается. В такой ситуации рекомендуется переписать функцию на PL/pgSQL.

Синтаксис команды CREATE FUNCTION требует, чтобы тело функции было записано как строковая константа. Обычно наиболее удобно использовать долларовые кавычки (см. раздел Строковые константы в долларовых кавычках) для строковой константы. Если вы решите использовать обычный синтаксис строковой константы с одинарными кавычками, вы должны удваивать одинарные кавычки (’) и обратную косую черту (\) в теле функции (согласно правилам экранирования, см. раздел Строковые константы).

Аргументы для функций SQL

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

Чтобы использовать имя, объявите аргумент функции с именем, а затем просто напишите это имя в теле функции. Если имя аргумента совпадает с именем любого столбца в текущей команде SQL, имя столбца будет иметь приоритет. Чтобы победить это, укажите имя аргумента с именем самой функции в качестве префикса, то есть function_name.argument_name. Если такое квалифицированное имя будет всё ещё конфликтовать с квалифицированным именем столбца, снова выберется столбец, а не аргумент. Чтобы преодолеть и это, присвойте алиас таблице, чтобы у столбца стало другое квалифицированное имя.

В более старом "нумерованном" подходе на аргументы ссылаются с использованием синтаксиса $n; тогда $1 относится к первому входному аргументу, $2 ко второму и т. д. Это будет работать независимо от того, был ли конкретный аргумент объявлен с именем.

Если аргумент имеет составной тип, то к полям можно обратиться через точку (т.н. dot notation), например, argname.fieldname или $1.fieldname. Здесь опять могут быть проблемы с совпадением имени, и может потребоваться указать имя аргумента с именем функции в качестве префикса, чтобы однозначно сослаться на поле аргумента.

Аргументы функции SQL могут использоваться только как значения данных, но не как идентификаторы. Так, например, это разумно:

INSERT INTO mytable VALUES ($1);

а это не сработает

INSERT INTO $1 VALUES (42);

Функции SQL c базовыми типами

Простейшая возможная функция SQL не имеет аргументов и просто возвращает значение базового типа, например, integer:

CREATE FUNCTION one() RETURNS integer AS $$
    SELECT 1 AS rrres;
$$ LANGUAGE SQL;

-- Альтернативный синтаксис строкового литерала:
CREATE FUNCTION one() RETURNS integer AS '
    SELECT 1 AS rrres;
' LANGUAGE SQL;

SELECT one();

 one
-----
   1

Обратите внимание, что внутри функции мы определили псевдоним rrres для столбца результата, но этот псевдоним не виден вне функции, и результат обозначен как one вместо rrres.

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

-- Вариант с именованными аргументами:
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
    SELECT x + y;
$$ LANGUAGE SQL;

-- Альтернативый вариант с нумерованными аргументами:
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

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

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT 1;
$$ LANGUAGE SQL;

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

SELECT tf1(17, 100.0);

В этом примере мы выбрали имя accountno для первого аргумента, но оно совпадает с именем столбца в таблице bank. В команде UPDATE accountno ссылается на столбец bank.accountno, а для ссылки на аргумент необходимо использовать tf1.accountno. Конечно, мы могли бы избежать этого, использовав другое имя для аргумента.

На практике, вероятно, хотелось бы вернуть из функции что-то более полезное, чем константа 1, поэтому более вероятное определение:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno;
    SELECT balance FROM bank WHERE accountno = tf1.accountno;
$$ LANGUAGE SQL;

Функция корректирует баланс и возвращает новый баланс. То же самое можно сделать одной командой, используя RETURNING:

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tf1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

Результат последнего SELECT или RETURNING не обязан в точности соответствовать типу результата. QHB может автоматически привести результат к нужному типу, если это можно сделать неявным преобразованием (implicit cast) или присваивающим преобразованием (assignment cast). Либо вы можете использовать явное преобразование типа. Например, предположим, что мы хотим, чтобы предыдущая функция add_em возвращала тип float8. Это не сработает:

CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
    SELECT $1 + $2;
$$ LANGUAGE SQL;

потому что нет неявного преобразования integer во float. Нужно добавить явное преобразование:

CREATE FUNCTION add_em(integer, integer) RETURNS float8 AS $$
    SELECT ($1 + $2)::float8;
$$ LANGUAGE SQL;

Функции SQL с составными типами

При написании функций, работающих с аргументами составных типов, нам придётся обращаться к конкретным полям аргумента. Например, предположим, что emp является таблицей, содержащей данные о сотрудниках и, следовательно, имя составного типа строки таблицы тоже emp. Вот функция double_salary которая вычисляет, какой была бы чья-то зарплата, если её удвоить:

CREATE TABLE emp (
    name        text,
    salary      numeric,
    age         integer,
    cubicle     point
);

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
    SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

 name | dream
------+-------
 Bill |  8400

Обратите внимание на использование синтаксиса $1.salary для выбора одного поля из аргумента-строки. Также обратите внимание, как вызывающая команда SELECT использует table_name.*, чтобы взять всю текущую строку таблицы в качестве значения составного типа. На строку таблицы можно сослаться, используя только имя таблицы, например так:

SELECT name, double_salary(emp) AS dream
    FROM emp
    WHERE emp.cubicle ~= point '(2,1)';

но этот альтернативный способ не рекомендуется. Вариант table_name.* лучше читается, сразу видно, что передаётся строка таблицы, а не какая-то колонка. (См. раздел Использование составных типов в запросах для получения подробной информации об этих двух обозначениях для составного значения строки таблицы).

Иногда удобно создавать значение составного аргумента на лету. Это можно сделать с помощью конструктора типа ROW. Например, мы можем подправить данные перед передачей в функцию:

SELECT name, double_salary(ROW(name, salary * 1.1, age, cubicle)) AS dream
    FROM emp;

Можно создать функцию, которая возвращает составной тип. Вот пример функции, которая возвращает одну строку emp:

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT text 'None' AS name,
        1000.0 AS salary,
        25 AS age,
        point '(2,2)' AS cubicle;
$$ LANGUAGE SQL;

В этом примере мы заполняем все поля константами, но вместо них могли быть произвольные вычисления.

Обратите внимание на две важные вещи в реализации функции:

  • Порядок значений в списке выборки должен точно соответствовать порядку полей составного типа (= порядку столбцов в таблице). Алиасы, которые мы дали константам в new_emp(), ни на что не влияют.

  • Мы должны убедиться, что тип каждого выражения соответствует соответствующему столбцу составного типа, при необходимости вставляя преобразование типа. В противном случае мы получим такие ошибки:

ERROR:  function declared to return emp returns varchar instead of text at column 1

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

Другой способ определить ту же функцию:

CREATE FUNCTION new_emp() RETURNS emp AS $$
    SELECT ROW('None', 1000.0, 25, '(2,2)')::emp;
$$ LANGUAGE SQL;

Здесь мы создали значение анонимного составного типа, а потом привели его к нужному типу emp. В данном примере это ни чем не лучше, но в некоторых случаях это удобная альтернатива — например, если мы возвращаем кортеж, который нам вернула другая функция. Другой пример: если возвращаемое значение нашей функции — доменный тип на основе составного, то мы обязаны сделать выброрку 1 составного значения, а не отдельных полей, т.к. в последнем случае не пройдёт преобразование в доменный тип.

Функцию, возвращающую составное значение, можно использовать как значение, и как таблицу:

-- Кортеж как значение
SELECT new_emp();

         new_emp
--------------------------
 (None,1000.0,25,"(2,2)")

-- Кортеж как таблица
SELECT * FROM new_emp();

 name | salary | age | cubicle
------+--------+-----+---------
 None | 1000.0 |  25 | (2,2)

Второй способ более подробно описан в разделе Использование результата функции как таблицы.

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

SELECT (new_emp()).name;

 name
------
 None

Дополнительные скобки необходимы, чтобы не запутать парсер. Без скобок вы получите что-то вроде этого:

SELECT new_emp().name;
ERROR:  syntax error at or near "."
LINE 1: SELECT new_emp().name;
                        ^

Другой вариант — использовать функциональную нотацию для извлечения поля:

SELECT name(new_emp());

 name
------
 None

Как объяснено в разделе Использование составных типов в запросах, эти две нотации эквивалентны.

Что ещё можно сделать с результатом работы такой функции? Можно передать его в другую функцию, которая как раз ожидает аргумент подходящего составного типа:

CREATE FUNCTION getname(emp) RETURNS text AS $$
    SELECT $1.name;
$$ LANGUAGE SQL;

SELECT getname(new_emp());
 getname
---------
 None
(1 row)

Функции SQL с выходными параметрами

Альтернативный способ описания результатов функции — с помощью выходных параметров, как в этом примере:

CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
AS 'SELECT x + y'
LANGUAGE SQL;

SELECT add_em(3,7);
 add_em
--------
     10
(1 row)

Это существенно не отличается от версии add_em показанной в предыдущем разделе. Реальное назначение выходных параметров заключается в том, чтобы предоставлять удобный способ определения функций, которые возвращают несколько значений. Например,

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
AS 'SELECT x + y, x * y'
LANGUAGE SQL;

 SELECT * FROM sum_n_product(11,42);
 sum | product
-----+---------
  53 |     462
(1 row)

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

CREATE TYPE sum_prod AS (sum int, product int);

CREATE FUNCTION sum_n_product (int, int) RETURNS sum_prod
AS 'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

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

Также обратите внимание, что выходные параметры не указываются при вызове такой функции из SQL; cнаружи функции выходные параметры ведут себя в точности как возвращаемые значения. Сигнатура функции в QHB определяется только входными параметрами, поэтому, например, вы можете удалить функцию sum_n_product любым из следующих вариантов:

DROP FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int);
DROP FUNCTION sum_n_product (int, int);

Параметры могут быть помечены как IN (по умолчанию), OUT, INOUT или VARIADIC. INOUT-параметр служит как входным параметром (частью списка входных аргументов), так и выходным параметром (частью кортежа результата). VARIADIC-параметр являются входными параметром, но обрабатываются особым образом, это описано в следующем разделе.

Замечание
Если у функции есть OUT- или INOUT-параметры, то у неё не может быть возвращаемого значения (RETURNS) и наоборот. Это два разных стиля описания возвращаемых значений; OUT-параметры — универсальный способ, а RETURNS — более красивый и наглядный.

Функции SQL с переменным числом аргументов

Функции SQL могут быть объявлены так, чтобы принимать переменное число аргументов, при условии, что все «необязательные» аргументы имеют один и тот же тип. Необязательные аргументы будут переданы функции в виде массива. Для этого последний аргумент функции должен быть массивом и помечен как VARIADIC. Например:

CREATE FUNCTION mleast(VARIADIC arr numeric[]) RETURNS numeric AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT mleast(10, -1, 5, 4.4);
 mleast
--------
     -1
(1 row)

Технически все предоставленные аргументы от позиции VARIADIC и дальше собираются в одномерный массив, как если бы вы написали

SELECT mleast(ARRAY[10, -1, 5, 4.4]);    -- прямо в таком виде не сработает

Однако вы не можете написать так — или, по крайней мере, это не будет соответствовать определению этой функции. VARIADIC-параметр ожидает один или несколько элементов базового типа массива, а не значение-массив.

Иногда полезно иметь возможность передавать уже имеющий массив в функцию с переменным числом аргументов; это особенно удобно, когда одна функция с переменным числом аргументов хочет передать свой переменный аргумент другой такой функции. Это можно сделать, воспользовавшись тем же ключевым словом VARIADIC:

SELECT mleast(VARIADIC ARRAY[10, -1, 5, 4.4]);

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

SELECT mleast(VARIADIC ARRAY[]::numeric[]);

Простое написание SELECT mleast() не работает, поскольку VARIADIC-аргумент должен соответствовать хотя бы одному фактическому значению. (Вы можете определить вторую функцию с именем mleast без параметров, если хотите разрешить такие вызовы).

Замечание по безопасности
Если есть схема в которой несознательные пользователи могут создавать функции, и вы вызываете функцию из этой схемы, и у неё есть VARIADIC-параметр, то всегда вызывайте её с использование синтаксиса VARIADIC ARRAY[...]. Так вы можете быть уверены, что вызываете именно эту функцию (если её не удалят, конечно). Если вы передаёте просто несколько чисел через запятую в VARIADIC-параметр, то злоумышленник может создать функцию с тем же именем, но с конкретным набором параметров, и ваш код начнёт вызывать эту функцию.

Даже если у VARIADIC-параметра есть имя, это имя не просто использовать для сопоставления параметров по именам при вызове функции (см. раздел Вызов функции). Например, следующие варианты все не работают:

SELECT mleast(arr => 10);
SELECT mleast(arr => 10, arr => 20);
SELECT mleast(arr => ARRAY[10, -1, 5, 4.4]);

Единственный работающий вариант опять использует ключевое слово VARIADIC:

SELECT mleast(VARIADIC arr => ARRAY[10, -1, 5, 4.4]);

Функции SQL со значениями аргументов по умолчанию

Функции могут быть объявлены со значениями по умолчанию для некоторых или всех входных аргументов. Значения по умолчанию подставляются, когда функция вызывается с недостаточным количеством фактических аргументов. Поскольку аргументы могут быть пропущены с конца списка, все параметры после параметра со значением по умолчанию также должны иметь значения по умолчанию. (Хотя при использовании связывания аргументов по именам при вызове функции можно задать любое подмножество аргументов, не только с конца, но надо всё-таки, чтобы позиционное задание аргументов тоже работало.)

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

Примеры:

CREATE FUNCTION foo(a int, b int DEFAULT 2, c int DEFAULT 3)
RETURNS int
LANGUAGE SQL
AS $$
    SELECT $1 + $2 + $3;
$$;

SELECT foo(10, 20, 30);
 foo
-----
  60
(1 row)

SELECT foo(10, 20);
 foo
-----
  33
(1 row)

SELECT foo(10);
 foo
-----
  15
(1 row)

SELECT foo();  -- не работает, потому что для первого аргумента нет умолчания
ERROR:  function foo() does not exist

Вместо ключевого слова DEFAULT можно также использовать =:

CREATE FUNCTION foo(a int, b int = 2, c int = 3)

Использование результата функции как таблицы

Все функции SQL можно использовать в предложении FROM запроса, но это особенно полезно для функций, возвращающих составные типы. Если функция определена как возвращающая базовый тип, функция создает таблицу из одного столбца. Если функция определена для возврата составного типа, она создает столбец для каждого поля составного типа.

Вот пример:

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE FUNCTION getfoo(int) RETURNS foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT *, upper(fooname) FROM getfoo(1) AS t1;

 fooid | foosubid | fooname | upper
-------+----------+---------+-------
     1 |        1 | Joe     | JOE
(1 row)

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

Обратите внимание, что мы получили только одну строку из функции. Это потому, что мы не использовали SETOF. Это описано в следующем разделе.

Функции SQL, возвращающие множество строк

Когда функция SQL объявляется как возвращающая SETOF sometype, последня выборка функции выполняется полностью, и каждая строка, которую она выводит, возвращается как элемент набора результатов.

Такие функции обычно используется в предложении FROM. В этом случае каждая строка, возвращаемая функцией, становится строкой псевдотаблицы, обрабатываемой запросом. Например, предположим, что таблица foo имеет то же содержимое, что и выше, и мы говорим:

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

fooid | foosubid | fooname
-------+----------+---------
    1 |        1 | Joe
    1 |        2 | Ed
(2 rows)

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

CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT product int)
RETURNS SETOF record
AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT * FROM sum_n_product_with_tab(10);
 sum | product
-----+---------
  11 |      10
  13 |      30
  15 |      50
  17 |      70
(4 rows)

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

Часто бывает полезно сконструировать результат выборки, вызывая функцию, возвращающую таблицу, несколько раз, подставляя её аргументы из результатов подзапроса. Предпочтительный способ сделать это - использовать ключевое слово LATERAL, которое описано в разделе Подзапросы LATERAL. Вот пример использования возвращающей таблицу функции для перечисления элементов древовидной структуры:

SELECT * FROM nodes;
   name    | parent
-----------+--------
 Top       |
 Child1    | Top
 Child2    | Top
 Child3    | Top
 SubChild1 | Child1
 SubChild2 | Child1
(6 rows)

CREATE FUNCTION listchildren(text) RETURNS SETOF text AS $$
    SELECT name FROM nodes WHERE parent = $1
$$ LANGUAGE SQL STABLE;

SELECT * FROM listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, child FROM nodes, LATERAL listchildren(name) AS child;
  name  |   child
--------+-----------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

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

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

SELECT listchildren('Top');
 listchildren
--------------
 Child1
 Child2
 Child3
(3 rows)

SELECT name, listchildren(name) FROM nodes;
  name  | listchildren
--------+--------------
 Top    | Child1
 Top    | Child2
 Top    | Child3
 Child1 | SubChild1
 Child1 | SubChild2
(5 rows)

В последнем SELECT обратите внимание, что для Child2, Child3 и т.д. нет выходных строк. Это происходит потому, что listchildren возвращает 0 строк для этих аргументов, поэтому строки результата не генерируются. Это такое же поведение, которое мы имели бы при внутреннем соединении с результатом функции при использовании синтаксиса LATERAL.

Поведение QHB для функции, возвращающей множество в списке выбора запроса, почти такое же, как если бы функция, возвращающая множество, была в LATERAL FROM. Например,

SELECT x, generate_series(1,5) AS g FROM tab;

почти эквивалентно

SELECT x, g FROM tab, LATERAL generate_series(1,5) AS g;

Разница проявляется только в свободе выбора планировщика. Во втором варианте планировщик может поместить g во внешнюю цикл соединения вложенными циклами, поскольку g на самом деле не зависит от tab. Это приведет к другому порядку строк в итоговом результате. Табличные функции в списке выборки всегда будут выполнятся самым внутренним циклом соединения, т.е. результат функции (функций) пробегается до конца, а только потом переходят к следующей строке FROM.

Если в списке выбора запроса несколько функций, возвращающих множество строк, то мы получим не декартово произведение их результатов, как можно было подумать, а совмещение результатов функций по номерам: для каждой строки из базового запроса есть выходная строка, использующая первый результат из каждой функции, затем выходная строка, использующая второй результат из каждой функции и так далее; если результаты какой-то функция кончились раньше, вместо них будут NULL, и так пока не кончится самая "результативная" функция. Для каждой строки из базового запроса будет столько строк, сколько строк в самом большом из результатов функций. Такое поведение аналогично тому, что вы получите, поместив эти функции в один LATERAL ROWS FROM( ... )

Табличные функции могут вызывать друг друга в списке выборки, хотя это не разрешено в секции FROM. В таких случаях каждый уровень вложенности обрабатывается отдельно, генерируя отдельные внутренний цикл, как если бы это был отдельный LATERAL ROWS FROM( ... ). Например, в

SELECT srf1(srf2(x), srf3(y)), srf4(srf5(z)) FROM tab;

функции возврата srf2, srf3 и srf5 будут запускаться в первом шаге для каждой строки tab, а затем для каждой строки того, что получилось, будут запускаться srf1 и srf4.

Функции, возвращающие множество, нельзя использовать в конструкциях условного вычисления, таких как CASE или COALESCE. Например, рассмотрим

SELECT x, CASE WHEN x > 0 THEN generate_series(1, 5) ELSE 0 END FROM tab;

Может показаться, что это должно привести к пяти повторениям входных строк, которые имеют x > 0, и одному повторению прочих; но на самом деле, поскольку generate_series(1, 5) будет выполняться в неявном элементе LATERAL FROM до того, как выражение CASE начнёт вычислять, запрос выдаст пять повторений каждой строки из tab. Чтобы избежать путаницы, такие конструкции запрещены, и вызывают ошибку разбора.

Заметка
Если последней командой функции является INSERT, UPDATE или DELETE с RETURNING, эта команда всегда будет выполняться до конца, даже если функция не объявлена с помощью SETOF или вызывающий запрос не извлечёт все строки результата (например, у него есть LIMIT). Любые дополнительные строки, созданные указанием RETURNING отбрасываются, но изменения в заданной таблице производятся (и все завершаются до возврата из функции).

Функции SQL, возвращающие таблицы

Есть еще один способ объявить функцию как возвращающую набор: использовать синтаксис RETURNS TABLE(columns). Это эквивалентно использованию одного или нескольких OUT-параметров плюс маркировке функции как возвращающей SETOF record (или, если столбец единственный, SETOF тип-столбца). Нотация RETURNS TABLE(columns) указана в последних версиях стандарта SQL и, следовательно, может быть более переносимой, чем использование SETOF.

Например, предыдущий пример суммы и произведения может быть переписан так:

CREATE FUNCTION sum_n_product_with_tab (x int)
RETURNS TABLE(sum int, product int) AS $$
    SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

Не допускается использование явных параметров OUT или INOUT с указанием RETURNS TABLE, надо выбрать один из вариантов нотации.

Полиморфные функции SQL

SQL-функции могут принимать и возвращать полиморфные типы anyelement, anyarray, anynonarray, anyenum и anyrange, обсуждавшиеся в разделе Полиморфные типы. Вот пример полиморфной функции make_array, которая создает массив из двух элементов произвольного типа данных:

CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
 intarray | textarray
----------+-----------
 {1,2}    | {a,b}
(1 row)

Обратите внимание на использование приведения типа 'a'::text чтобы указать, что аргумент имеет тип text. Это необходимо, потому что строковый литерал по умолчанию не относится к какому-то конкретному строковому типу (имеет тип unknown). Без приведения типа вы получите такую ошибку:

ERROR:  could not determine polymorphic type because input has type "unknown"

Разрешается иметь полиморфные аргументы и фиксированный тип результата, но не наоборот:

CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
    SELECT $1 > $2;
$$ LANGUAGE SQL;

SELECT is_greater(1, 2);
 is_greater
------------
 f
(1 row)

CREATE FUNCTION invalid_func() RETURNS anyelement AS $$
    SELECT 1;
$$ LANGUAGE SQL;
ERROR:  cannot determine result data type
DETAIL:  A function returning a polymorphic type must have at least one polymorphic argument.

Функции с выходными аргументами вполне могут использовать полиморфизм:

CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray)
AS 'select $1, array[$1,$1]' LANGUAGE SQL;

SELECT * FROM dup(22);
 f2 |   f3
----+---------
 22 | {22,22}
(1 row)

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

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i]) FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

SELECT anyleast(10, -1, 5, 4);
 anyleast
----------
       -1
(1 row)

SELECT anyleast('abc'::text, 'def');
 anyleast
----------
 abc
(1 row)

CREATE FUNCTION concat_values(text, VARIADIC anyarray) RETURNS text AS $$
    SELECT array_to_string($2, $1);
$$ LANGUAGE SQL;

SELECT concat_values('|', 1, 4, 2);
 concat_values
---------------
 1|4|2
(1 row)

Функции SQL и правила сортировки

Когда SQL-функция имеет один или несколько параметров такого типа, для которого применимы правила сортировки (COLLATION), правила сортировки выбираются при каждом вызове на основании фактических аргументов функции. Алгоритм выбора описан в разделе Поддержка сортировки. Если правила сортировки успешно выбраны (т.е. нет конфликтов между неявно установленными правилами сортировки аргументов), то все параметры функции считаются имеющими такое правило сортировки, и обрабатываются соответственно. Это повлияет на поведение операций, связанных со сравнением и сортировкой, внутри функции. Например, для функции anyleast описанной выше, результат

SELECT anyleast('abc'::text, 'ABC');

будет зависеть от правил сортировки базы данных по умолчанию. В локали C результат будет ABC, но во многих других локалях это будет abc.

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

SELECT anyleast('abc'::text, 'ABC' COLLATE "C");

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

CREATE FUNCTION anyleast (VARIADIC anyarray) RETURNS anyelement AS $$
    SELECT min($1[i] COLLATE "en_US") FROM generate_subscripts($1, 1) g(i);
$$ LANGUAGE SQL;

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

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

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

Перегрузка функций

С одним и тем же SQL-именем может быть определено несколько функций, если они принимают разные наборы входных аргументов. Другими словами, имена функций могут быть перегружены.

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

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

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

CREATE FUNCTION test(int, real) RETURNS ...
CREATE FUNCTION test(smallint, double precision) RETURNS ...

то не понятно, какая функция вызовется при тривиальном запросе SELECT test(1, 1.5).

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

Имя функции, принимающей один аргумент составного типа, не должно совпадать с одним из полей этого составного типа! Дело в том, что если кортеж типа T имеет поле attribute, то attribute(t) означает тоже самое, что и t.attribute. Если вы создадите свою функцию attribute(T), то вместо вашей функции будет молча вызываться t.attribute. Пользовательскую функцию всё-таки можно вызвать, используя квалифицированное имя schema.attribute(t), но лучше в такое не ввязываться.

Другой возможный конфликт — между вариативной и невариативной функциями. Например, можно создать foo(numeric) и foo(VARIADIC numeric[]). В этом случае неясно, какая из них должна вызываться в случае foo(10.1). Правило состоит в том, что используется функция, появляющаяся ранее в пути поиска, или, если две функции находятся в одной и той же схеме, выбирается невариативная.

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

CREATE FUNCTION test(int) RETURNS int
    AS 'filename', 'test_1arg'
    LANGUAGE C;
CREATE FUNCTION test(int, int) RETURNS int
    AS 'filename', 'test_2arg'
    LANGUAGE C;

test_2arg — имя нативной функции; такой формат имени — лишь одно из многих возможных соглашений, нет чётких правил на этот счёт.

Категории волатильности функций

Каждая функция имеет категорию волатильности, с возможными вариантами VOLATILE, STABLE или IMMUTABLE. VOLATILE является значением по умолчанию, если в CREATE FUNCTION не указать явно другую категорию. Категория волатильности — это обещания оптимизатору касательно поведения функции:

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

  • Функция STABLE не может изменить данные и гарантированно возвращает одинаковые результаты при одинаковых входных значения — в рамках одного запроса. Эта категория позволяет оптимизатору заменить несколько вызовов функции с одними и теми же аргументами на один вызов. В частности, если стабильная функция содержится в "правой части" условия фильтрации, то можно вычислить функцию один раз, и потом искать результат в индексе. (Для волатильных функций это недопустимо, потому что значение условия потенциально может меняться, и надо его вычислять для каждой строки).

  • Функция IMMUTABLE не может изменять данные и гарантированно будет возвращать одни и те же результаты, если всегда будут одни и те же аргументы. Эта категория позволяет оптимизатору предварительно оценить функцию, когда запрос вызывает ее с постоянными аргументами. Например, запрос типа SELECT ... WHERE x = 2 + 2 может быть упрощен на вид до SELECT ... WHERE x = 4, потому что функция, лежащая в основе оператора сложения целых чисел, помечена как IMMUTABLE.

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

Любая функция с побочными эффектами должна быть помечена как VOLATILE, чтобы оптимизатор не мог убрать ее вызов. Даже функция без побочных эффектов должна быть помечена как VOLATILE если ее значение может измениться в течение выполнения одного запроса: например, random(), currval(), timeofday(). Другим важным примером является то, что семейство функций current_timestamp квалифицируется как STABLE, поскольку их значения не изменяются в пределах транзакции.

Разница между категориями STABLE и IMMUTABLE почти незаметна, если рассуждать о простых интерактивных запросах, которые планируются и выполняются немедленно: не имеет большого значения, выполняется ли функция один раз во время планирования или один раз во время запуска запроса. Разница, если план будет сохранён и использован позже. Обозначение функции IMMUTABLE, когда это неправда, может привести к преждевременной оптимизации вызова на этапе планирования, а потом к повторному использованию устаревшего значения при использовании плана. Это опасно при использовании подготовленных операторов или внутри процедур, план которых кешируется (например, процедуры на PL/pgSQL).

Для функций, написанных на SQL или на одном из стандартных процедурных языков, существует еще одно важное свойство, определяемое категорией волатильности, а именно видимость любых изменений данных, которые уже были внесены командой SQL, вызывающей функцию. Функция VOLATILE увидит такие изменения, функция STABLE или IMMUTABLE нет. Это поведение реализовано с использованием моментальных снимков MVCC: функции STABLE и IMMUTABLE используют моментальный снимок, созданный в начале вызывающего запроса, тогда как функции VOLATILE получают новый снимок в начале запроса, который они выполняют внутри себя.

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

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

Такое же поведение снимка используется для IMMUTABLE-функций. Однако, если результат вашей функции зависит от содержимого таблиц, то она, по-видимому, не IMMUTABLE! (Результат изменится, когда поменяют содержимое таблицы). Однако QHB не запретит вам объявить такую функцию IMMUTABLE.

Распространенной ошибкой является пометка функции IMMUTABLE когда ее результаты зависят от параметра конфигурации. Например, функция, которая манипулирует временем, вполне может выдавать результаты, которые зависят от настройки TimeZone. В целях безопасности такие функции должны быть помечены как STABLE.

Замечание
QHB проверяет, чтобы функции, помеченные STABLE или IMMUTABLE, не содержали никаких команд SQL, кроме SELECT, т.к. функции, меняющие данные явно нестабильные. Однако не проверят и не предотвращает вызвать волатильную функцию (формально, если функция вызывает волатильную, то она тоже волатильная). Если вы попробуете сделать это, то заметите, что изменения данных, сделанные вызванной волатильной функцией, не видны внешней стабильной функции: она работает с моментальным снимком базы.

Функции на процедурном языке

QHB позволяет писать пользовательские функции на различных интерпретируемых("скриптовых") языках. Эти языки обычно называются процедурными языками (PL). Процедурные языки не встроены в сервер QHB; они реализованы в подгружаемых модулях. См. главу Процедурные языки и последующие главы для получения дополнительной информации.

Внутренние функции

Внутренние функции - это функции, написанные на C или на Rust, которые статически скомпонованы в сервер QHB. Определение функции состоит из её имени на C, которое не обязательно совпадает с именем, объявленным для использования в SQL. (Из соображений обратной совместимости пустое "тело" функции воспринимается как то, что имя функции на C совпадает с SQL-именем).

Обычно все внутренние функции, присутствующие на сервере, объявляются во время инициализации кластера базы данных (см. раздел Создание кластера базы данных), но пользователь может использовать CREATE FUNCTION для создания дополнительного псевдонима внутренней функции. Внутренние функции объявляются в CREATE FUNCTION с LANGUAGE internal. Например, создадим псевдоним для функции sqrt:

CREATE FUNCTION square_root(double precision) RETURNS double precision
    AS 'dsqrt'
    LANGUAGE internal
    STRICT;

Большинство внутренних функций «строгие» (STRICT).

Замечание
Не все предопределенные функции являются «внутренними» в вышеописанном смысле. Некоторые предопределенные функции написаны на SQL.

Функции на нативном языке

Пользовательские нативные функции могут быть написаны на C, Rust, С++ или других компилируемых unmanaged языках. Функции должны следовать соглашению о вызове C ("extern C", все означенные языки имеют возможность следовать такому соглашению) и соглашению QHB об именах функций, экспортируемых из библиотек. Это соглашение об именах называется "Version 1", и для разработки на языке C предоставляется макрос PG_FUNCTION_INFO_V1() для правильного оформления вашей функции (см. ниже).

Пользовательские функции компилируются в динамически загружаемые объекты (также называемые разделяемыми библиотеками) и загружаются сервером по требованию. Функция динамической загрузки — это то, что отличает «нативные» функции от «внутренних», а сами функции устроены одинаково.

Динамическая загрузка

При первом вызове пользовательской функции в конкретном загружаемом объектном файле в сеансе динамический загрузчик загружает этот объектный файл в память, чтобы вызвать функцию. Поэтому функция CREATE FUNCTION для пользовательской функции C должна указывать две части информации для функции: имя загружаемого объектного файла и C-имя (символ ссылки) конкретной функции, вызываемой в этом объектном файле. Если C-имя не указано явно, предполагается, что оно совпадает с именем функции SQL.

Следующий алгоритм используется для поиска файла общего объекта на основе имени, указанного в команде CREATE FUNCTION:

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

  2. Если имя начинается со строки $libdir, эта часть заменяется именем каталога библиотеки пакетов QHB, которое определяется во время сборки.

  3. Если имя не содержит часть каталога, поиск файла производится по пути, указанному в переменной конфигурации dynamic_library_path, см. dynamic_library_path.

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

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

Рекомендуется размещать разделяемые библиотеки относительно $libdir или по пути динамической библиотеки. Это упрощает обновление версий, если новая установка находится в другом месте. Действительный каталог, $libdir стоит $libdir можно узнать с помощью команды pg_config --pkglibdir.

Идентификатор пользователя, на котором работает сервер QHB, должен проходить путь к файлу, который вы собираетесь загрузить. Создание файла или каталога более высокого уровня нечитабельным и/или не исполняемым пользователем qhb является распространенной ошибкой.

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

Заметка
QHB не будет компилировать функцию C/Rust автоматически. Объектный файл должен быть скомпилирован перед тем, как на него будет ссылаться команда CREATE FUNCTION. См. раздел Компиляция и связывание динамически загружаемых функций для получения дополнительной информации.

Чтобы гарантировать, что динамически загружаемый объектный файл не загружается на несовместимый сервер, QHB проверяет, что файл содержит «магический блок» с соответствующим содержимым. Это позволяет серверу обнаруживать очевидные несовместимости, такие как код, скомпилированный для другой основной версии QHB. Чтобы включить магический блок, запишите это в один (и только один) из исходных файлов модуля после включения заголовка fmgr.h:

PG_MODULE_MAGIC;

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

При желании динамически загружаемый файл может содержать функции инициализации и финализации. Если файл содержит функцию с именем _PG_init, эта функция будет вызываться сразу после загрузки файла. Функция не получает параметров и должна возвращать void. Если файл содержит функцию с именем _PG_fini, эта функция будет вызвана непосредственно перед выгрузкой файла. Аналогично, функция не получает параметров и должна возвращать void. Обратите внимание, что _PG_fini будет вызываться только во время выгрузки файла, а не во время завершения процесса. (В настоящее время выгрузки отключены и никогда не произойдут, но это может измениться в будущем).

Базовые типы в функциях языка C/RUST

Чтобы знать, как писать функции на языке C/RUST, вам необходимо знать, как QHB внутренне представляет базовые типы данных и как их можно передавать в функции и из функций. Внутри QHB рассматривает базовый тип как «блок памяти». Пользовательские функции, которые вы определяете для типа, в свою очередь, определяют способ работы QHB с ним. То есть QHB будет хранить и извлекать данные только с диска и использовать ваши пользовательские функции для ввода, обработки и вывода данных.

Базовые типы могут иметь один из трех внутренних форматов:

  • передача по значению с фиксированной длиной

  • передача по ссылке, фиксированной длины

  • передача по ссылке, переменной длины

Типы по значению могут иметь длину только 1, 2 или 4 байта (также 8 байтов, если sizeof(Datum) равен 8 на вашем сервере). Вы должны быть осторожны, чтобы определить ваши типы так, чтобы они были одинакового размера (в байтах) на всех архитектурах. Например, тип long опасен, потому что на одних машинах он составляет 4 байта, а на других - 8 байтов, тогда как тип int на большинстве машин Unix составляет 4 байта. int4 реализация типа int4 на машинах Unix может быть:

/* 4-byte integer, passed by value */
typedef int int4;

(Фактический код QHB C вызывает этот тип int32, потому что в C существует соглашение, согласно которому int XX означает XX бит . Поэтому обратите внимание также на то, что тип C int8 имеет размер 1 байт. SQL-тип int8 называется int64 в C. См. также таблицу 1).

С другой стороны, типы фиксированной длины любого размера могут передаваться по ссылке. Например, вот пример реализации типа QHB:

/* 16-byte structure, passed by reference */
typedef struct
{
    double  x, y;
} Point;

Только указатели на такие типы могут использоваться при передаче их в и из функций QHB. Чтобы вернуть значение такого типа, выделите правильный объем памяти с помощью palloc, заполните выделенную память и верните указатель на нее. (Также, если вы просто хотите вернуть то же значение, что и один из ваших входных аргументов того же типа данных, вы можете пропустить лишний palloc и просто вернуть указатель на входное значение).

Наконец, все типы переменной длины также должны передаваться по ссылке. Все типы переменной длины должны начинаться с непрозрачного поля длины ровно 4 байта, которое будет установлено SET_VARSIZE; никогда не устанавливайте это поле напрямую! Все данные, которые должны быть сохранены в этом типе, должны быть расположены в памяти сразу после этого поля длины. Поле длины содержит общую длину структуры, то есть включает в себя размер самого поля длины.

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

!!! Предупреждение

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

В качестве примера мы можем определить тип text следующим образом:

typedef struct {
    int32 length;
    char data[FLEXIBLE_ARRAY_MEMBER];
} text;

Нотация [FLEXIBLE_ARRAY_MEMBER] означает, что фактическая длина части данных не указана в этом объявлении.

При манипулировании типами переменной длины мы должны быть осторожны, чтобы правильно распределить объем памяти и правильно задать поле длины. Например, если мы хотим сохранить 40 байтов в text структуре, мы можем использовать фрагмент кода, подобный этому:

#include "postgres.h"
...
char buffer[40]; /* our source data */
...
text *destination = (text *) palloc(VARHDRSZ + 40);
SET_VARSIZE(destination, VARHDRSZ + 40);
memcpy(destination->data, buffer, 40);
...

VARHDRSZ - это то же самое, что и sizeof(int32), но считается хорошим стилем использовать макрос VARHDRSZ для ссылки на размер служебной информации для типа переменной длины. Кроме того, поле длины должно быть установлено с SET_VARSIZE макроса SET_VARSIZE, а не путем простого присваивания.

Таблица 1 указывает, какой тип C/RUST соответствует какому типу SQL при написании функции на языке C/RUST, использующей встроенный тип QHB. В столбце «Определено в» указан заголовочный файл, который необходимо включить, чтобы получить определение типа. (Фактическое определение может быть в другом файле, который включен в указанный файл. Рекомендуется, чтобы пользователи придерживались определенного интерфейса). Обратите внимание, что вы всегда должны сначала включать postgres.h в любой исходный файл, поскольку он объявляет ряд вещей, которые вам понадобятся в любом случае.

Таблица 1. Эквивалентные типы C для встроенных типов SQL

Тип SQLТип CОпределено в
booleanboolpostgres.h (возможно встроенный компилятор)
boxBOX*utils/geo_decls.h
byteabytea*postgres.h
"char"char(встроенный компилятор)
characterBpChar*postgres.h
cidCommandIdpostgres.h
dateDateADTutils/date.h
smallint ( int2 )int16postgres.h
int2vectorint2vector*postgres.h
integer ( int4 )int32postgres.h
real ( float4 )float4*postgres.h
double precision ( float8 )float8*postgres.h
intervalInterval*datatype/timestamp.h
lsegLSEG*utils/geo_decls.h
nameNamepostgres.h
oidOidpostgres.h
oidvectoroidvector*postgres.h
pathPATH*utils/geo_decls.h
pointPOINT*utils/geo_decls.h
regprocregprocpostgres.h
texttext*postgres.h
tidItemPointerstorage/itemptr.h
timeTimeADTutils/date.h
time with time zoneTimeTzADTutils/date.h
timestampTimestamp*datatype/timestamp.h
varcharVarChar*postgres.h
xidTransactionIdpostgres.h

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

Версия 1 Соглашение о вызовах

Соглашение о вызовах версии 1 опирается на макросы, которые подавляют большую часть сложности передачи аргументов и результатов. Объявление C функции версии-1 всегда:

Datum funcname(PG_FUNCTION_ARGS)

Кроме того, вызов макроса:

PG_FUNCTION_INFO_V1(funcname);

должен появиться в том же исходном файле. (Обычно он написан непосредственно перед самой функцией). Этот вызов макроса не требуется для внутренних функций языка, поскольку QHB предполагает, что все внутренние функции используют соглашение версии 1. Это, однако, требуется для динамически загружаемых функций.

В функции версии 1 каждый фактический аргумент выбирается с помощью PG_GETARG_ xxx () который соответствует типу данных аргумента. В нестрогих функциях необходимо предварительно проверить нулевой аргумент, используя PG_ARGNULL_ xxx (). Результат возвращается с использованием PG_RETURN_ xxx () для возвращаемого типа. PG_GETARG_ xxx () принимает в качестве аргумента номер аргумента функции для выборки, где счет начинается с 0. PG_RETURN_ xxx () принимает в качестве аргумента фактическое значение, которое нужно вернуть.

Вот несколько примеров использования соглашения о вызовах версии 1:

#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"

PG_MODULE_MAGIC;

/* by value */

PG_FUNCTION_INFO_V1(add_one);

Datum
add_one(PG_FUNCTION_ARGS)
{
    int32   arg = PG_GETARG_INT32(0);

    PG_RETURN_INT32(arg + 1);
}

/* by reference, fixed length */

PG_FUNCTION_INFO_V1(add_one_float8);

Datum
add_one_float8(PG_FUNCTION_ARGS)
{
    /* The macros for FLOAT8 hide its pass-by-reference nature. */
    float8   arg = PG_GETARG_FLOAT8(0);

    PG_RETURN_FLOAT8(arg + 1.0);
}

PG_FUNCTION_INFO_V1(makepoint);

Datum
makepoint(PG_FUNCTION_ARGS)
{
    /* Here, the pass-by-reference nature of Point is not hidden. */
    Point     *pointx = PG_GETARG_POINT_P(0);
    Point     *pointy = PG_GETARG_POINT_P(1);
    Point     *new_point = (Point *) palloc(sizeof(Point));

    new_point->x = pointx->x;
    new_point->y = pointy->y;

    PG_RETURN_POINT_P(new_point);
}

/* by reference, variable length */

PG_FUNCTION_INFO_V1(copytext);

Datum
copytext(PG_FUNCTION_ARGS)
{
    text     *t = PG_GETARG_TEXT_PP(0);

    /*
     * VARSIZE_ANY_EXHDR is the size of the struct in bytes, minus the
     * VARHDRSZ or VARHDRSZ_SHORT of its header.  Construct the copy with a
     * full-length header.
     */
    text     *new_t = (text *) palloc(VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
    SET_VARSIZE(new_t, VARSIZE_ANY_EXHDR(t) + VARHDRSZ);

    /*
     * VARDATA is a pointer to the data region of the new struct.  The source
     * could be a short datum, so retrieve its data through VARDATA_ANY.
     */
    memcpy((void *) VARDATA(new_t), /* destination */
           (void *) VARDATA_ANY(t), /* source */
           VARSIZE_ANY_EXHDR(t));   /* how many bytes */
    PG_RETURN_TEXT_P(new_t);
}

PG_FUNCTION_INFO_V1(concat_text);

Datum
concat_text(PG_FUNCTION_ARGS)
{
    text  *arg1 = PG_GETARG_TEXT_PP(0);
    text  *arg2 = PG_GETARG_TEXT_PP(1);
    int32 arg1_size = VARSIZE_ANY_EXHDR(arg1);
    int32 arg2_size = VARSIZE_ANY_EXHDR(arg2);
    int32 new_text_size = arg1_size + arg2_size + VARHDRSZ;
    text *new_text = (text *) palloc(new_text_size);

    SET_VARSIZE(new_text, new_text_size);
    memcpy(VARDATA(new_text), VARDATA_ANY(arg1), arg1_size);
    memcpy(VARDATA(new_text) + arg1_size, VARDATA_ANY(arg2), arg2_size);
    PG_RETURN_TEXT_P(new_text);
}

Предполагая, что приведенный выше код был подготовлен в файле funcs.c и скомпилирован в общий объект, мы могли бы определить функции для QHB с помощью таких команд:

CREATE FUNCTION add_one(integer) RETURNS integer
     AS 'DIRECTORY/funcs', 'add_one'
     LANGUAGE C STRICT;

-- note overloading of SQL function name "add_one"
CREATE FUNCTION add_one(double precision) RETURNS double precision
     AS 'DIRECTORY/funcs', 'add_one_float8'
     LANGUAGE C STRICT;

CREATE FUNCTION makepoint(point, point) RETURNS point
     AS 'DIRECTORY/funcs', 'makepoint'
     LANGUAGE C STRICT;

CREATE FUNCTION copytext(text) RETURNS text
     AS 'DIRECTORY/funcs', 'copytext'
     LANGUAGE C STRICT;

CREATE FUNCTION concat_text(text, text) RETURNS text
     AS 'DIRECTORY/funcs', 'concat_text'
     LANGUAGE C STRICT;

Здесь DIRECTORY обозначает каталог файла общей библиотеки (например, каталог учебника по QHB, который содержит код для примеров, используемых в этом разделе). (Лучше было бы использовать просто 'funcs' в предложении AS после добавления DIRECTORY в путь поиска. В любом случае мы можем опустить системное расширение для общей библиотеки, обычно .so ).

Обратите внимание, что мы указали функции как «строгие», что означает, что система должна автоматически принимать нулевой результат, если любое входное значение равно нулю. Делая это, мы избегаем необходимости проверять нулевые входы в коде функции. Без этого нам пришлось бы явно проверять нулевые значения, используя PG_ARGISNULL().

На первый взгляд, соглашения о кодировании в версии 1 могут показаться просто бессмысленным мракобесием по сравнению с использованием простых соглашений о вызовах языка C/RUST. Тем не менее, они позволяют работать с NULL аргументами / возвращаемыми значениями и «поджаренными» (сжатыми или вне строки) значениями.

Макрос PG_ARGISNULL(n) позволяет функции проверять, является ли каждый вход нулевым. (Конечно, делать это необходимо только в функциях, не объявленных как «строгие»). Как и в PG_GETARG_ xxx (), входные аргументы считаются начиная с нуля. Обратите внимание, что следует воздерживаться от выполнения PG_GETARG_ xxx () пока не убедитесь что аргумент не является нулевым. Чтобы вернуть нулевой результат, выполните PG_RETURN_NULL(); это работает как в строгих, так и в нестрогих функциях.

Другие опции, предоставляемые интерфейсом версии 1, - это два варианта PG_GETARG_ xxx (). Первый из них, PG_GETARG_ xxx _COPY(), гарантирует возврат копии указанного аргумента, который является безопасным для записи. (Обычные макросы иногда возвращают указатель на значение, которое физически хранится в таблице, в которую нельзя записывать. Использование PG_GETARG_ xxx _COPY() гарантирует доступный для записи результат). Второй вариант состоит из PG_GETARG_ xxx _SLICE() макроса, который принимает три аргумента. Первый - это номер аргумента функции (как указано выше). Второе и третье - это смещение и длина возвращаемого сегмента. Смещения отсчитываются от нуля, а отрицательная длина требует возврата оставшейся части значения. Эти макросы обеспечивают более эффективный доступ к частям больших значений в том случае, если они имеют тип хранения «внешний». (Тип хранения столбца может быть указан с помощью ALTER TABLE tablename ALTER COLUMN colname SET STORAGE storagetype. storagetype - один из plain, external, extended или main).

Наконец, соглашения о вызовах функций версии 1 позволяют возвращать заданные результаты (раздел Возврат наборов) и реализовывать функции триггера (глава Триггеры)

Написание кода

Прежде чем перейти к более сложным темам, мы должны обсудить некоторые правила кодирования для функций языка C/RUST QHB. Хотя может быть возможно загрузить функции, написанные на языках, отличных от C, в QHB, это обычно сложно (когда это вообще возможно), потому что другие языки, такие как C++, FORTRAN или Pascal, часто не следуют тому же соглашению о вызовах, что и C. То есть другие языки не передают аргумент и возвращают значения между функциями одинаковым образом. По этой причине мы будем предполагать, что ваши функции языка C на самом деле написаны на C/RUST.

Основные правила написания и построения функций C/RUST следующие:

  • Используйте pg_config --includedir-server чтобы узнать, где установлены файлы заголовков сервера QHB в вашей системе (или системе, на которой будут работать ваши пользователи).

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

  • Не забудьте определить «магический блок» для вашей общей библиотеки, как описано в разделе 6.10.1.

  • При выделении памяти используйте функции QHB palloc и pfree вместо соответствующих библиотек C функции malloc и free. Память, выделенная palloc будет автоматически освобождаться в конце каждой транзакции, предотвращая утечки памяти.

  • Всегда обнуляйте байты ваших структур, используя memset (или выделяйте их сначала с помощью palloc0). Даже если вы назначите каждому полю вашей структуры, могут быть отступы выравнивания (отверстия в структуре), которые содержат значения мусора. Без этого трудно поддерживать хеш-индексы или хеш-объединения, так как для вычисления хеша необходимо выделить только значимые биты вашей структуры данных. Планировщик также иногда полагается на сравнение констант с помощью побитового равенства, поэтому вы можете получить нежелательные результаты планирования, если логически эквивалентные значения не являются побитовыми.

  • Большинство внутренних типов QHB объявлены в postgres.h, в то время как интерфейсы диспетчера функций ( PG_FUNCTION_ARGS и т. д.) Находятся в fmgr.h, поэтому вам нужно будет включить как минимум эти два файла. По причинам переносимости лучше сначала включить postgres.h, а не файлы заголовков других систем или пользователей. В том числе postgres.h также будет включать elog.h и palloc.h для вас.

  • Имена символов, определенные в объектных файлах, не должны конфликтовать друг с другом или с символами, определенными в исполняемом файле сервера QHB. Вам придется переименовать ваши функции или переменные, если вы получите сообщения об ошибках по этому поводу.

Компиляция и связывание динамически загружаемых функций

Прежде чем вы сможете использовать свои функции расширения QHB, написанные на C/RUST, они должны быть скомпилированы и связаны особым образом для создания файла, который может быть динамически загружен сервером. Чтобы быть точным, необходимо создать общую библиотеку .

Для получения информации, выходящей за рамки этого раздела, вам следует прочитать документацию по вашей операционной системе, в частности страницы руководства для компилятора C/RUST, cc и редактора ссылок, ld. Кроме того, исходный код QHB содержит несколько рабочих примеров в каталоге contrib. Однако, если вы будете полагаться на эти примеры, вы сделаете свои модули зависимыми от доступности исходного кода QHB.

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

В следующих примерах мы предполагаем, что ваш исходный код находится в файле foo.c и мы создадим общую библиотеку foo.so Промежуточный объектный файл будет называться foo.o если не указано иное. Общая библиотека может содержать более одного объектного файла, но здесь мы используем только один.

Linux

  • Флаг компилятора для создания PIC - -fPIC. Флаг компилятора для создания разделяемой библиотеки - -shared. Полный пример выглядит так:
cc -fPIC -c foo.c
cc -shared -o foo.so foo.o

Полученный файл общей библиотеки затем можно загрузить в QHB . При указании имени файла для команды CREATE FUNCTIONнеобходимо указать имя файла общей библиотеки, а не файла промежуточных объектов. Обратите внимание, что стандартное расширение разделяемой библиотеки системы (обычно .so или .sl ) может быть опущено в команде CREATE FUNCTION и обычно должно быть опущено для лучшей переносимости.

Обратитесь к разделу 6.10.1 о том, где сервер ожидает найти файлы общей библиотеки.

Составные аргументы

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

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

SELECT name, c_overpaid(emp, 1500) AS overpaid
    FROM emp
    WHERE name = 'Bill' OR name = 'Sam';

Используя соглашения о вызовах версии-1, мы можем определить c_overpaid как:

#include "postgres.h"
#include "executor/executor.h"  /* for GetAttributeByName() */

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(c_overpaid);

Datum
c_overpaid(PG_FUNCTION_ARGS)
{
    HeapTupleHeader  t = PG_GETARG_HEAPTUPLEHEADER(0);
    int32            limit = PG_GETARG_INT32(1);
    bool isnull;
    Datum salary;

    salary = GetAttributeByName(t, "salary", &isnull);
    if (isnull)
        PG_RETURN_BOOL(false);
    /* Alternatively, we might prefer to do PG_RETURN_NULL() for null salary. */

    PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
}

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

Существует также GetAttributeByNum, который выбирает целевой атрибут по номеру столбца, а не по имени.

Следующая команда объявляет функцию c_overpaid в SQL:

CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
    AS 'DIRECTORY/funcs', 'c_overpaid'
    LANGUAGE C STRICT;

Обратите внимание, что мы использовали STRICT чтобы нам не нужно было проверять, были ли входные аргументы NULL.

Возвращающиеся строки (составные типы)

Чтобы вернуть значение строки или составного типа из функции языка C/RUST, вы можете использовать специальный API, который предоставляет макросы и функции, чтобы скрыть большую часть сложности построения составных типов данных. Чтобы использовать этот API, исходный файл должен включать:

#include "funcapi.h"

Существует два способа создания составного значения данных (далее «кортеж»): вы можете построить его из массива значений Datum или из массива строк C/RUST, которые можно передать во входные функции преобразования столбца кортежа типы данных. В любом случае сначала необходимо получить или создать дескриптор TupleDesc для структуры кортежа. При работе с Datums вы передаете TupleDesc в BlessTupleDesc, а затем вызываете heap_form_tuple для каждой строки. При работе со строками C вы передаете TupleDesc в TupleDescGetAttInMetadata, а затем вызываете BuildTupleFromCStrings для каждой строки. В случае если функция возвращает набор кортежей, все шаги настройки могут быть выполнены один раз во время первого вызова функции.

Несколько вспомогательных функций доступны для настройки необходимого TupleDesc. Рекомендуемый способ сделать это в большинстве функций, возвращающих составные значения, это вызвать:

TypeFuncClass get_call_result_type(FunctionCallInfo fcinfo,
                                   Oid *resultTypeId,
                                   TupleDesc *resultTupleDesc)

передача той же самой структуры fcinfo переданной самой вызывающей функции. (Это, конечно, требует использования соглашений о вызовах версии-1). resultTypeId может быть задан как NULL или как адрес локальной переменной для получения OID типа результата функции. resultTupleDesc должен быть адресом локальной переменной TupleDesc. Убедитесь, что результатом является TYPEFUNC_COMPOSITE; если это так, resultTupleDesc был заполнен необходимым TupleDesc. (Если это не так, вы можете сообщить об ошибке в соответствии с «function returning record called in context that cannot accept type record» ).

Заметка
get_call_result_type может разрешить фактический тип результата полиморфной функции; так что это полезно в функциях, которые возвращают скалярные полиморфные результаты, а не только в функциях, которые возвращают композиты. Вывод resultTypeId в первую очередь полезен для функций, возвращающих полиморфные скаляры.

Заметка
get_call_result_type имеет одноуровневый get_expr_result_type, который можно использовать для разрешения ожидаемого выходного типа для вызова функции, представленного деревом выражений. Это может быть использовано при попытке определить тип результата вне самой функции. Существует также get_func_result_type, который можно использовать, когда доступен только OID функции. Однако эти функции не могут работать с функциями, объявленными для возврата record, и get_func_result_type не может разрешать полиморфные типы, поэтому вы должны преимущественно использовать get_call_result_type.

Старые устаревшие функции для получения TupleDesc:

TupleDesc RelationNameGetTupleDesc(const char *relname)

чтобы получить TupleDesc для типа строки именованного отношения, и:

TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)

чтобы получить TupleDesc на основе типа OID. Это может быть использовано для получения TupleDesc для базового или составного типа. Однако она не будет работать для функции, которая возвращает record, и не может разрешать полиморфные типы.

Когда у вас есть TupleDesc, вызовите:

TupleDesc BlessTupleDesc(TupleDesc tupdesc)

если вы планируете работать с Datums, или:

AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)

если вы планируете работать со строками C. Если вы пишете функцию, возвращающую набор, вы можете сохранить результаты этих функций в структуре FuncCallContext - используйте поле tuple_desc или attinmeta соответственно.

При работе с Datums используйте:

HeapTuple heap_form_tuple(TupleDesc tupdesc, Datum *values, bool *isnull)

построить HeapTuple заданных пользовательских данных в форме Datum.

При работе со строками Си используйте:

HeapTuple BuildTupleFromCStrings(AttInMetadata *attinmeta, char **values)

построить HeapTuple данных пользовательских данных в форме строки C. values это массив строк C, по одному на каждый атрибут возвращаемой строки. Каждая строка C должна иметь форму, ожидаемую функцией ввода типа данных атрибута. Чтобы вернуть null значение для одного из атрибутов, соответствующий указатель в массиве values должен быть установлен в NULL. Эту функцию нужно будет вызывать снова для каждой возвращаемой строки.

Как только вы построите кортеж для возврата из своей функции, он должен быть преобразован в элемент Datum. Использование:

HeapTupleGetDatum(HeapTuple tuple)

преобразовать HeapTuple в действительный элемент данных. Этот Datum может быть возвращен напрямую, если вы намереваетесь вернуть только одну строку, или он может использоваться как текущее возвращаемое значение в функции, возвращающей множество.

Пример появится в следующем разделе.

Возврат наборов

Существует также специальный API, который обеспечивает поддержку возврата наборов (нескольких строк) из функции языка Си. Функция, возвращающая множество, должна следовать соглашениям о вызовах версии-1. Кроме того, исходные файлы должны включать funcapi.h, как указано выше.

Функция возврата набора (SRF) вызывается один раз для каждого возвращаемого элемента. Поэтому SRF должен сохранять достаточно состояния, чтобы помнить, что он делал, и возвращать следующий элемент при каждом вызове. Структура FuncCallContext предназначена для управления этим процессом. Внутри функции fcinfo->flinfo->fn_extra используется для хранения указателя на FuncCallContext во всех вызовах.

typedef struct FuncCallContext
{
    /*
     * Number of times we've been called before
     *
     * call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
     * incremented for you every time SRF_RETURN_NEXT() is called.
     */
    uint64 call_cntr;

    /*
     * OPTIONAL maximum number of calls
     *
     * max_calls is here for convenience only and setting it is optional.
     * If not set, you must provide alternative means to know when the
     * function is done.
     */
    uint64 max_calls;

    /*
     * OPTIONAL pointer to miscellaneous user-provided context information
     *
     * user_fctx is for use as a pointer to your own data to retain
     * arbitrary context information between calls of your function.
     */
    void *user_fctx;

    /*
     * OPTIONAL pointer to struct containing attribute type input metadata
     *
     * attinmeta is for use when returning tuples (i.e., composite data types)
     * and is not used when returning base data types. It is only needed
     * if you intend to use BuildTupleFromCStrings() to create the return
     * tuple.
     */
    AttInMetadata *attinmeta;

    /*
     * memory context used for structures that must live for multiple calls
     *
     * multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
     * by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
     * context for any memory that is to be reused across multiple calls
     * of the SRF.
     */
    MemoryContext multi_call_memory_ctx;

    /*
     * OPTIONAL pointer to struct containing tuple description
     *
     * tuple_desc is for use when returning tuples (i.e., composite data types)
     * and is only needed if you are going to build the tuples with
     * heap_form_tuple() rather than with BuildTupleFromCStrings().  Note that
     * the TupleDesc pointer stored here should usually have been run through
     * BlessTupleDesc() first.
     */
    TupleDesc tuple_desc;

} FuncCallContext;

SRF использует несколько функций и макросов, которые автоматически манипулируют структурой FuncCallContext (и ожидают найти ее через fn_extra). Использование:

SRF_IS_FIRSTCALL()

определить, вызывается ли ваша функция в первый или последующий раз. При первом вызове (только) используйте:

SRF_FIRSTCALL_INIT()

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

SRF_PERCALL_SETUP()

правильно настроить использование FuncCallContext и очистку любых ранее возвращенных данных, оставшихся от предыдущего прохода.

Если ваша функция имеет данные для возврата, используйте:

SRF_RETURN_NEXT(funcctx, result)

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

SRF_RETURN_DONE(funcctx)

очистить и закончить SRF.

Текущий контекст памяти, когда вызывается SRF, является временным контекстом, который будет очищен между вызовами. Это означает, что вам не нужно вызывать pfree для всего, что вы выделили с помощью palloc; это все равно уйдет. Однако, если вы хотите распределить какие-либо структуры данных между вызовами, вам нужно поместить их в другое место. Контекст памяти, на который ссылается multi_call_memory_ctx, является подходящим местом для любых данных, которые должны сохраняться до завершения работы SRF. В большинстве случаев это означает, что вы должны переключиться на multi_call_memory_ctx при выполнении настройки первого вызова.

!!! Предупреждение

Хотя фактические аргументы функции остаются неизменными между вызовами, если вы сбрасываете значения аргументов (что обычно делается прозрачно с помощью макроса PG_GETARG_xxx) во временном контексте, то очищенные копии будут освобождаться в каждом цикле. Соответственно, если вы сохраняете ссылки на такие значения в вашем user_fctx, вы должны либо скопировать их в multi_call_memory_ctx после удаления, либо убедиться, что вы удалили значения только в этом контексте.

Полный пример псевдокода выглядит следующим образом:

Datum
my_set_returning_function(PG_FUNCTION_ARGS)
{
    FuncCallContext  *funcctx;
    Datum             result;
    further declarations as needed

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx = SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        /* One-time setup code appears here: */
        user code
        if returning composite
            build TupleDesc, and perhaps AttInMetadata
        endif returning composite
        user code
        MemoryContextSwitchTo(oldcontext);
    }

    /* Each-time setup code appears here: */
    user code
    funcctx = SRF_PERCALL_SETUP();
    user code

    /* this is just one way we might test whether we are done: */
    if (funcctx->call_cntr < funcctx->max_calls)
    {
        /* Here we want to return another item: */
        user code
        obtain result Datum
        SRF_RETURN_NEXT(funcctx, result);
    }
    else
    {
        /* Here we are done returning items and just need to clean up: */
        user code
        SRF_RETURN_DONE(funcctx);
    }
}

Полный пример простого SRF, возвращающего составной тип, выглядит следующим образом:

PG_FUNCTION_INFO_V1(retcomposite);

Datum
retcomposite(PG_FUNCTION_ARGS)
{
    FuncCallContext     *funcctx;
    int                  call_cntr;
    int                  max_calls;
    TupleDesc            tupdesc;
    AttInMetadata       *attinmeta;

    /* stuff done only on the first call of the function */
    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext   oldcontext;

        /* create a function context for cross-call persistence */
        funcctx = SRF_FIRSTCALL_INIT();

        /* switch to memory context appropriate for multiple function calls */
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        /* total number of tuples to be returned */
        funcctx->max_calls = PG_GETARG_UINT32(0);

        /* Build a tuple descriptor for our result type */
        if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
            ereport(ERROR,
                    (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
                     errmsg("function returning record called in context "
                            "that cannot accept type record")));

        /*
         * generate attribute metadata needed later to produce tuples from raw
         * C strings
         */
        attinmeta = TupleDescGetAttInMetadata(tupdesc);
        funcctx->attinmeta = attinmeta;

        MemoryContextSwitchTo(oldcontext);
    }

    /* stuff done on every call of the function */
    funcctx = SRF_PERCALL_SETUP();

    call_cntr = funcctx->call_cntr;
    max_calls = funcctx->max_calls;
    attinmeta = funcctx->attinmeta;

    if (call_cntr < max_calls)    /* do when there is more left to send */
    {
        char       **values;
        HeapTuple    tuple;
        Datum        result;

        /*
         * Prepare a values array for building the returned tuple.
         * This should be an array of C strings which will
         * be processed later by the type input functions.
         */
        values = (char **) palloc(3 * sizeof(char *));
        values[0] = (char *) palloc(16 * sizeof(char));
        values[1] = (char *) palloc(16 * sizeof(char));
        values[2] = (char *) palloc(16 * sizeof(char));

        snprintf(values[0], 16, "%d", 1 * PG_GETARG_INT32(1));
        snprintf(values[1], 16, "%d", 2 * PG_GETARG_INT32(1));
        snprintf(values[2], 16, "%d", 3 * PG_GETARG_INT32(1));

        /* build a tuple */
        tuple = BuildTupleFromCStrings(attinmeta, values);

        /* make the tuple into a datum */
        result = HeapTupleGetDatum(tuple);

        /* clean up (this is not really necessary) */
        pfree(values[0]);
        pfree(values[1]);
        pfree(values[2]);
        pfree(values);

        SRF_RETURN_NEXT(funcctx, result);
    }
    else    /* do when there is no more left */
    {
        SRF_RETURN_DONE(funcctx);
    }
}

Один из способов объявить эту функцию в SQL:

CREATE TYPE __retcomposite AS (f1 integer, f2 integer, f3 integer);

CREATE OR REPLACE FUNCTION retcomposite(integer, integer)
    RETURNS SETOF __retcomposite
    AS 'filename', 'retcomposite'
    LANGUAGE C IMMUTABLE STRICT;

Другой способ - использовать параметры OUT:

CREATE OR REPLACE FUNCTION retcomposite(IN integer, IN integer,
    OUT f1 integer, OUT f2 integer, OUT f3 integer)
    RETURNS SETOF record
    AS 'filename', 'retcomposite'
    LANGUAGE C IMMUTABLE STRICT;

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

Модуль contrib/tablefunc каталога в исходном дистрибутиве содержит больше примеров возвращающих множество функций.

Полиморфные аргументы и возвращаемые типы

Можно объявить функции языка Си, чтобы они принимали и возвращали полиморфные типы anyelement, anyarray, anynonarray, anyenum и anyrange. См. Раздел Полиморфные типы для более подробного объяснения полиморфных функций. Когда аргументы функции или возвращаемые типы определены как полиморфные типы, автор функции не может заранее знать, с каким типом данных он будет вызываться или должен возвращаться. В fmgr.h предусмотрены две подпрограммы, позволяющие функции C версии 1 обнаружить фактические типы данных своих аргументов и тип, который ожидается вернуть. Процедуры называются get_fn_expr_rettype(FmgrInfo *flinfo) и get_fn_expr_argtype(FmgrInfo *flinfo, int argnum). Они возвращают OID типа результата или аргумента или InvalidOid, если информация недоступна. Структура flinfo обычно доступна как fcinfo->flinfo. Параметр argnum основан на zero. get_call_result_type также может использоваться как альтернатива get_fn_expr_rettype. Существует также get_fn_expr_variadic, который можно использовать для определения, были ли переменные аргументы объединены в массив. Это в первую очередь полезно для VARIADIC "any" функций, поскольку такое объединение всегда будет происходить для функций с переменными числами, принимающих обычные типы массивов.

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

PG_FUNCTION_INFO_V1(make_array);
Datum
make_array(PG_FUNCTION_ARGS)
{
    ArrayType  *result;
    Oid         element_type = get_fn_expr_argtype(fcinfo->flinfo, 0);
    Datum       element;
    bool        isnull;
    int16       typlen;
    bool        typbyval;
    char        typalign;
    int         ndims;
    int         dims[MAXDIM];
    int         lbs[MAXDIM];

    if (!OidIsValid(element_type))
        elog(ERROR, "could not determine data type of input");

    /* get the provided element, being careful in case it's NULL */
    isnull = PG_ARGISNULL(0);
    if (isnull)
        element = (Datum) 0;
    else
        element = PG_GETARG_DATUM(0);

    /* we have one dimension */
    ndims = 1;
    /* and one element */
    dims[0] = 1;
    /* and lower bound is 1 */
    lbs[0] = 1;

    /* get required info about the element type */
    get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);

    /* now build the array */
    result = construct_md_array(&element, &isnull, ndims, dims, lbs,
                                element_type, typlen, typbyval, typalign);

    PG_RETURN_ARRAYTYPE_P(result);
}

Следующая команда объявляет функцию make_array в SQL:

CREATE FUNCTION make_array(anyelement) RETURNS anyarray
    AS 'DIRECTORY/funcs', 'make_array'
    LANGUAGE C IMMUTABLE;

Существует вариант полиморфизма, который доступен только для функций языка Си: они могут быть объявлены для получения параметров типа "any". (Обратите внимание, что это имя типа должно быть заключено в двойные кавычки, поскольку оно также является зарезервированным словом SQL). Это работает как любой элемент, за исключением того, что оно не ограничивает различные "any" аргументы одним и тем же типом и не помогает определить результат функции тип. Функция языка Си также может объявить свой последний параметр VARIADIC "any". Это будет соответствовать одному или нескольким фактическим аргументам любого типа (не обязательно того же типа). Эти аргументы не будут собраны в массив, как это происходит с обычными переменными функциями; они просто будут переданы функции отдельно. Макрос PG_NARGS () и методы, описанные выше, должны использоваться для определения количества фактических аргументов и их типов при использовании этой функции. Кроме того, пользователи такой функции могут захотеть использовать ключевое слово VARIADIC в своем вызове функции, ожидая, что функция будет рассматривать элементы массива как отдельные аргументы. Сама функция должна реализовать это поведение, если необходимо, после использования get_fn_expr_variadic, чтобы обнаружить, что фактический аргумент был помечен как VARIADIC.

Общая память и LWLocks

Надстройки могут резервировать LWLocks и распределение общей памяти при запуске сервера. Общая библиотека надстройки должна быть предварительно загружена, указав ее в shared_preload_libraries. Общая память резервируется путем вызова:

void RequestAddinShmemSpace(int size)

из вашей функции _PG_init.

LWLocks резервируются путем вызова:

void RequestNamedLWLockTranche(const char *tranche_name, int num_lwlocks)

из _PG_init. Это обеспечит доступность массива num_lwlocks LWLocks под именем tranche_name. Используйте GetNamedLWLockTranche, чтобы получить указатель на этот массив.

Чтобы избежать возможных условий гонки, каждый бэкэнд должен использовать LWLock AddinShmemInitLock при подключении и инициализации распределения общей памяти, как показано здесь:

static mystruct *ptr = NULL;

if (!ptr)
{
        bool    found;

        LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
        ptr = ShmemInitStruct("my struct name", size, &found);
        if (!found)
        {
                initialize contents of shmem area;
                acquire any requested LWLocks using:
                ptr->locks = GetNamedLWLockTranche("my tranche name");
        }
        LWLockRelease(AddinShmemInitLock);
}

Использование C ++ для расширяемости

Хотя серверная часть QHB написана на C/Rust, можно написать расширения на C++, если следовать этим рекомендациям:

  • Все функции, к которым обращается бэкэнд, должны представлять интерфейс C для бэкэнда; эти функции C могут затем вызывать функции C++. Например, внешняя связь C необходима для функций, к которым обращаются к серверу. Это также необходимо для любых функций, которые передаются в виде указателей между серверной частью и кодом C++.

  • Освободите память, используя соответствующий метод освобождения. Например, большая часть внутренней памяти выделяется с помощью palloc(), поэтому используйте pfree() для ее освобождения. Использование C++ удалить в таких случаях не удастся.

  • Не допускайте распространения исключений в коде C (используйте блок catch-all на верхнем уровне всех внешних функций C). Это необходимо, даже если код C++ явно не выбрасывает какие-либо исключения, потому что такие события, как нехватка памяти, могут по-прежнему генерировать исключения. Любые исключения должны быть перехвачены и соответствующие ошибки переданы обратно в интерфейс C. Если возможно, скомпилируйте C++ с -fno-exception, чтобы полностью исключить исключения; в таких случаях вы должны проверять ошибки в вашем C++ коде, например, проверять NULL, возвращаемый new().

  • При вызове внутренних функций из кода C++ убедитесь, что стек вызовов C++ содержит только простые старые структуры данных (POD). Это необходимо, потому что ошибки бэкэнда генерируют удаленный longjmp (), который неправильно разворачивает стек вызовов C++ с объектами не POD.

Таким образом, лучше всего поместить код C++ за стеной внешних функций C/RUST, которые взаимодействуют с бэкэндом и избегают утечек исключительных ситуаций, памяти и стека вызовов.

Информация по оптимизации функций

По умолчанию функция — это «черный ящик», система очень мало знает о её поведении. Из этого следует, что запросы, использующие функцию, могут выполняться менее эффективно, чем могли бы. Можно предоставить дополнительную информацию, которая поможет планировщику оптимизировать вызовы функций.

Некоторые основные факты могут быть предоставлены декларативными аннотациями в команде CREATE FUNCTION. Наиболее важным из них является категория волатильности функции (IMMUTABLE, STABLE или VOLATILE); всегда нужно быть осторожным, чтобы правильно указать это при определении функции. Свойство безопасности параллельного исполнения (PARALLEL UNSAFE, PARALLEL RESTRICTED или PARALLEL SAFE) также должно быть указано, если вы надеетесь на использование этой функции в параллельных запросах. Также может быть полезно указать оценочную стоимость выполнения функции и/или количество строк, которые она должна вернуть. Однако декларативный способ указания этих двух фактов позволяет указывать только константное значение, а это будет неадекватно.

Также можно прикрепить вспомогательную функцию для планировщика к любой функции, которую можно вызвать из SQL, для вспомогательной функции та функция, к которой она прикреплена, называется её целевой функцией. Вспомогателная функция предоставляет информацию планировщику о своей целевой функции. Вспомогаетльная функция — нативная (в то время как целевая функция — на любом языке), это делает её написание сложным, и они применяются редко.

Вспомогательная функция для планировщика должна иметь следующую SQL-сигнатуру:

supportfn(internal) returns internal

Чтобы подсоединить её к целевой функции, нужно при создании последней использовать указание SUPPORT.

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

Если целевая функция возвращают boolean, то часто полезно оценить процент строк, для которых она вернёт истину. Вы можете реализовать SupportRequestSelectivity и вернуть оптимизатору примерный процент.

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

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

Самый сложный тип запроса SupportRequestSimplify позволяет переписать непосредственно дерево разбора, заменив вызов целевой функции на что-то ещё. Например, int4mul(n, 1) можно упростить до просто n, этого не знает планировщик, но знает вспомогательная функция, тесно связанная с int4mul. SupportRequestSimplify будет вызвано для каждого вхождения целевой функции в запрос, однако не гарантируется, что не смотря на советы вспомогательной функции, планировщик всё-таки не вызовет оригинальную целевую функцию как есть.

Когда целевая функций, возвращающая boolean, используются для фильтрации (например, в секции WHERE), из неё можно выделить другой, "грубый" фильтр, который позволит индексный поиск. Это делает вспомогательная функция по запросу SupportRequestIndexCondition; фильтр, который она вернёт, может быть в точности эквивалентным вызову целевой функции или быть более слабым, в последне случае для каждой строки будет перепроверяться оригинальный фильтр вызовом целевой функции. Планировщик может решить, что индексный поиск всё равно невозможен или невыгоден, — в этом случае фильтр, созданный вспомогательной функцией, не будет использоваться.

Пользовательские агрегаты

Агрегатные функции в QHB определяются в терминах состояний и функций перехода состояний. То есть агрегат работает с некоторым состоянием, которое обновляется при обработке каждой последующей входной строки. Чтобы создать новую агрегатную функцию, надо выбрать тип данных для состояния, его начальное значение и функцию перехода (свёртки). Функция перехода состояния принимает предыдущее значение состояния и агрегируемое значение(я) текущей строки и возвращает новое значение состояния. Если тип состояния агрегата отличен от желаемого типа результата агрегации, то нужно указать также функцию финализации, которая в конце преобразует одно в другое. В принципе, функции перехода и финализации — это просто обычные функции, которые могут использоваться и вне контекста агрегата. Но на практике, даже если можно использовать в качестве функции перехода существующую функцию, вы, возможно, заходите написать её специальную версию, оптимизированную именно для агрегации.

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

В качестве примера определим агрегат суммы для комплексных чисел:

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)'
);

Здесь мы задали функцию перехода состояния, тип состояния и начальное состояние. Функцию финализации не задали, и поэтому результатом агрегации будет считаться конечное значение состояния. Как использовать этот агрегат:

SELECT sum(a) FROM test_complex;

   sum
-----------
 (34,53.9)

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

Приведенное выше определение sum вернет ноль (начальное состояние), если нет входных строк, или все они имеют значение NULL. А по стандарту SQL в таком случае положено вернуть NULL. Чтобы добиться этого, можно задать начальное состояние initcond = NULL или, что тоже самое, просто опустить initcond. Однако в этом случае функция перехода sfunc должна уметь обрабатывать текущее состояние NULL. Или можно объявить функцию перехода строгой (STRICT), в этом случае, если текущее состояние NULL, когда встретится строка со значнием NOT NULL, QHB поместит это первое NOT NULL значение в состояние агрегата (разумеется, типы входного значения и состояния должны быть одинаковыми). Для многих агрегатов, например, sum, min, max такое поведение — то, что нужно.

Еще одна особенность поведения «строгой» функции перехода заключается в том, что каждый раз, когда входное значение NULL, функция не вызывается, а состояние сохраняется прежним. Таким образом, NULL значения игнорируются. Если вам нужно другое поведение для входных NULL, не объявляйте вашу функцию перехода строгой, вместо этого закодируйте в ней обработку NULL.

avg (среднее арифметическое) — пример более сложного агрегата. Рабочее состояние должно состоять из суммы значений и их количества. Окончательный результат получается делением этих величин. Встроенная реализация avg хранит промежуточное состояние в виде массива, а не в виде кортежа. Например, определение avg(float8) выглядит так:

CREATE AGGREGATE avg (float8)
(
    sfunc = float8_accum,
    stype = float8[],
    finalfunc = float8_avg,
    initcond = '{0,0,0}'
);

Замечание
Для float8_accum требуется массив из трех элементов, а не из только двух, потому что она накапливает количество, сумму и сумму квадратов. Это сделано для того, чтобы ту же самую функцию float8_accum можно было использовать и для других агрегатов помимо avg.

Вызовы агрегатных функций в SQL позволяют использовать опции DISTINCT и ORDER BY, которые определяют, какие строки передаются в агрегирующую функцию и в каком порядке. Это происходит за кулисами, и агрегатная функция не может настраивать это поведение.

Для получения дополнительной информации см. описание команды CREATE AGGREGATE.

Режим движущегося агрегата

Агрегатные функции могут дополнительно поддерживать режим движущегося(скользящего) агрегата, который позволяет гораздо быстрее считать агрегат по окну с движущимся началом. (См. разделы Руководство по оконным функциям и Вызовы оконных функций для получения информации об использовании агрегатных функций в качестве оконных функций). Основная идея заключается в том, что в дополнение к обычной «прямой» функции перехода агрегат предоставляет обратную функцию перехода, которая позволяет удалять строки из текущего состояния агрегата, когда они выпадают из окна. Например, агрегат sum, который использует сложение в качестве функции прямого перехода, будет использовать вычитание в качестве функции обратного перехода. Без функции обратного перехода придётся пересчитывать агрегат с нуля при каждом перемещении рамки окна, в результате чего время выполнения будет пропорционально числу входных строк, умноженному на среднюю длину окна. При использовании функции обратного перехода время выполнения пропорционально просто количеству входных строк.

В функцию обратного перехода передаётся текущее состояние и значение выпадающей строки. Это обязательно первая из строк, добавленных в агрегат (и ещё не выпавших). Формально функция должна восстановить состояние агрегата, как если бы выпадающая строка никогда и не была добавлена в агрегат, а агрегацию начали со второй строки. Чтобы это реализовать, может потребоваться хранить в текущем состоянии агрегата больше информации, чем для агрегирования "только вперёд". По этой причине режим движущегося агрегата имеет независимый набор из типа данных, функций перехода прямой и обратной, и функции финализации, если нужна. А в простом случае этот набор будет совпадать с набором для "обыкновенного" режима агрегации.

В качестве примера опять определение агрегата sum для комплексных чисел, теперь поддерживается скользящий режим:

CREATE AGGREGATE sum (complex)
(
    sfunc = complex_add,
    stype = complex,
    initcond = '(0,0)',
    msfunc = complex_add,
    minvfunc = complex_sub,
    mstype = complex,
    minitcond = '(0,0)'
);

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

Функция прямого перехода для режима движущегося агрегата не может возвращать значение NULL в качестве нового значения состояния. Функция обратного перехода может вернуть NULL, но это будет воспринято как то, что она не может "убрать" данное значение — в этом случае агрегат пересчитают с нуля, начиная от нового начала окна. Это соглашение позволяет использовать функции движущегося агрегата, если иногда возникают ситуации, когда нельзя удалить значение из текущего состояния; в этих редких ситуациях функция обратного перехода "сдаётся", и агрегирование будет работать медленнее, но в большинстве ситуаций — быстро. Пример — агрегат, работающий с числами с плавающей запятой; функция обратного перехода будет вынуждена "сдаться", когда её попросят удалить значение NaN из текущего состояния агрегата.

При написании вспомогательных функций движущегося агрегата важно убедиться, что функция обратного перехода может точно восстановить правильное состояние. В противном случае могут быть заметные для пользователя различия в результатах в зависимости от того, используется ли режим скользящего агрегата. Примером агрегата, для которого добавление функции обратного перехода на первый взгляд кажется простым, а на самом деле имеет много подводных камней, является sum по float4 или float8. Наивное объявление sum(float8) может быть таким:

CREATE AGGREGATE unsafe_sum (float8)
(
    stype = float8,
    sfunc = float8pl,
    mstype = float8,
    msfunc = float8pl,
    minvfunc = float8mi
);

Этот агрегат, однако, может дать неожиданные результаты при скользящем агрегировании:

SELECT
  unsafe_sum(x) OVER (ORDER BY n ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
FROM (VALUES (1, 1.0e20::float8),
             (2, 1.0::float8)) AS v (n,x);

Вторая строчка результата будет 0, а не 1. Причиной является ограниченная точность значений с плавающей запятой: добавление 1 к 1e20 даёт снова 1e20, и потом вычитание 1e20 из этого дает 0, а не 1. Обратите внимание, что это проблемы арифметики с плавающей запятой в целом, а не QHB.

Полиморфные агрегаты

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

CREATE AGGREGATE array_accum (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

Здесь типом состояния для любого конкретного вызова агрегата является массив, элементы которого имеют тип, равный типу фактических входных данных. Поведение агрегата заключается в добавлении всех входных данных в массив. (Примечание: встроенный агрегат array_agg предоставляет аналогичную функциональность с лучшей производительностью, чем этот пример).

Протестируем этот агрегат на двух разных типах данных:

SELECT attrelid::regclass, array_accum(attname)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |              array_accum              
---------------+---------------------------------------
 pg_tablespace | {spcname,spcowner,spcacl,spcoptions}
(1 row)

SELECT attrelid::regclass, array_accum(atttypid::regtype)
    FROM pg_attribute
    WHERE attnum > 0 AND attrelid = 'pg_tablespace'::regclass
    GROUP BY attrelid;

   attrelid    |        array_accum        
---------------+---------------------------
 pg_tablespace | {name,oid,aclitem[],text[]}
(1 row)

Обычно у агрегатной функции с полиморфным типом результата тоже будет полиморфным, как в приведенном выше примере. Это необходимо, потому что иначе функция финализации не может быть разумно объявлена: у нее должен полиморфный тип результата (если, конечно, агрегирование разных типов не приводит к одинаковому типу результата), но нет полиморфного типа аргумента. Такую функцию CREATE FUNCTION не даст создать. Но иногда неудобно использовать тип полиморфного состояния. Наиболее распространенный случай, когда вспомогательные функции должны быть написаны на нативном языке, и тип состояния должен быть объявлен как internal поскольку для него нет эквивалента на уровне SQL. Для решения этой проблемы разшено добавить к функции финализации фиктивный аргумент полиморфного типа. В конце агрегации в функцию финализации будет передано состояние агрегата и NULL для всех аргументов кроме первого. Фактический тип фиктивного аргумента считается равным типу агрегируемого столбца, и из этого выводится тип результата агрегата. Вот почти настоящее определение встроенного агрегата array_agg:

CREATE FUNCTION array_agg_transfn(internal, anynonarray)
  RETURNS internal ...;
CREATE FUNCTION array_agg_finalfn(internal, anynonarray)
  RETURNS anyarray ...;

CREATE AGGREGATE array_agg (anynonarray)
(
    sfunc = array_agg_transfn,
    stype = internal,
    finalfunc = array_agg_finalfn,
    finalfunc_extra
);

Здесь опция finalfunc_extra указывает, что функция финализации получает в дополнение к значению состояния фиктивные аргументы. Дополнительный аргумент функции array_agg_finalfn делает её валидной и позволяет вывести итоговый тип агрегата (не из типа состояния, а непосредственно из входного типа).

Агрегатная функция может принимать различное количество аргументов, для этого надо объявить её последний аргумент как VARIADIC-массив, так же, как для обычных функций (см. раздел Функции SQL с переменным числом аргументов). Второй агрумент функции перехода должен быть таким же массивом (обычно тоже VARIADIC, но это не обязательно).

Замечание
VARIADIC-агрегаты подвержены ошибкам при использовании одновременно с опцией ORDER BY (см. раздел Агрегатные выражения), т.к. не жалуются на неправильное количество аргументов. Помните, что все справа от ORDER BY — ключи сортировки, а не значения для агрегирования. Например,
SELECT myaggregate(a ORDER BY a, b, c) FROM ...
— это агрегация 1 колонке и 3 ключа сортировки. А пользователь, возможно, имел в виду
SELECT myaggregate(a, b, c ORDER BY a) FROM ...
Если myaggregate является VARIADIC, то оба этих вызова совершенно корректны.

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

Сортирующие агрегатные функции

Агрегаты, которые мы описывали до сих пор, являются «нормальными» агрегатами. QHB также поддерживает т.н. сортирующие агрегаты. Результат нормального агрегата может зависеть от порядка строк (например, string_agg) или не зависеть (наприме, min); в любом случае при его вызове в агрегатном выражении можно указать ORDER BY по любым столбцам, не обязательно тем, что передаются в агрегатную функцию, и сортировку осуществляет исполнитель запросов перед подачей в агрегатную функцию. Сортирующие агрегаты осуществляют сортировку сами, именно по тем столбцам, которые они обрабатывают, поэтому набор данных для передачи в них "совмещён" с ORDER BY (WITHIN GROUP(ORDER BY col)), а в агрегатную функцию передаются дополнительные "прямые" параметры. Предполагается, что сортирующий агрегат будет сортировать и накапливать данные внутри себя, и так делают все встроенные сортирующие агрегаты, но это не обязательно. Возможно, вам нужен сортирующий агрегат, если:

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

Типичные примеры — подсчёт ранга или процентиля. Например, встроенное определение percentile_disc эквивалентно следующему:

CREATE FUNCTION ordered_set_transition(internal, anyelement)
  RETURNS internal ...;
CREATE FUNCTION percentile_disc_final(internal, float8, anyelement)
  RETURNS anyelement ...;

CREATE AGGREGATE percentile_disc (float8 ORDER BY anyelement)
(
    sfunc = ordered_set_transition,
    stype = internal,
    finalfunc = percentile_disc_final,
    finalfunc_extra
);

Этот агрегат принимает прямой аргумент float8 (процентная отсечка) и входные данные для агрегации, которые могут иметь любой сортируемый тип данных. Например, получение медианного семейного дохода:

SELECT percentile_disc(0.5) WITHIN GROUP (ORDER BY income) FROM households;
 percentile_disc
-----------------
           50489

Здесь 0.5 - прямой аргумент, т.к. процентная отсечка — это свойство всего агрегата, а не отдельных значений.

При реализации сортирующего агрегата вам придётся самостоятельно выполнять сортировку (если она вам нужна). Обычно это делают, храня ссылку на объект tuplesort внутри состояния агрегата, помещая туда все входные значения, и завершая сортировки и считывая данных в функции финализации. Такой дизайн позволяет в функции финализации добавлять «гипотетические» строки в данные в порядке сортировки. Хотя обычные агрегаты часто могут быть реализованы с помощью вспомогательных функций, написанных на PL/pgSQL или другом процедурном языке, сортирующие агрегаты должны записываться на нативном языке, хотя бы потому, что их состояние — сложный объект, который на SQL нельзя определить иначе как internal. Далее, поскольку функция финализации выполняет сортировку, после неё невозможно (или по крайней мере неэффективно) продолжить добавление входных строк. Поэтому функция финализации должна быть зарегистрирована в CREATE AGGREGATE не как READ_ONLY, а как READ_WRITE (или как SHAREABLE, если для дополнительных вызовов функции финализации возможно использование уже отсортированного состояния).

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

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

Частичная агрегация

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

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

В качестве простых примеров агрегаты MAX и MIN могут поддерживать частичного агрегирования, указав в качестве функции объединения максимум/минимум из двух, то же саамое, что и в функции перехода состояния.

Функция объединения используется также, как функция перехода, только входные аргументы имеют тип состояния, а не исходных данных; в частности такие же правила обработки NULL'ов в случае строгой/не сторгой функции. Кроме того, если у агрегата указана initcond, отличная от NULL, имейте в виду, что initcond будет стартовым значением для каждого частичного запуска агрегации и потом для функции объединения тоже.

Если тип состояния агрегата объявлен как internal, то функция объединения несет ответственность за то, чтобы ее результат был аллокирован в правильном контексте памяти; в частности, это означает, что, когда один из аргументов NULL, нельзя просто вернуть второй аргумент, поскольку это значение в неправильном контексте и имеет слишком короткий срок жизни.

Также, если тип состояния internal, нужно реализовать функции сериализации и десериализации, чтобы состояние можно было передать в другой процесс. Функция сериализации должна принимать один аргумент типа internal и возвращать результат типа bytea, который представляет собой упакованное значение состояния. И наоборот, функция десериализации выполняет это преобразование в обратном направлении. Она должна принимать два аргумента типов bytea и internal и возвращать результат типа internal. (Второй аргумент не используется и всегда равен нулю, но он необходим по соображениям безопасности типов). Результат функции десериализации должен быть просто размещен в текущем контексте памяти, поскольку в отличие от результата функции объединения, он короткоживущий.

Стоит также отметить, что для того, чтобы агрегат выполнялся параллельно, сам агрегат должен быть помечен как PARALLEL SAFE. А для его вспомогательных функциях такая пометка не обязательна.

Написание вспомогательных функций агрегатов

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

if (AggCheckCallContext(fcinfo, NULL))

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

(В то время как агрегатным функциям перехода всегда разрешено изменять значение перехода на месте, агрегатным конечным функциям обычно не рекомендуется делать это; если они это делают, поведение должно быть объявлено при создании агрегата. См. CREATE AGGREGATE для более подробной информации).

Второй аргумент AggCheckCallContext может использоваться для получения контекста памяти, в котором хранятся значения агрегатного состояния. Это полезно для функций перехода, которые хотят использовать «расширенные» объекты (см. Раздел Использование TOAST) в качестве значений состояния. При первом вызове функция перехода должна вернуть расширенный объект, чей контекст памяти является дочерним по отношению к контексту совокупного состояния, и затем продолжать возвращать тот же расширенный объект при последующих вызовах. Смотрите array_append() для примера. (array_append() не является функцией перехода какого-либо встроенного агрегата, но написана так, чтобы вести себя эффективно при использовании в качестве функции перехода пользовательского агрегата).

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

Пользовательские типы

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

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

Предположим, мы хотим определить тип complex, который представляет комплексные числа. Естественным способом представления комплексного числа в памяти будет следующая структура C:

typedef struct Complex {
    double      x;
    double      y;
} Complex;

Нам нужно будет сделать этот тип передаваемым по ссылке, поскольку он слишком велик, чтобы поместиться в одно значение Datum.

В качестве внешнего строкового представления типа мы выберем строку вида (x,y).

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

PG_FUNCTION_INFO_V1(complex_in);

Datum
complex_in(PG_FUNCTION_ARGS)
{
    char       *str = PG_GETARG_CSTRING(0);
    double      x,
                y;
    Complex    *result;

    if (sscanf(str, " ( %lf, %lf )", &x, &y) != 2)
        ereport(ERROR,
                (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
                 errmsg("invalid input syntax for type %s: \"%s\"",
                        "complex", str)));

    result = (Complex *) palloc(sizeof(Complex));
    result->x = x;
    result->y = y;
    PG_RETURN_POINTER(result);
}

Функция вывода может быть просто

PG_FUNCTION_INFO_V1(complex_out);

Datum
complex_out(PG_FUNCTION_ARGS)
{
    Complex    *complex = (Complex *) PG_GETARG_POINTER(0);
    char       *result;

    result = psprintf("(%g,%g)", complex->x, complex->y);
    PG_RETURN_CSTRING(result);
}

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

Опционально, пользовательский тип может предоставлять процедуры ввода и вывода в бинарный формат. Двоичный ввод-вывод обычно быстрее, но менее переносим, чем текстовый ввод-вывод. Как и в случае с текстовым вводом/выводом, вы должны точно определить, что такое внешнее двоичное представление. Большинство встроенных типов данных пытаются обеспечить машинно-независимое двоичное представление. Для complex мы воспользуемся преобразователям в бинарное представление ввода/вывода типа float8:

PG_FUNCTION_INFO_V1(complex_recv);

Datum
complex_recv(PG_FUNCTION_ARGS)
{
    StringInfo  buf = (StringInfo) PG_GETARG_POINTER(0);
    Complex    *result;

    result = (Complex *) palloc(sizeof(Complex));
    result->x = pq_getmsgfloat8(buf);
    result->y = pq_getmsgfloat8(buf);
    PG_RETURN_POINTER(result);
}

PG_FUNCTION_INFO_V1(complex_send);

Datum
complex_send(PG_FUNCTION_ARGS)
{
    Complex    *complex = (Complex *) PG_GETARG_POINTER(0);
    StringInfoData buf;

    pq_begintypsend(&buf);
    pq_sendfloat8(&buf, complex->x);
    pq_sendfloat8(&buf, complex->y);
    PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
}

После того, как мы написали функции ввода-вывода и скомпилировали их в разделяемую библиотеку, мы можем определить тип complex в SQL. Сначала мы объявим "оболочку" типа:

CREATE TYPE complex;

Она служит заполнителем, который позволяет нам ссылаться на тип при определении его функций ввода/вывода. Теперь мы можем определить функции ввода/вывода:

CREATE FUNCTION complex_in(cstring)
    RETURNS complex
    AS 'filename'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_out(complex)
    RETURNS cstring
    AS 'filename'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_recv(internal)
   RETURNS complex
   AS 'filename'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_send(complex)
   RETURNS bytea
   AS 'filename'
   LANGUAGE C IMMUTABLE STRICT;

Наконец, мы можем предоставить полное определение типа данных:

CREATE TYPE complex (
   internallength = 16,
   input = complex_in,
   output = complex_out,
   receive = complex_recv,
   send = complex_send,
   alignment = double
);

Когда вы определяете новый базовый тип, QHB автоматически обеспечивает поддержку массивов этого типа. Тип массива обычно имеет то же имя, что и базовый тип, с добавлением символа подчеркивания (_) в начале.

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

Если внутреннее представление типа данных имеет переменную длину, то его первые 4 байта должы хранить длину (на C это выглядит как поле char vl_len_[4]); к этому полю вы не должны никогда обращаться напрямую, но использовать специальные макросы (VARSIZE() и SET_VARSIZE()), эти макросы существуют, потому что поле длины может быть закодировано в зависимости от платформы. В SET_VARSIZE надо передавать общий размер элемента в байтах, включая 4 байта vl_len_.

Замечание
В более старом коде можно встретить объявления vl_len_ как int32 вместо char[4]. Это нормально, особенно если в структуре есть хотя бы одно другое поле с выравнивание 4+ байт. Если нет, то QHB может хранить структуру невыровено, для этого и char[4]

Для получения дополнительной информации см. Описание команды CREATE TYPE.

Использование TOAST

Если значения вашего типа данных сильно различаются по размеру (во внутреннем представлении), обычно желательно сделать тип данных сохраняющимся в TOAST (см. раздел TOAST). Стоит это сделать даже в том случае, когда значения всегда слишком малы для сжатия или внешнего хранения, поскольку TOAST может сэкономить пространство и для небольших данных, уменьшая издержки на заголовок.

Чтобы поддерживать сохранение в TOAST, функции C, работающие с типом данных, должны распаковать любые TOAST-значения, которые им передают с помощью PG_DETOAST_DATUM. (Эта деталь обычно скрывается путем определения макроса GETARG_DATATYPE_P для конкретного типа). Затем при запуске команды CREATE TYPE укажите внутреннюю длину как variable и выберите какой-либо подходящий вариант хранения, отличный от plain.

Если выравнивание данных неважно (для конкретной функции, либо потому, что тип данных имеет произвольное выравнивание (до 1 байта)), то можно избежать некоторых издержек PG_DETOAST_DATUM и вызывать вместо неё PG_DETOAST_DATUM_PACKED (обычно скрывается путем определения макроса GETARG_DATATYPE_PP ) и использовать макросы VARSIZE_ANY_EXHDR и VARDATA_ANY для доступа к потенциально упакованным данным. Опять же, данные, возвращаемые этими макросами, не выравниваются, даже если определение типа данных требует выравнивания. Если выравнивание важно, вы должны пройти через обычный интерфейс PG_DETOAST_DATUM.

Другая функция, включаемая поддержкой TOAST, - это возможность иметь расширенное представление данных в памяти, с которым удобнее работать, чем с форматом, хранящимся на диске. Обычный или «плоский» формат хранения varlena - это, в конечном счете, просто blob of bytes; например, он не может содержать указатели, так как он может быть скопирован в другие места в памяти. Для сложных типов данных плоский формат может быть довольно дорогим для работы, поэтому QHB предоставляет способ «развернуть» плоский формат в представление, более подходящее для вычислений, а затем передать этот формат в памяти между функциями тип данных.

Чтобы использовать расширенное хранилище, тип данных должен определять расширенный формат, который следует правилам, приведенным в src/include/utils/expandeddatum.h, и предоставлять функции для «раскрытия» значения плоской переменной varlena в расширенный формат и «выравнивания» расширенного формата для возвращения к обычному представлению varlena. Затем убедитесь, что все функции C для типа данных могут принимать любое представление, возможно, путем преобразования одной в другую сразу после получения. Это не требует одновременного исправления всех существующих функций для типа данных, поскольку стандартный макрос PG_DETOAST_DATUM определен для преобразования расширенных входных данных в обычный плоский формат. Следовательно, существующие функции, работающие с форматом плоской varlena, будут продолжать работать, хотя и неэффективно, с расширенными входными данными; их не нужно преобразовывать до тех пор, пока не будет важна лучшая производительность.

Функции на C/RUST, которые знают, как работать с расширенным представлением, обычно делятся на две категории: те, которые могут обрабатывать только расширенный формат, и те, которые могут обрабатывать либо расширенные, либо плоские входные данные varlena. Первые легче написать, но могут быть менее эффективными в целом, потому что преобразование плоского ввода в расширенную форму для использования одной функцией может стоить больше, чем экономится при работе в расширенном формате. Когда требуется обрабатывать только расширенный формат, преобразование плоских входных данных в расширенную форму может быть скрыто внутри макроса выборки аргументов, так что функция выглядит не более сложной, чем та, которая работает с традиционным вводом varlena. Чтобы обработать оба типа ввода, напишите функцию выборки аргумента, которая будет сбрасывать внешние, короткие и сжатые входные данные, но не расширенные. Такая функция может быть определена как возвращение указателя на объединение плоского формата varlena и расширенного формата. Вызывающие могут использовать VARATT_IS_EXPANDED_HEADER() чтобы определить, какой формат они получили.

Инфраструктура TOAST не только позволяет отличать обычные значения varlena от расширенных значений, но также различает указатели «чтение-запись» и «только для чтения» на расширенные значения. Функции C/RUST, которые должны проверять только расширенное значение или будут изменять его только безопасным и не семантически видимым образом, не должны заботиться о том, какой тип указателя они получают. Функции на C/RUST, которые создают измененную версию входного значения, могут изменять расширенное входное значение на месте, если они получают указатель чтения-записи, но не должны изменять ввод, если они получают указатель только для чтения; в этом случае они должны сначала скопировать значение, создав новое значение для изменения. Функция C/RUST, которая создала новое расширенное значение, всегда должна возвращать указатель на чтение-запись. Кроме того, функция C/RUST, которая изменяет расширенное значение для чтения-записи на месте, должна позаботиться о том, чтобы оставить значение в нормальном состоянии, если оно не будет выполнено частично.

Примеры работы с расширенными значениями см. В стандартной инфраструктуре массива, в частности в src/backend/utils/adt/array_expanded.c.

Пользовательские операторы

Каждый оператор является «синтаксическим сахаром» для вызова базовой функции, которая выполняет реальную работу; поэтому вы должны сначала создать базовую функцию, прежде чем сможете создать оператор. Тем не менее, оператор не просто синтаксический сахар, потому что он несет дополнительную информацию, которая помогает планировщику запросов оптимизировать запросы, использующие оператор. Следующий раздел будет посвящен объяснению этой дополнительной информации.

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

Вот пример создания оператора для сложения двух комплексных чисел. Мы предполагаем, что уже создали определение типа complex (см. раздел Пользовательские типы. Сначала нам нужна функция, которая будет выполнять работу, а затем мы сможем определить оператор:

CREATE FUNCTION complex_add(complex, complex)
    RETURNS complex
    AS 'filename', 'complex_add'
    LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR + (
    leftarg = complex,
    rightarg = complex,
    function = complex_add,
    commutator = +
);

Теперь мы можем выполнить запрос следующим образом:

SELECT (a + b) AS c FROM test_complex;

        c
-----------------
 (5.2,6.05)
 (133.42,144.95)

Это мы показали, как создать бинарный оператор. Чтобы создать унарный оператор, просто опустите один из leftarg (для левого унарного) или rightarg (для правого унарного). function и leftarg/rightarg являются единственными обязательными элементами в CREATE OPERATOR. Указание commutator, показанное в примере, является дополнительной подсказкой оптимизатору запросов. Дальнейшие подробности о commutator и других указаниях оптимизации приведены в следующем разделе.

Информация по оптимизации оператора

Определение оператора QHB может включать несколько необязательных указаний, которые сообщают системе полезные сведения о поведении оператора. Эти пункты должны предоставляться всякий раз, когда это уместно, поскольку они могут значительно ускорить выполнение запросов, использующих оператор. Но если вы их предоставите, вы должны быть уверены, что они действительно имеют место быть! Неправильное использование предложения по оптимизации может привести к медленным запросам, неправильному выводу или другим плохим вещам. Вы всегда можете опустить указание оптимизации, если вы не уверены в нём; единственное последствие — запросы могут выполняться медленнее, чем нужно.

Дополнительные пункты оптимизации могут быть добавлены в будущих версиях QHB. Описанные здесь — это те, которые есть в релизе 1.2.0.

Также можно прикрепить вспомогательную функцию для планировщика к функции, которая лежит в основе оператора — это другой способ рассказать системе о поведении оператора. См. раздел Информация по оптимизации функций для получения дополнительной информации.

COMMUTATOR

Указание COMMUTATOR, если оно есть, задаёт имя оператора, который является коммутатором данного. Мы говорим, что оператор A является коммутатором оператора B, если (x A y) равно (y B x) для всех возможных x, y. Обратите внимание, что B также является коммутатором A. Например, операторы < и > для определенного типа данных обычно являются коммутаторами друг друга, а оператор + обычно коммутирует сам с собой, т.е. является коммутативным. А вот оператор - не коммутирует ни с каким другим (понятно, что коммутатор - существует, но его не принято делать оператором).

Тип левого операнда коммутируемого оператора такой же, как тип правого операнда его коммутатора, и наоборот. Таким образом, имя оператора коммутатора - это все, что нужно дать QHB для поиска коммутатора, и это все, что необходимо указать в указании COMMUTATOR.

Очень важно предоставлять информацию о коммутаторах для операторов, которые будут использоваться в индексах и объединениях, поскольку это позволяет оптимизатору запросов «перевернуть» условие, чтобы привести его к форме, необходимой для различных типов планов. В частности, рассмотрим запрос с предложением WHERE, например tab1.x = tab2.y, где tab1.x и tab2.y имеют пользовательский тип, и предположим, что по tab2.y есть индекс. Чтобы сгенерировать сканирование индекса, оптимизатор должен привести условие к виду tab2.y = tab1.x, поскольку механизм сканирования индекса ожидает увидеть индексированный столбец слева от оператора, который ему дан. QHB не будет по умолчанию считать, что = коммутативно — создатель оператора = должен это указать, пометив оператор информацией коммутатора.

Когда вы определяете коммутативный оператор, вы просто делаете это. Когда вы определяете пару коммутативных операторов, все становится немного сложнее: как первый определяемый может ссылаться на другой, который вы еще не определили? Есть два решения этой проблемы:

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

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

NEGATOR

Это понятие есть только для булевых операторов. Указание NEGATOR, если оно есть, задаёт оператор, который является отрицанием данного. Мы говорим, что оператор A является отрицанием оператора B, если оба возвращают булевы результаты и (x A y) равно NOT (x B y) для всех возможных x, y. Обратите внимание, что B также является отрицанием A. Например, < и >= являются парой отрицателей для большинства типов данных. Оператор никогда не может быть собственным отрицателем.

В отличие от коммутаторов, пара унарных операторов может быть действительно отмечена как отрицатели друг друга; это означало бы (A x) равно NOT (B x) для всех x или эквивалент для правых унарных операторов.

Отрицатель оператора должен иметь те же типы левого и/или правого операнда, что и определяемый оператор, поэтому, как и в случае с COMMUTATOR предложении NEGATOR должно быть указано только имя оператора.

Предоставление отрицателя очень полезно для оптимизатора запросов, поскольку оно позволяет упростить выражения типа NOT (x >= y) в x < y. Это происходит чаще, чем вы думаете, потому что операции NOT могут быть появляться вследствие других преобразований.

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

RESTRICT

Это понятие есть только для булевых операторов. Указание RESTRICT задаёт функцию оценки селективности ограничения для оператора. (Обратите внимание, что это имя функции, а не оператора). Идея этой оценки состоит в том, чтобы угадать, какая доля строк в таблице будет удовлетворять условию WHERE column OP constant для данного оператора и постоянного значения в правой части. Это помогает оптимизатору, давая ему некоторое представление о том, сколько строк будет отфильтровано таким WHERE. (А что, если константа будет слева, спросите вы? Ну, это одна из вещей, для которых предназначен COMMUTATOR...)

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

Вспомогательная функцияОператор
eqsel=
neqsel<>
scalarltsel<
scalarlesel<=
scalargtsel>
scalargesel>=

Например, вы можете обойтись использованием eqsel / neqsel для операторов, которые имеют очень высокую / очень низкую селективность, даже если они на самом деле не имеют никакого отношения к равенсту/неравенству. Например, геометрические операторы приближенного равенства используют eqsel в предположении, что им обычно соответствует лишь небольшая часть записей в таблице.

Вы можете использовать scalarltsel, scalarlesel, scalargtsel и scalargesel для сравнения типов данных, которые имеют некоторые разумные средства преобразования в скалярные величины для сравнения диапазонов. В этом случае желательно добавить поддержку типа данных в функции convert_to_scalar().

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

JOIN

Это понятие есть только для булевых операторов. Указание JOIN задаёт функцию оценки избирательности соединения для оператора. (Обратите внимание, что это имя функции, а не имя оператора). Идея оценки избирательности соединения состоит в том, чтобы угадать, какая доля строк в паре таблиц будет удовлетворять условию вида ON table1.column1 OP table2.column2 для данного оператора. Как и в случае с RESTRICT, это существенно помогает оптимизатору, позволяя ему выяснить, какая из нескольких возможных последовательностей объединения может потребовать меньше всего работы.

И опять здесь не рассказывается, как писать свой функции оценки селективности, а просто предлагается использовать одну из стандартных функций оценки, если она похожа на то, что нужно:

Вспомогательная функцияОператор
eqjoinsel=
neqjoinsel<>
scalarltjoinsel<
scalarlejoinsel<=
scalargtjoinsel>
scalargejoinsel>=
areajoinsel2D area-based comparisons
positionjoinsel2D position-based comparisons
contjoinsel2D containment-based comparisons

HASHES

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

Предположение, лежащее в основе хеш-соединения, заключается в том, что оператор соединения может возвращать true только для пары значений, которые хешируют один и тот же хеш-код. Если два значения помещаются в разные хэш-блоки, объединение никогда не сравнит их вообще, неявно предполагая, что результат оператора соединения должен быть ложным. Поэтому нельзя указывать HASHES для операторов, которые не представляют некоторую форму равенства. В большинстве случаев практично поддерживать хеширование для операторов, которые принимают одинаковый тип данных с обеих сторон. Однако иногда возможно разработать совместимые хеш-функции для двух или более типов данных, то есть функции, которые будут генерировать одинаковые хеш-коды для «равных» значений, даже если значения имеют разные представления. Например, довольно просто обеспечить это свойство при хешировании целых чисел различной ширины.

Чтобы иметь быть HASHES, оператор соединения должен принадлежать семейству операторов хеш-индекса. Это не проверяется при создании оператора, поскольку, конечно, в этот момент семейство ссылочных операторов еще не может существовать. Но попытки использовать оператор в хеш-соединениях потерпят неудачу во время выполнения, если такого семейства операторов не существует. Обращение к семейству операторов нужно, чтобы найти специфические для типа данных хеш-функции. Разумеется, нужно создать подходящие хеш-функции, прежде чем вы сможете создать семейство операторов.

При создании хэш-функции следует соблюдать осторожность, поскольку существуют машинно-зависимые обстоятельства, провоцирующие ошибки. которыми она может не справиться с задачей. Например, если ваш тип данных — это структура, в которой есть неинформативные байты (исклютельно для выравнивания), в них может быть случайный мусор, и вы не можете просто передать всю структуру hash_any. (Чтобы этого избежать, следует писать все функции так, чтобы неинформативные байты заполнялись нулями.) Другой пример — на машинах, использующих числа с плавающей запятой в соотвествии со стандартом IEEE отрицательный ноль и положительный ноль - это разные значения (разные битовые комбинации), но они должны считаться равными. Если значение с плавающей запятой может содержать отрицательный ноль, то нужен дополнительный шаг перед хешированием.

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

Замечание
Функция, лежащая в основе HASHES-оператора, должна быть помечена как IMMUTABLE или STABLE. Если он VOLATILE, система никогда не будет пытаться использовать оператор для хеш-соединения.

Замечание
И если эта функция строгая (STRICT), она должна быть всюдуопределенной, т.е. должна возвращать true или false для любых NOT NULL входных значений. Если это правило не соблюдается, хэш-оптимизация условий IN может привести к неверным результатам. (В частности, IN может вернуть false, когда правильный ответ в соответствии со стандартом NULL; или может выдать ошибку, сообщающую, что NULL — недопустимый результат).

MERGES

Предложение MERGES, если оно присутствует, сообщает системе, что для соединения, основанного на этом операторе, можно использовать метод merge-join. MERGES имеет смысл только для двоичного оператора, который возвращает boolean, и на практике оператор должен представлять равенство для некоторого типа данных или пары типов данных.

Объединение слиянием основано на идее упорядочения левой и правой таблиц по порядку и последующего их параллельного сканирования. Таким образом, оба типа данных должны быть полностью упорядочены, и оператор соединения должен иметь какой тип, который может быть успешным только для пар значений, попадающих в «одно и то же место» в порядке сортировки. На практике это означает, что оператор соединения должен вести себя как равенство. Но возможно объединить два разных типа данных, если они логически совместимы. Например, оператор равенства smallint -versus-integer является присоединяемым слиянием. Нам нужны только операторы сортировки, которые приведут оба типа данных в логически совместимую последовательность.

Чтобы пометить MERGES, оператор соединения должен появляться как член равенства семейства операторов индекса btree. Это не применяется при создании оператора, поскольку, конечно, семейство ссылочных операторов еще не могло существовать. Но оператор фактически не будет использоваться для объединений слиянием, если не будет найдено соответствующее семейство операторов. Флаг MERGES таким образом, служит подсказкой для планировщика, что стоит искать подходящее семейство операторов.

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

Заметка
Функция, лежащая в основе оператора объединения слиянием, должна быть помечена как неизменяемая или стабильная. Если он изменчив, система никогда не будет пытаться использовать оператор для объединения слиянием.

Интерфейсные расширения для индексов

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

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

Индексные методы и операторные классы

Таблица pg_am содержит одну строку для каждого метода индекса (внутренне известного как метод доступа). Поддержка регулярного доступа к таблицам встроена в QHB, но все методы индекса описаны в pg_am. Можно добавить новый метод доступа к индексу, написав необходимый код и затем создав запись в pg_am - но это выходит за рамки этой главы (см. Определение интерфейса метода доступа индекса ).

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

Можно определить несколько классов операторов для одного и того же типа данных и метода индекса. Благодаря этому для одного типа данных можно определить несколько наборов семантики индексирования. Например, для B-дерева требуется определить порядок сортировки для каждого типа данных, с которым он работает. Для типа данных с комплексным числом может быть полезно иметь один класс операторов B-дерева, который сортирует данные по комплексному абсолютному значению, другой, который сортирует по вещественной части, и так далее. Как правило, один из классов операторов считается наиболее полезным и помечается как класс операторов по умолчанию для этого типа данных и метода индекса.

Одно и то же имя класса оператора можно использовать для нескольких различных методов индекса (например, методы B-дерева и хеш-индекса имеют классы операторов с именем int4_ops), но каждый такой класс является независимой сущностью и должен определяться отдельно.

Стратегии индексного метода

Операторы, связанные с классом операторов, идентифицируются «номерами стратегий», которые служат для идентификации семантики каждого оператора в контексте его класса операторов. Например, B-деревья накладывают строгий порядок на ключи, от меньшего к большему, и поэтому операторы типа «меньше чем» и «больше или равно» интересны в отношении B-дерева. Поскольку QHB позволяет пользователю определять операторы, QHB не может посмотреть на имя оператора (например, < или >= ) и сказать, какое это сравнение. Вместо этого метод индекса определяет набор «стратегий», которые можно рассматривать как обобщенные операторы. Каждый класс операторов определяет, какой фактический оператор соответствует каждой стратегии для конкретного типа данных и интерпретации семантики индекса.

Метод индексирования B-дерева определяет пять стратегий, показанных в таблице

Таблица 2. B-Tree Стратегии
ОперацияНомер стратегии
меньше, чем1
меньше или равно2
равный3
больше или равно4
больше чем5

Хеш-индексы поддерживают только сравнения на равенство, поэтому они используют только одну стратегию, показанную в таблице 3.

Таблица 3. Хэш-стратегии

ОперацияНомер стратегии
равный1

Индексы GiST более гибкие: у них нет фиксированного набора стратегий вообще. Вместо этого подпрограмма поддержки «согласованности» каждого конкретного класса операторов GiST интерпретирует числа стратегий так, как им нравится. Например, некоторые из встроенных классов операторов индекса GiST индексируют двумерные геометрические объекты, предоставляя стратегии «R-дерева», показанные в таблице 4. Четыре из них являются настоящими двумерными тестами (перекрывается, то же самое, содержит, содержится в); четыре из них рассматривают только направление X; и другие четыре обеспечивают те же самые тесты в направлении Y.

Таблица 4. GiST двумерные стратегии R-дерева

ОперацияНомер стратегии
строго слева от1
не распространяется на право2
перекрывается3
не распространяется на лево4
строго справа от5
одно и то же6
содержит7
содержится в8
не распространяется выше9
строго ниже10
строго выше11
не распространяется ниже12

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

Таблица 5. SP-GiST Point Strategies

ОперацияНомер стратегии
строго слева от1
строго справа от5
одно и то же6
содержится в8
строго ниже10
строго выше11

Индексы GIN аналогичны индексам GiST и SP-GiST тем, что у них также нет фиксированного набора стратегий. Вместо этого подпрограммы поддержки каждого класса операторов интерпретируют номера стратегий в соответствии с определением класса операторов. Например, номера стратегий, используемые встроенным классом операторов для массивов, показаны в таблице 6.

Таблица 6. Стратегии GIN Array

ОперацияНомер стратегии
перекрытие1
содержит2
содержится в3
равный4

Индексы BRIN аналогичны индексам GiST, SP-GiST и GIN тем, что они также не имеют фиксированного набора стратегий. Вместо этого подпрограммы поддержки каждого класса операторов интерпретируют номера стратегий в соответствии с определением класса операторов. В качестве примера, номера стратегий, используемые встроенными классами операторов Minmax, показаны в таблице 7.

Таблица 7. BRIN Minmax Стратегии

ОперацияНомер стратегии
меньше, чем1
меньше или равно2
равный3
больше или равно4
больше чем5

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

Процедуры поддержки метода индекса

Стратегии обычно не достаточно информации для системы, чтобы понять, как использовать индекс. На практике индексные методы требуют дополнительных подпрограмм поддержки для работы. Например, метод индексирования B-дерева должен уметь сравнивать два ключа и определять, больше ли один, равен или меньше другого. Точно так же метод хэш-индекса должен иметь возможность вычислять хеш-коды для ключевых значений. Эти операции не соответствуют операторам, используемым в квалификациях в командах SQL; они являются административными процедурами, используемыми внутренними методами индексации.

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

B-деревья требуют функции поддержки сравнения и позволяют предоставлять две дополнительные функции поддержки по усмотрению автора класса оператора, как показано в таблице 8. Требования к этим функциям поддержки объясняются далее в разделе Вспомогательные функции B-дерева.

Таблица 8. Функции поддержки B-Tree
ФункцияНомер поддержки
Сравните два ключа и верните целое число меньше нуля, нуля или больше нуля, указывая, является ли первый ключ меньше, равен или больше второго1
Возвратите адреса C-вызываемой функции поддержки сортировки (необязательно)2
Сравните значение теста с базовым значением плюс / минус смещение и верните true или false в соответствии с результатом сравнения (необязательно)3

Для хеш-индексов требуется одна вспомогательная функция, и она может предоставляться по второму усмотрению по усмотрению автора класса оператора, как показано в таблице 9.

Таблица 9. Функции поддержки хэша

ФункцияНомер поддержки
Вычислить 32-битное хеш-значение для ключа1
Вычислить 64-битное хеш-значение для ключа с учетом 64-битной соли; если соль равна 0, младшие 32 бита результата должны соответствовать значению, которое было бы вычислено функцией 1 (необязательно)2

Индексы GiST имеют девять вспомогательных функций, две из которых являются необязательными, как показано в таблице 10.

Таблица 10. GiST Поддержка Функции

ФункцияОписаниеНомер поддержки
consistentопределить, удовлетворяет ли ключ квалификатору запроса1
unionвычислить объединение набора ключей2
compressвычислить сжатое представление ключа или значения для индексации3
decompressвычислить распакованное представление сжатого ключа4
penaltyвычислить штраф за вставку нового ключа в поддерево с заданным ключом поддерева5
picksplitопределить, какие записи страницы должны быть перемещены на новую страницу, и вычислить ключи объединения для получающихся страниц6
equalсравнить два ключа и вернуть true, если они равны7
distanceопределить расстояние от ключа до значения запроса (необязательно)8
fetchвычислять исходное представление сжатого ключа для сканирования только по индексу (необязательно)9

Для индексов SP-GiST требуется пять вспомогательных функций, как показано в таблице 11.

Таблица 11. Функции поддержки SP-GiST

ФункцияОписаниеНомер поддержки
configпредоставить основную информацию о классе оператора1
chooseопределить, как вставить новое значение во внутренний кортеж2
picksplitопределить, как разбить набор значений3
inner_consistentопределить, какие подразделы нужно искать для запроса4
leaf_consistentопределить, удовлетворяет ли ключ квалификатору запроса5

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

Таблица 12. Функции поддержки GIN

ФункцияОписаниеНомер поддержки
compareсравнить два ключа и вернуть целое число меньше нуля, нуля или больше нуля, указывающее, является ли первый ключ меньше, равен или больше второго1
extractValueизвлекать ключи из значения для индексации2
extractQueryизвлекать ключи из условия запроса3
consistentопределить, соответствует ли значение условию запроса (логический вариант) (необязательно, если присутствует функция поддержки 6)4
comparePartialсравнивать частичный ключ из запроса и ключ из индекса и возвращать целое число меньше нуля, нуля или больше нуля, указывающее, должен ли GIN игнорировать эту запись индекса, рассматривать запись как совпадение или остановить сканирование индекса (необязательно)5
triConsistentопределить, соответствует ли значение условию запроса (троичный вариант) (необязательно, если присутствует функция поддержки 4)6

Индексы BRIN имеют четыре основные вспомогательные функции, как показано в таблице 13 ; эти основные функции могут потребовать предоставления дополнительных функций поддержки.

Таблица 13. Функции поддержки BRIN

ФункцияОписаниеНомер поддержки
opcInfoвернуть внутреннюю информацию, описывающую сводные данные индексированных столбцов1
add_valueдобавить новое значение в существующий кортеж итогового индекса2
consistentопределить, соответствует ли значение условию запроса3
unionвычислить объединение двух сводных кортежей4

В отличие от операторов поиска, функции поддержки возвращают тот тип данных, который ожидает конкретный метод индекса; например, в случае функции сравнения для B-деревьев целое число со знаком. Количество и типы аргументов каждой вспомогательной функции также зависят от метода индекса. Для B-дерева и хеша функции поддержки сравнения и хеширования принимают те же типы входных данных, что и операторы, включенные в класс операторов, но это не относится к большинству функций поддержки GiST, SP-GiST, GIN и BRIN.

Пример

Теперь, когда мы увидели идеи, вот обещанный пример создания нового класса операторов. (Вы можете найти рабочую копию этого примера в src/tutorial/complex.c и src/tutorial/complex.sql в исходном выпуске). Класс операторов инкапсулирует операторы, которые сортируют комплексные числа в порядке абсолютных значений, поэтому мы выбираем имя complex_abs_ops. Во-первых, нам нужен набор операторов. Процедура определения операторов обсуждалась в разделе Пользовательские операторы. Для класса операторов на B-деревьях нам нужны следующие операторы:

  • абсолютное значение меньше чем (стратегия 1)

  • абсолютное значение меньше или равно (стратегия 2)

  • абсолютное значение равно (стратегия 3)

  • абсолютное значение больше или равно (стратегия 4)

  • абсолютное значение больше чем (стратегия 5)

Наименее подверженный ошибкам способ определения связанного набора операторов сравнения - сначала написать функцию поддержки сравнения B-деревьев, а затем написать другие функции в виде однострочных оболочек вокруг функции поддержки. Это уменьшает шансы получения противоречивых результатов для угловых случаев. Следуя этому подходу, мы сначала пишем:

#define Mag(c)  ((c)->x*(c)->x + (c)->y*(c)->y)

static int
complex_abs_cmp_internal(Complex *a, Complex *b)
{
    double      amag = Mag(a),
                bmag = Mag(b);

    if (amag < bmag)
        return -1;
    if (amag > bmag)
        return 1;
    return 0;
}

Теперь функция меньше чем выглядит так:

PG_FUNCTION_INFO_V1(complex_abs_lt);

Datum
complex_abs_lt(PG_FUNCTION_ARGS)
{
    Complex    *a = (Complex *) PG_GETARG_POINTER(0);
    Complex    *b = (Complex *) PG_GETARG_POINTER(1);

    PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);
}

Другие четыре функции отличаются только тем, как они сравнивают результат внутренней функции с нулем.

Далее мы объявляем функции и операторы на основе этих функций в SQL:

CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
    AS 'filename', 'complex_abs_lt'
    LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR < (
   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
   commutator = >, negator = >=,
   restrict = scalarltsel, join = scalarltjoinsel
);

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

Другие вещи, которые стоит отметить, происходят здесь:

  • Может быть только один оператор с именем, скажем, = и принимающий complex типы для обоих операндов. В этом случае у нас нет никакого другого оператора = для complex, но если бы мы строили практический тип данных, мы бы, вероятно, хотели бы иметь = как обычную операцию равенства для комплексных чисел (а не равенства абсолютных значений). В этом случае нам нужно будет использовать другое имя оператора для complex_abs_eq.

  • Хотя QHB может справляться с функциями, имеющими одинаковое имя SQL, при условии, что они имеют разные типы данных аргументов, C может справиться только с одной глобальной функцией, имеющей данное имя. Поэтому мы не должны называть функцию C чем-то простым, например, abs_eq. Обычно рекомендуется включать имя типа данных в имя функции C, чтобы не конфликтовать с функциями других типов данных.

  • Мы могли бы сделать имя SQL для функции abs_eq, полагаясь на QHB, чтобы отличать его по типу данных аргумента от любой другой функции SQL с тем же именем. Чтобы упростить пример, мы заставляем функцию иметь одинаковые имена на уровне C и уровне SQL.

Следующим шагом является регистрация подпрограммы поддержки, требуемой B-деревьями. Пример кода C, который реализует это, находится в том же файле, который содержит функции оператора. Вот как мы объявляем функцию:

CREATE FUNCTION complex_abs_cmp(complex, complex)
    RETURNS integer
    AS 'filename'
    LANGUAGE C IMMUTABLE STRICT;

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

CREATE OPERATOR CLASS complex_abs_ops
    DEFAULT FOR TYPE complex USING btree AS
        OPERATOR        1       <,
        OPERATOR        2       <=,
        OPERATOR        3       =,
        OPERATOR        4       >=,
        OPERATOR        5       >,
        FUNCTION        1       complex_abs_cmp(complex, complex);

И мы сделали! Теперь должна быть возможность создавать и использовать B-деревья для complex столбцов.

Мы могли бы написать записи оператора более подробно, как в:

OPERATOR 1 < (complex, complex),

но нет необходимости делать это, когда операторы принимают тот же тип данных, для которого мы определяем класс операторов.

В приведенном выше примере предполагается, что вы хотите сделать этот новый класс операторов классом операторов B-дерева по умолчанию для complex типа данных. Если вы этого не сделаете, просто пропустите слово DEFAULT.

Классы операторов и семейства операторов

До сих пор мы неявно предполагали, что класс операторов имеет дело только с одним типом данных. Хотя в определенном столбце индекса, безусловно, может быть только один тип данных, часто полезно индексировать операции, которые сравнивают индексированный столбец со значением другого типа данных. Кроме того, если используется оператор кросс-типа данных в связи с классом операторов, часто бывает так, что другой тип данных имеет собственный связанный класс операторов. Полезно сделать связи между связанными классами явными, потому что это может помочь планировщику в оптимизации запросов SQL (особенно для классов операторов B-дерева, поскольку планировщик содержит много знаний о том, как работать с ними).

Для удовлетворения этих потребностей QHB использует концепцию семейства операторов. Семейство операторов содержит один или несколько классов операторов, а также может содержать индексируемые операторы и соответствующие вспомогательные функции, которые принадлежат семейству в целом, но не относятся к какому-либо одному классу в семействе. Мы говорим, что такие операторы и функции «свободны» в семье, а не связаны с определенным классом. Как правило, каждый класс операторов содержит операторы с одним типом данных, в то время как операторы с несколькими типами данных в семействе отсутствуют.

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

В качестве примера, QHB имеет встроенное семейство операторов B-дерева integer_ops, которое включает классы операторов int8_ops, int4_ops и int2_ops для индексов bigint (int8), integer (int4) и smallint (int2) соответственно. Семейство также содержит операторы сравнения между типами данных, позволяющие сравнивать любые два из этих типов, чтобы можно было искать индекс по одному из этих типов, используя значение сравнения другого типа. Семья может быть продублирована следующими определениями:

CREATE OPERATOR FAMILY integer_ops USING btree;

CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS
  -- standard int8 comparisons
  OPERATOR 1 <,
  OPERATOR 2 <=,
  OPERATOR 3 =,
  OPERATOR 4 >=,
  OPERATOR 5 >,
  FUNCTION 1 btint8cmp(int8, int8),
  FUNCTION 2 btint8sortsupport(internal),
  FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ;

CREATE OPERATOR CLASS int4_ops
DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS
  -- standard int4 comparisons
  OPERATOR 1 <,
  OPERATOR 2 <=,
  OPERATOR 3 =,
  OPERATOR 4 >=,
  OPERATOR 5 >,
  FUNCTION 1 btint4cmp(int4, int4),
  FUNCTION 2 btint4sortsupport(internal),
  FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ;

CREATE OPERATOR CLASS int2_ops
DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS
  -- standard int2 comparisons
  OPERATOR 1 <,
  OPERATOR 2 <=,
  OPERATOR 3 =,
  OPERATOR 4 >=,
  OPERATOR 5 >,
  FUNCTION 1 btint2cmp(int2, int2),
  FUNCTION 2 btint2sortsupport(internal),
  FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ;

ALTER OPERATOR FAMILY integer_ops USING btree ADD
  -- cross-type comparisons int8 vs int2
  OPERATOR 1 < (int8, int2),
  OPERATOR 2 <= (int8, int2),
  OPERATOR 3 = (int8, int2),
  OPERATOR 4 >= (int8, int2),
  OPERATOR 5 > (int8, int2),
  FUNCTION 1 btint82cmp(int8, int2),

  -- cross-type comparisons int8 vs int4
  OPERATOR 1 < (int8, int4),
  OPERATOR 2 <= (int8, int4),
  OPERATOR 3 = (int8, int4),
  OPERATOR 4 >= (int8, int4),
  OPERATOR 5 > (int8, int4),
  FUNCTION 1 btint84cmp(int8, int4),

  -- cross-type comparisons int4 vs int2
  OPERATOR 1 < (int4, int2),
  OPERATOR 2 <= (int4, int2),
  OPERATOR 3 = (int4, int2),
  OPERATOR 4 >= (int4, int2),
  OPERATOR 5 > (int4, int2),
  FUNCTION 1 btint42cmp(int4, int2),

  -- cross-type comparisons int4 vs int8
  OPERATOR 1 < (int4, int8),
  OPERATOR 2 <= (int4, int8),
  OPERATOR 3 = (int4, int8),
  OPERATOR 4 >= (int4, int8),
  OPERATOR 5 > (int4, int8),
  FUNCTION 1 btint48cmp(int4, int8),

  -- cross-type comparisons int2 vs int8
  OPERATOR 1 < (int2, int8),
  OPERATOR 2 <= (int2, int8),
  OPERATOR 3 = (int2, int8),
  OPERATOR 4 >= (int2, int8),
  OPERATOR 5 > (int2, int8),
  FUNCTION 1 btint28cmp(int2, int8),

  -- cross-type comparisons int2 vs int4
  OPERATOR 1 < (int2, int4),
  OPERATOR 2 <= (int2, int4),
  OPERATOR 3 = (int2, int4),
  OPERATOR 4 >= (int2, int4),
  OPERATOR 5 > (int2, int4),
  FUNCTION 1 btint24cmp(int2, int4),

  -- cross-type in_range functions
  FUNCTION 3 in_range(int4, int4, int8, boolean, boolean),
  FUNCTION 3 in_range(int4, int4, int2, boolean, boolean),
  FUNCTION 3 in_range(int2, int2, int8, boolean, boolean),
  FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;

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

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

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

Индексы GiST, SP-GiST и GIN не имеют явного представления об операциях с кросс-типом данных. Набор поддерживаемых операторов - это то, что могут обрабатывать основные вспомогательные функции для данного класса операторов.

В BRIN требования зависят от структуры, предоставляющей классы операторов. Для классов операторов, основанных на minmax, требуемое поведение такое же, как и для семейств операторов B-дерева: все операторы в семействе должны выполнять совместимую сортировку, и приведение не должно изменять связанный порядок сортировки.

Системные зависимости от классов операторов

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

В частности, существуют функции SQL, такие как ORDER BY и DISTINCT которые требуют сравнения и сортировки значений. Чтобы реализовать эти функции для определенного пользователем типа данных, QHB ищет класс оператора B-дерева по умолчанию для типа данных. Член «равно» этого класса операторов определяет представление системы о равенстве значений для GROUP BY и DISTINCT, а порядок сортировки, налагаемый классом операторов, определяет порядок ORDER BY по умолчанию.

Если для типа данных не существует класса операторов B-дерева по умолчанию, система будет искать класс операторов хеша по умолчанию. Но поскольку этот класс операторов обеспечивает только равенство, он может поддерживать только группирование, а не сортировку.

Если для типа данных не существует класса операторов по умолчанию, вы получите ошибки типа «не удалось определить оператор упорядочения», если попытаетесь использовать эти функции SQL с типом данных.

Сортировка по классу операторов B-дерева, отличному от заданного по умолчанию, возможна, если указать, например, оператор класса меньше в опции USING, например:

SELECT * FROM mytable ORDER BY somecol USING ~<~;

В качестве альтернативы, указать оператор класса больше, чем в USING выбрать сортировку по убыванию.

Сравнение массивов определенного пользователем типа также основывается на семантике, определенной классом операторов B-дерева по умолчанию для этого типа. Если класс операторов B-дерева по умолчанию отсутствует, но есть класс операторов хеш-функций по умолчанию, то поддерживается равенство массивов, но не сравнение по порядку.

Еще одна особенность SQL, которая требует еще больших знаний о типе данных, - это опция кадрирования RANGE offset PRECEDING/FOLLOWING для оконных функций (см. раздел Вызовы оконных функций). Для запроса, такого как

SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING)
  FROM mytable;

недостаточно знать, как упорядочить по x; база данных также должна понимать, как «вычесть 5» или «добавить 10» к значению x текущей строки, чтобы идентифицировать границы текущего фрейма окна. Сравнение результирующих границ со значениями x других строк возможно с помощью операторов сравнения, предоставляемых классом операторов B-дерева, который определяет порядок ORDER BY, но операторы сложения и вычитания не являются частью класса операторов, поэтому какие из них следует использовать? Такой выбор был бы нежелателен, поскольку разные порядки сортировки (разные классы операторов B-дерева) могут нуждаться в разном поведении. Поэтому класс операторов B-дерева может указывать вспомогательную функцию in_range, которая инкапсулирует поведения сложения и вычитания, которые имеют смысл для порядка сортировки. Он может даже обеспечить более одной функции поддержки in_range, если имеется более одного типа данных, который имеет смысл использовать в качестве смещения в предложениях RANGE. Если класс оператора B-дерева, связанный с предложением окна ORDER BY, не имеет соответствующей функции поддержки in_range, опция RANGE offset PRECEDING/FOLLOWING не поддерживается.

Другим важным моментом является то, что оператор равенства, который появляется в семействе хеш-операторов, является кандидатом на хеш-объединения, агрегирование хеш-функций и связанные с ними оптимизации. Семейство хеш-операторов здесь важно, так как оно идентифицирует хеш-функцию (и) для использования.

Операторы сортировки

Некоторые методы доступа к индексу (в настоящее время только GiST и SP-GiST) поддерживают концепцию операторов упорядочения. До сих пор мы обсуждали поисковые операторы. Оператор поиска - это оператор, для которого можно выполнить поиск по индексу, чтобы найти все строки, удовлетворяющие WHERE indexed_column operator constant. Обратите внимание, что ничего не обещано о порядке, в котором будут возвращены соответствующие строки. Напротив, оператор упорядочения не ограничивает набор строк, которые могут быть возвращены, а вместо этого определяет их порядок. Оператор упорядочения - это оператор, для которого индекс может быть отсканирован для получения строк в порядке, представленном ORDER BY indexed_column operator constant. Причиной определения операторов упорядочения таким образом является то, что он поддерживает поиск ближайшего соседа, если оператор измеряет расстояние. Например, запрос типа

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

находит десять мест, ближайших к заданной целевой точке. Индекс GiST для столбца местоположения может сделать это эффективно, потому что <-> является оператором упорядочения.

В то время как операторы поиска должны возвращать логические результаты, операторы упорядочения обычно возвращают некоторый другой тип, например, с плавающей или числовой для расстояний. Этот тип обычно не совпадает с индексируемым типом данных. Чтобы избежать жестких предположений о поведении различных типов данных, определение оператора упорядочения необходимо для именования семейства операторов B-дерева, которое определяет порядок сортировки результирующего типа данных. Как было сказано в предыдущем разделе, семейства операторов B-дерева определяют понятие упорядочения в QHB, так что это естественное представление. Поскольку оператор точки <-> возвращает float8, его можно указать в команде создания класса оператора, например:

OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops

где float_ops - это встроенное семейство операторов, которое включает операции на float8. В этом объявлении говорится, что индекс может возвращать строки в порядке возрастания значений оператора <->.

Особенности операторских классов

Есть две особенности классов операторов, которые мы еще не обсуждали, в основном потому, что они бесполезны с наиболее часто используемыми индексными методами.

Как правило, объявление оператора в качестве члена класса (или семейства) оператора означает, что метод индекса может извлечь именно набор строк, которые удовлетворяют условию WHERE, используя оператор. Например:

SELECT * FROM table WHERE integer_column < 4;

может быть точно удовлетворен B-деревом по целочисленному столбцу. Но бывают случаи, когда индекс полезен как неточное руководство для соответствующих строк. Например, если индекс GiST хранит только ограничивающие рамки для геометрических объектов, он не может точно удовлетворить условие WHERE, которое проверяет перекрытие между непрямоугольными объектами, такими как многоугольники. Тем не менее, мы могли бы использовать индекс для поиска объектов, ограничивающий прямоугольник которых перекрывает ограничивающий прямоугольник целевого объекта, а затем выполнить точный тест перекрытия только для объектов, найденных индексом. Если этот сценарий применим, индекс считается «потерянным» для оператора. Поиск по индексу с потерями реализуется с помощью метода индекса, возвращающего флаг перепроверки, когда строка может или не может действительно удовлетворять условию запроса. Базовая система затем проверит исходное условие запроса в извлеченной строке, чтобы увидеть, следует ли возвращать его в качестве действительного соответствия. Этот подход работает, если индекс гарантированно возвращает все необходимые строки плюс, возможно, некоторые дополнительные строки, которые можно устранить, выполнив исходный вызов оператора. Методы индекса, которые поддерживают поиск с потерями (в настоящее время GiST, SP-GiST и GIN), позволяют функциям поддержки отдельных классов операторов устанавливать флаг повторной проверки, и, таким образом, это по сути функция класса операторов.

Рассмотрим снова ситуацию, когда мы храним в индексе только ограничивающую рамку сложного объекта, такого как многоугольник. В этом случае нет смысла хранить весь многоугольник в элементе индекса - мы могли бы также хранить просто более простой объект типа box. Эта ситуация выражается опцией STORAGE в CREATE OPERATOR CLASS: мы напишем что-то вроде:

CREATE OPERATOR CLASS polygon_ops
    DEFAULT FOR TYPE polygon USING gist AS
        ...
        STORAGE box;

В настоящее время только методы индекса GiST, GIN и BRIN поддерживают тип STORAGE, который отличается от типа данных столбца. Процедуры поддержки сжатия и распаковки GiST должны иметь дело с преобразованием типов данных при использовании STORAGE. В GIN тип STORAGE идентифицирует тип значений «ключа», который обычно отличается от типа индексированного столбца - например, класс оператора для столбцов целочисленного массива может иметь ключи, которые являются просто целыми числами. Функции извлечения GIN extractValue и extractQuery отвечают за извлечение ключей из индексированных значений. BRIN аналогичен GIN: тип STORAGE определяет тип хранимых итоговых значений, а процедуры поддержки классов операторов отвечают за правильную интерпретацию итоговых значений.

Упаковка связанных объектов в расширение

Полезное расширение QHB обычно включает несколько объектов SQL; например, новый тип данных потребует новых функций, новых операторов и, возможно, новых классов операторов индекса. Полезно собрать все эти объекты в один пакет, чтобы упростить управление базой данных. QHB называет такой пакет расширением. Чтобы определить расширение, вам нужен как минимум файл сценария, который содержит команды SQL для создания объектов расширения, и управляющий файл, который задает несколько основных свойств самого расширения. Если расширение включает в себя C/RUST-код, обычно также будет файл общей библиотеки, в который был встроен C/RUST-код. Когда у вас есть эти файлы, простая команда (см. CREATE EXTENSION загружает объекты в вашу базу данных.

Основное преимущество использования расширения вместо простого запуска сценария SQL для загрузки группы «незакрепленных» объектов в вашу базу данных заключается в том, что QHB поймет, что объекты расширения объединяются. Вы можете удалить все объекты с помощью одной команды DROP EXTENSION (нет необходимости поддерживать отдельный сценарий «удаления»). Еще более полезно, что qhb_dump знает, что он не должен создавать дамп отдельных объектов-членов расширения - вместо этого он будет просто включать команду CREATE EXTENSION в дампы. Это значительно упрощает миграцию на новую версию расширения, которая может содержать больше объектов или отличаться от старой версии. Однако обратите внимание, что при загрузке такого дампа в новую базу данных вы должны иметь доступ к элементу управления, сценарию и другим файлам расширения.

QHB не позволит вам удалить отдельный объект, содержащийся в расширении, за исключением удаления всего расширения. Кроме того, хотя вы можете изменить определение объекта-члена расширения (например, с помощью функции CREATE OR REPLACE FUNCTION для функции), имейте в виду, что измененное определение не будет выгружено qhb_dump. Такое изменение обычно имеет смысл только в том случае, если вы одновременно вносите такое же изменение в файл сценария расширения. (Но есть специальные положения для таблиц, содержащих данные конфигурации; см. Раздел Упаковка связанных объектов в расширение). В производственных ситуациях, как правило, лучше создавать сценарий обновления расширения для выполнения изменений в объектах-членах расширения.

Сценарий расширения может устанавливать привилегии для объектов, являющихся частью расширения, с помощью операторов GRANT и REVOKE. Окончательный набор привилегий для каждого объекта (если они установлены) будет сохранен в системном каталоге pg_init_privs. Когда используется qhb_dump, команда CREATE EXTENSION будет включена в дамп, за которым следует набор операторов GRANT и REVOKE необходимых для того, чтобы установить привилегии для объектов такими, какими они были на момент получения дампа.

QHB в настоящее время не поддерживает сценарии расширения, выдающие операторы CREATE POLICY или SECURITY LABEL. Ожидается, что они будут установлены после создания расширения. Все политики RLS и метки безопасности на объектах расширения будут включены в дампы, созданные qhb_dump.

Механизм расширения также содержит положения для упаковки сценариев модификации, которые корректируют определения объектов SQL, содержащихся в расширении. Например, если версия 1.1 расширения добавляет одну функцию и изменяет тело другой функции по сравнению с 1.0, автор расширения может предоставить скрипт обновления, который вносит только эти два изменения. Затем можно применить команду ALTER EXTENSION UPDATE чтобы применить эти изменения и отследить, какая версия расширения фактически установлена в данной базе данных.

Виды объектов SQL, которые могут быть членами расширения, показаны в описании ALTER EXTENSION. В частности, объекты, относящиеся к общему кластеру базы данных, такие как базы данных, роли и табличные пространства, не могут быть членами расширения, поскольку расширение известно только в одной базе данных. (Хотя сценарию расширения не запрещено создавать такие объекты, в этом случае они не будут отслеживаться как часть расширения). Также обратите внимание, что хотя таблица может быть членом расширения, ее вспомогательные объекты, такие как индексы, непосредственно не считаются членами расширения. Другим важным моментом является то, что схемы могут принадлежать расширениям, но не наоборот: расширение как таковое имеет неквалифицированное имя и не существует «внутри» какой-либо схемы. Объекты-члены расширения, тем не менее, будут принадлежать схемам, когда это уместно для их типов объектов. Расширение может или не может быть подходящим для того, чтобы расширение владело схемой (схемами), в которой находятся его элементы-члены.

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

Определение объектов расширения

Широко распространенные расширения должны предполагать немного о базе данных, которую они занимают. В частности, если вы не указали SET search_path = pg_temp, предположите, что каждое неквалифицированное имя может преобразовываться в объект, определенный злоумышленником. Остерегайтесь конструкций, которые неявно зависят от search_path: выражения IN и CASE expression WHEN всегда выбирают оператор, используя путь поиска. Вместо них используйте OPERATOR(schema.=) ANY и CASE WHEN expression.

Файлы расширений

Команда CREATE EXTENSION опирается на управляющий файл для каждого расширения, который должен называться так же, как расширение с суффиксом .control, и должен быть помещен в каталог SHAREDIR/extension. Также должен быть хотя бы один файл сценария SQL, который следует за расширению шаблона именования extension--version.sql (например, foo--1.0.sql для версии 1.0 расширения foo). По умолчанию файлы сценариев также размещаются в каталог SHAREDIR/extension; но файл управления может указывать другой каталог для файла(ов) сценария.

Формат файла для файла управления расширениями такой же, как и для файла qhb.conf, а именно список назначений parameter_name = value, по одному на строку. Пустые строки и комментарии, представленные #, разрешены. Не забудьте указать любое значение, которое не является ни одним словом или числом.

Управляющий файл может устанавливать следующие параметры:

directory (string)
Каталог, содержащий файл(ы) SQL- сценария расширения. Если не указан абсолютный путь, имя SHAREDIR каталога SHAREDIR установки. Поведение по умолчанию эквивалентно указанию directory = ’extension’.
default_version (string)
Версия расширения по умолчанию (та, которая будет установлена, если в CREATE EXTENSION не указана версия). Хотя это может быть опущено, это приведет к сбою CREATE EXTENSION если опция VERSION не появится, поэтому вы обычно не хотите этого делать.
comment (string)
Комментарий (любая строка) о расширении. Комментарий применяется при первоначальном создании расширения, но не при его обновлении (поскольку это может переопределить добавленные пользователем комментарии). Кроме того, комментарий расширения можно установить, написав команду COMMENT в файле сценария.
encoding (string)
Кодировка набора символов, используемая в файле(ах) скрипта. Это следует указывать, если файлы сценариев содержат символы, не относящиеся к ASCII. В противном случае предполагается, что файлы находятся в кодировке базы данных.
module_pathname (string)
Значение этого параметра будет заменено для каждого вхождения MODULE_PATHNAME в файлах скриптов. Если он не установлен, замена не производится. Как правило, это значение равно $libdir/shared_library_name а затем MODULE_PATHNAME используется в командах CREATE FUNCTION для функций языка C/RUST, поэтому файлам сценариев не нужно жестко связывать имя разделяемой библиотеки.
requires (string)
Список имен расширений, от которых зависит это расширение, например requires = ’foo, bar’. Эти расширения должны быть установлены до того, как можно будет установить это расширение.
superuser (boolean)
Если этот параметр имеет значение true (по умолчанию), только суперпользователи могут создать расширение или обновить его до новой версии. Если установлено значение false, требуются только те привилегии, которые необходимы для выполнения команд в сценарии установки или обновления.
relocatable (boolean)
Расширение можно перемещать, если возможно переместить содержащиеся в нем объекты в другую схему после первоначального создания расширения. По умолчанию установлено значение false, то есть расширение не может быть перемещено. См. Раздел Перемещаемость расширения для получения дополнительной информации.
schema (string)
Этот параметр может быть установлен только для не перемещаемых расширений. Это заставляет расширение загружаться в точно названную схему, а не в любую другую. Параметр schema используется только при первоначальном создании расширения, а не при его обновлении. См. Раздел Перемещаемость расширения для получения дополнительной информации.

В дополнение к основному управляющему файлу extension.control расширение может иметь вторичные управляющие файлы, названные в расширении стиля extension--version.control. Если они есть, они должны находиться в каталоге файлов сценариев. Вторичные управляющие файлы имеют тот же формат, что и основной управляющий файл. Любые параметры, установленные во вторичном управляющем файле, переопределяют первичный управляющий файл при установке или обновлении до этой версии расширения. Однако каталог параметров и default_version нельзя установить во вторичном управляющем файле.

Файлы сценариев SQL расширения могут содержать любые команды SQL, кроме команд управления транзакциями (BEGIN, COMMIT и т.д.) И команд, которые не могут быть выполнены внутри блока транзакции (например, VACUUM). Это связано с тем, что файлы сценариев неявно выполняются внутри блока транзакции.

Файлы сценариев SQL расширения также могут содержать строки, начинающиеся с \echo, которые будут игнорироваться (обрабатываться как комментарии) механизмом расширения. Это положение обычно используется для выдачи ошибки, если файл сценария подается в qsql, а не загружается через CREATE EXTENSION (см. Пример сценария в разделе Упаковка связанных объектов в расширение). Без этого пользователи могут случайно загрузить содержимое расширения как «незакрепленные» объекты, а не как расширение, - состояние дел, которое немного утомительно восстанавливать.

Хотя файлы сценариев могут содержать любые символы, разрешенные указанной кодировкой, управляющие файлы должны содержать только простой ASCII, поскольку QHB не может узнать, в какой кодировке находится управляющий файл. На практике это проблема, только если вы хотите используйте не-ASCII символы в комментарии расширения. В этом случае рекомендуется не использовать параметр comment к контрольному файлу, а вместо этого использовать COMMENT ON EXTENSION в файле сценария для установки комментария.

Перемещаемость расширения

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

  • Полностью перемещаемое расширение может быть перемещено в другую схему в любое время, даже после его загрузки в базу данных. Это делается с помощью команды ALTER EXTENSION SET SCHEMA, которая автоматически переименовывает все объекты-члены в новую схему. Обычно это возможно только в том случае, если расширение не содержит внутренних предположений о том, в какой схеме находится какой-либо из его объектов. Кроме того, все объекты расширения должны начинаться с одной схемы (игнорируя объекты, которые не принадлежат какой-либо схеме, например: процедурные языки). Отметьте полностью перемещаемое расширение, установив relocatable = true в его контрольный файл.

  • Расширение может перемещаться во время установки, но не после. Обычно это происходит, если файл сценария расширения должен явно ссылаться на целевую схему, например, при настройке свойств search_path для функций SQL. Для такого расширения установите relocatable = false в его управляющем файле и используйте @extschema@ чтобы обратиться к целевой схеме в файле сценария. Все вхождения этой строки будут заменены фактическим именем целевой схемы перед выполнением сценария. Пользователь может установить целевую схему, используя опцию SCHEMA команды CREATE EXTENSION.

  • Если расширение вообще не поддерживает перемещение, установите в его управляющем файле relocatable = false, а также задайте для schema имя предполагаемой целевой схемы. Это предотвратит использование опции SCHEMA CREATE EXTENSION, если только в ней не указана та же схема, что и в контрольном файле. Этот выбор обычно необходим, если расширение содержит внутренние предположения об именах схем, которые нельзя заменить использованием @extschema@. Механизм замещения @extschema@ доступен в этом случае, хотя он имеет ограниченное использование, поскольку имя схемы определяется управляющим файлом.

Во всех случаях файл сценария будет выполняться с параметром search_path, изначально установленным для указания на целевую схему; то есть CREATE EXTENSION делает эквивалент этого:

SET LOCAL search_path TO @extschema@;

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

Целевая схема определяется параметром schema в управляющем файле, если он указан, в противном случае - параметром SCHEMA в CREATE EXTENSION если он задан, в противном случае - текущей схемой создания объекта по умолчанию (первой в пути search_path вызывающего). Когда используется параметр schema управляющего файла, целевая схема будет создана, если она еще не существует, но в двух других случаях она уже должна существовать.

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

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

Таблицы конфигурации расширений

Некоторые расширения включают таблицы конфигурации, которые содержат данные, которые могут быть добавлены или изменены пользователем после установки расширения. Обычно, если таблица является частью расширения, ни определение таблицы, ни ее содержимое не будут выгружены qhb_dump. Но такое поведение нежелательно для таблицы конфигурации; любые изменения данных, сделанные пользователем, должны быть включены в дампы, иначе расширение будет вести себя по-другому после дампа и перезагрузки.

Чтобы решить эту проблему, файл сценария расширения может пометить созданную им таблицу или последовательность как отношение конфигурации, что заставит qhb_dump включить содержимое таблицы или последовательности (не ее определение) в дампы. Для этого вызовите функцию pg_extension_config_dump(regclass, text) после создания таблицы или последовательности, например

CREATE TABLE my_config (key text, value text);
CREATE SEQUENCE my_config_seq;

SELECT pg_catalog.pg_extension_config_dump('my_config', '');
SELECT pg_catalog.pg_extension_config_dump('my_config_seq', '');

Таким образом можно пометить любое количество таблиц или последовательностей. Последовательности, связанные с serial или bigserial столбцов, также могут быть отмечены.

Когда второй аргумент pg_extension_config_dump является пустой строкой, все содержимое таблицы выгружается с помощью qhb_dump. Обычно это верно только в том случае, если таблица изначально пуста, как это создано сценарием расширения. Если в таблице содержится смесь исходных данных и данных, предоставленных пользователем, второй аргумент pg_extension_config_dump предоставляет условие WHERE которое выбирает данные, которые должны быть выгружены. Например, вы можете сделать

CREATE TABLE my_config (key text, value text, standard_entry boolean);

SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entry');

и затем убедитесь, что standard_entry имеет значение true только в строках, созданных сценарием расширения.

Для последовательностей второй аргумент pg_extension_config_dump имеет никакого эффекта.

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

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

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

Последовательности, связанные с serial или bigserial столбцами, должны быть помечены напрямую, чтобы вывести их состояние. Маркировка их родительских отношений недостаточно для этой цели.

Обновления расширений

Одним из преимуществ механизма расширения является то, что он предоставляет удобные способы управления обновлениями команд SQL, которые определяют объекты расширения. Это делается путем привязки имени или номера версии к каждой выпущенной версии сценария установки расширения. Кроме того, если вы хотите, чтобы пользователи могли динамически обновлять свои базы данных с одной версии на другую, вы должны предоставить сценарии обновления, которые вносят необходимые изменения для перехода с одной версии на другую. Сценарии обновления имеют имена, следующего шаблона extension--old_version--target_version.sql (например, foo--1.0--1.1.sql содержит команды для изменения версии 1.0 расширения foo в версию 1.1 ).

При наличии подходящего сценария обновления команда ALTER EXTENSION UPDATE обновит установленное расширение до указанной новой версии. Сценарий обновления выполняется в той же среде, которую CREATE EXTENSION предоставляет для сценариев установки: в частности, search_path настраивается таким же образом, и любые новые объекты, созданные сценарием, автоматически добавляются в расширение. Кроме того, если сценарий выбирает удаление объектов-членов расширения, они автоматически отсоединяются от расширения.

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

Механизм обновления может использоваться для решения важного особого случая: преобразования «свободной» коллекции объектов в расширение. До того, как механизм расширения был добавлен в PostgreSQL (в 9.1), многие люди писали модули расширения, которые просто создавали разные неупакованные объекты. Учитывая существующую базу данных, содержащую такие объекты, как мы можем преобразовать объекты в правильно упакованное расширение? Удаление их, а затем выполнение простого CREATE EXTENSION - один из способов, но нежелательно, если у объектов есть зависимости (например, если существуют столбцы таблицы типа данных, созданные расширением). Чтобы исправить эту ситуацию, нужно создать пустое расширение, затем использовать ALTER EXTENSION ADD чтобы присоединить каждый существующий объект к расширению, а затем, наконец, создать любые новые объекты, которые находятся в текущей версии расширения, но отсутствуют в распакованном выпуске. CREATE EXTENSION поддерживает этот случай с опцией FROM old_version, которая заставляет его не запускать обычный скрипт установки для целевой версии, а вместо этого сценарий обновления с именем extension--old_version--target_version.sql. Выбор имени фиктивной версии для использования в качестве old_version зависит от автора расширения, хотя unpackaged является общим соглашением. Если у вас есть несколько предыдущих версий, вы должны иметь возможность обновиться до стиля расширения, используйте несколько фиктивных названий версий для их идентификации.

ALTER EXTENSION может выполнять последовательности файлов сценариев обновления для достижения запрошенного обновления. Например, если доступны только foo--1.0--1.1.sql и foo--1.1--2.0.sql, ALTER EXTENSION будет применять их последовательно, если будет запрошено обновление до версии 2.0, а в данный момент установлена версия 1.0.

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

Иногда полезно предоставить сценарии «понижения», например, foo--1.1--1.0.sql чтобы позволить отменить изменения, связанные с версией 1.1. Если вы это сделаете, будьте осторожны с возможностью неожиданного применения скрипта понижения, поскольку он дает более короткий путь. Рискованный случай - это сценарий обновления «быстрого пути», который переходит вперед на несколько версий, а также сценарий перехода к начальной точке быстрого пути. Может потребоваться меньше шагов, чтобы применить понижение и затем быстрый путь, чем продвигаться вперед по одной версии за раз. Если скрипт понижения удаляет незаменимые объекты, это приведет к нежелательным результатам.

Чтобы проверить наличие неожиданных путей обновления, используйте эту команду:

SELECT * FROM pg_extension_update_paths('extension_name');

Это показывает каждую пару различных известных имен версий для указанного расширения вместе с последовательностью пути обновления, которая была бы принята, чтобы получить от исходной версии до целевой версии, или NULL если нет доступного пути обновления. Путь показан в текстовом виде с -- разделителями. Вы можете использовать regexp_split_to_array(path,'--') если вы предпочитаете формат массива.

Установка расширений с использованием скриптов обновления

Расширение, которое существует уже некоторое время, вероятно, будет существовать в нескольких версиях, для которых автору потребуется написать сценарии обновления. Например, если вы выпустили расширение foo в версиях 1.0, 1.1 и 1.2, должны быть сценарии обновления foo--1.0--1.1.sql и foo--1.1--1.2.sql. До PostgreSQL 10 необходимо было также создавать новые файлы сценариев foo--1.1.sql и foo--1.2.sql которые напрямую собирали более новые версии расширений, иначе более новые версии не могли быть установлены напрямую, только путем установки 1.0 и затем обновление. Это было утомительно и дублировало, но теперь это не нужно, потому что CREATE EXTENSION может автоматически следовать цепочкам обновлений. Например, если доступны только файлы сценариев foo--1.0.sql, foo--1.0--1.1.sql и foo--1.1--1.2.sql, то запрос на установку версии 1.2 выполняется с помощью запуска этих трех сценарии в последовательности. Обработка такая же, как если бы вы сначала установили 1.0 а затем обновили до 1.2. (Как и в случае с ALTER EXTENSION UPDATE, если доступно несколько путей, предпочтительнее использовать кратчайший путь). Размещение файлов сценариев расширения в этом стиле может уменьшить объем работ по обслуживанию, необходимых для создания небольших обновлений.

Если вы используете вторичные (зависящие от версии) контрольные файлы с расширением, поддерживаемым в этом стиле, имейте в виду, что каждой версии нужен контрольный файл, даже если у него нет отдельного сценария установки, поскольку этот контрольный файл будет определять, как неявное обновление на эту версию выполняется. Например, если foo--1.0.control указывает requires = 'bar' но другие управляющие файлы foo этого не делают, зависимость расширения от bar будет сброшена при обновлении с 1.0 до другой версии.

Пример расширения

Вот полный пример SQL- расширения, двухэлементного составного типа, который может хранить значения любого типа в своих слотах, которые называются k и v. Нетекстовые значения автоматически преобразуются в текст для хранения.

Скриптовый файл pair--1.0.sql выглядит так:

-- complain if script is sourced in qsql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pair" to load this file. \quit

CREATE TYPE pair AS ( k text, v text );

CREATE OR REPLACE FUNCTION pair(text, text)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';

CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);

-- "SET search_path" is easy to get right, but qualified names perform better.
CREATE OR REPLACE FUNCTION lower(pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'
SET search_path = pg_temp;

CREATE OR REPLACE FUNCTION pair_concat(pair, pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
               $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';

Управляющий файл pair.control выглядит так:

# pair extension
comment = 'A key/value pair data type'
default_version = '1.0'
relocatable = false

Хотя вам вряд ли нужен make-файл для установки этих двух файлов в правильный каталог, вы можете использовать Makefile содержащий это:

EXTENSION = pair
DATA = pair--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Этот make-файл основан на PGXS, что описано в следующем разделе. Команда make install установит управляющие файлы и файлы сценариев в правильный каталог, как сообщает pg_config.

Как только файлы установлены, используйте команду CREATE EXTENSION, чтобы загрузить объекты в любую конкретную базу данных.

Инфраструктура сборки расширений

Если вы думаете о распространении модулей расширения QHB, то для них может быть довольно сложно настроить переносную систему сборки. Поэтому установка QHB предоставляет инфраструктуру сборки для расширений, которая называется PGXS, так что простые модули расширения могут быть собраны просто на уже установленном сервере. PGXS в основном предназначен для расширений, которые включают в себя код на C, хотя он также может использоваться и для расширений чистого SQL. Обратите внимание, что PGXS не предназначен для того, чтобы быть универсальной структурой системы сборки, которая может использоваться для создания любого программного обеспечения, взаимодействующего с QHB; он просто автоматизирует общие правила сборки для простых модулей расширения сервера. Для более сложных пакетов вам может потребоваться написать собственную систему сборки.

Чтобы использовать инфраструктуру PGXS для вашего расширения, вы должны написать простой make-файл. В make- файле вам нужно установить некоторые переменные и включить глобальный make-файл PGXS. Вот пример, который создает модуль расширения с именем isbn_issn, состоящий из разделяемой библиотеки, содержащей некоторый код C/RUST, файл управления расширениями, сценарий SQL, файл включения (требуется только в том случае, если другим модулям может потребоваться доступ к функциям расширения без прохождения через SQL) и текстовый файл документации:

MODULES = isbn_issn
EXTENSION = isbn_issn
DATA = isbn_issn--1.0.sql
DOCS = README.isbn_issn
HEADERS_isbn_issn = isbn_issn.h

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Последние три строки всегда должны быть одинаковыми. Ранее в файле вы назначаете переменные или добавляете пользовательские правила создания .

Установите одну из этих трех переменных, чтобы указать, что собирается:

MODULES
список объектов разделяемой библиотеки, которые будут построены из исходных файлов с одинаковым основанием (не включайте суффиксы библиотеки в этот список)
MODULE_big
общая библиотека для сборки из нескольких исходных файлов (список объектных файлов в OBJS )
PROGRAM
исполняемая программа для сборки (список объектных файлов в OBJS )

Следующие переменные также могут быть установлены:

EXTENSION
имена расширений; для каждого имени вы должны предоставить файл extension.control, который будет установлен в prefix/share/extension
MODULEDIR
подкаталог prefix/share в который должны быть установлены файлы DATA и DOCS (если не установлен, по умолчанию используется extension если установлено EXTENSION, или contrib если нет)
DATA
случайные файлы для установки в prefix/share/$MODULEDIR
DATA_built
случайные файлы для установки в prefix/share/$MODULEDIR, которые нужно сначала собрать
DATA_TSEARCH
случайные файлы для установки c prefix/share/tsearch_data
DOCS
случайные файлы для установки с prefix/doc/$MODULEDIR
HEADERS
HEADERS_built
Файлы (возможно, для сборки и) устанавливаются с prefix/include/server/$MODULEDIR/$MODULE_big .
В отличие от DATA_built, файлы в HEADERS_built не удаляются целью очистки; если вы хотите, чтобы они были удалены, также добавьте их в EXTRA_CLEAN или добавьте свои собственные правила, чтобы сделать это.
HEADERS_$MODULE
HEADERS_built_$MODULE
Файлы для установки (после сборки, если указано) с prefix/include/server/$MODULEDIR/$MODULE , где $MODULE должно быть именем модуля, используемым в MODULES или MODULE_big.
В отличие от DATA_built, файлы в HEADERS_built_$MODULE не удаляются с целью очистки; если вы хотите, чтобы они были удалены, также добавьте их в EXTRA_CLEAN или добавьте свои собственные правила, чтобы сделать это.
Разрешается использовать обе переменные для одного и того же модуля или любой комбинации, если только в списке модулей нет двух имен модулей, которые отличаются только наличием префикса built_, что может привести к неоднозначности. В этом (надеюсь маловероятном) случае вы должны использовать только переменные HEADERS_built_$MODULE .
SCRIPTS
файлы сценариев (не двоичные файлы) для установки в prefix/bin
SCRIPTS_built
файлы сценариев (не двоичные файлы) для установки в prefix/bin, которые должны быть собраны в первую очередь
REGRESS
список регрессионных тестов (без суффикса), см. ниже
REGRESS_OPTS
дополнительные ключи для перехода на pg_regress
ISOLATION
список тестовых случаев изоляции, см. ниже для более подробной информации
ISOLATION_OPTS
дополнительные ключи для передачи в pg_isolation_regress
TAP_TESTS
переключатель, определяющий, нужно ли запускать тесты TAP, см. ниже
NO_INSTALLCHECK
не определять цель installcheck, полезно, например, если тесты требуют специальной конфигурации или не используют pg_regress
EXTRA_CLEAN
дополнительные файлы для удаления в make clean
PG_CPPFLAGS
будет добавлен к CPPFLAGS
PG_CFLAGS
будет добавлен к CFLAGS
PG_CXXFLAGS
будет добавлен в CXXFLAGS
PG_LDFLAGS
будет добавлен к LDFLAGS
PG_LIBS
будет добавлен в строку ссылки PROGRAM
SHLIB_LINK
будет добавлен в строку ссылки MODULE_big
PG_CONFIG
путь к программе pg_config для сборки установки QHB (обычно просто pg_config для использования первым в вашем PATH)

Поместите этот makefile как Makefile в каталог, содержащий ваше расширение. Затем вы можете выполнить make для компиляции, а затем make install для установки вашего модуля. По умолчанию расширение компилируется и устанавливается для установки QHB, которая соответствует первой программе pg_config найденной в вашем PATH. Вы можете использовать другую установку, установив PG_CONFIG чтобы он указывал на ее программу pg_config, либо внутри makefile, либо в командной строке make.

Вы также можете запустить make в каталоге за пределами исходного дерева вашего расширения, если вы хотите сохранить каталог сборки отдельно. Эта процедура также называется VPATH построить. Вот как:

mkdir build_dir
cd build_dir
make -f /path/to/extension/source/tree/Makefile
make -f /path/to/extension/source/tree/Makefile install

Кроме того, вы можете настроить каталог для сборки VPATH аналогично тому, как это делается для основного кода. Один из способов сделать это - использовать основной скрипт config/prep_buildtree. Как только это будет сделано, вы можете собрать, установив переменную make VPATH следующим образом:

make VPATH=/path/to/extension/source/tree
make VPATH=/path/to/extension/source/tree install

Эта процедура может работать с большим разнообразием макетов каталогов.

Сценарии, перечисленные в переменной REGRESS, используются для регрессионного тестирования вашего модуля, который может быть вызван командой make installcheck после выполнения команды make install. Чтобы это работало, у вас должен быть запущен сервер QHB. Файлы сценариев, перечисленные в REGRESS должны появляться в подкаталоге с именем sql/ в каталоге вашего расширения. Эти файлы должны иметь расширение .sql, которое не должно быть включено в список REGRESS в makefile. Для каждого теста также должен быть файл, содержащий ожидаемый результат в подкаталоге с именем expected/, с тем же основанием и расширением .out. make installcheck выполняет каждый тестовый сценарий с помощью qsql и сравнивает полученный результат с соответствующим ожидаемым файлом. Любые различия будут записаны в файл regression.diffs в формате diff -c. Обратите внимание, что попытка запустить тест, в котором отсутствует ожидаемый файл, будет отображаться как "trouble", поэтому убедитесь, что у вас есть все ожидаемые файлы.

Сценарии, перечисленные в переменной ISOLATION, используются для тестов, в которых подчеркивается поведение одновременной сессии с вашим модулем, которая может быть вызвана командой make installcheck после выполнения команды make install. Чтобы это работало, у вас должен быть запущенный сервер QHB. Файлы сценариев, перечисленные в ISOLATION должны появляться в подкаталоге с именем specs/ в каталоге вашего расширения. Эти файлы должны иметь расширение .spec, которое не должно быть включено в список ISOLATION в makefile. Для каждого теста также должен быть файл, содержащий ожидаемый результат в подкаталоге с именем expected/, с тем же основанием и расширением .out. make installcheck выполняет каждый тестовый сценарий и сравнивает полученный результат с соответствующим ожидаемым файлом. Любые различия будут записаны в файл output_iso/regression.diffs в формате diff -c. Обратите внимание, что попытка запустить тест, в котором отсутствует ожидаемый файл, будет отображаться как "trouble", поэтому убедитесь, что у вас есть все ожидаемые файлы.

TAP_TESTS позволяет использовать тесты TAP. Данные каждого прогона присутствуют в подкаталоге с именем tmp_check/.

Заметка
Самый простой способ создать ожидаемые файлы - создать пустые файлы, а затем выполнить тестовый прогон (который, конечно, сообщит о различиях). Проверьте фактические файлы результатов, найденные в каталоге results/ (для тестов в REGRESS) или в output_iso/results/ (для тестов в ISOLATION), а затем скопируйте их в expected/ если они соответствуют тому, что вы ожидаете от теста.