Расширение SQL

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

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

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

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

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

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

Базовые типы

Базовые типы — это типы вроде integer, которые реализуются ниже уровня языка SQL (обычно на низкоуровневом языке, например C или RUST). В целом, они соответствуют тому, что часто называют абстрактными типами данных. QHB может работать с такими типами только через функции, предоставляемые пользователем, и понимает поведение таких типов только в той степени, в которой пользователь их описывает. Встроенные базовые типы описаны в главе Типы данных.

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

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

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

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

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

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

Домены

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

Псевдотипы

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

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

Особый интерес представляют некоторые псевдотипы, называемые полиморфными типами, которые используются для объявления полиморфных функций. Особенности этих типов позволяют определить одну функцию, которая будет работать со множеством различных типов данных, причем конкретный тип (или типы) определяется из типов данных, фактически переданных ей в конкретном вызове. Полиморфные типы приведены в Таблице 1. Некоторые примеры их использования можно найти в подразделе Полиморфные функции SQL.

Таблица 1. Полиморфные типы

ИмяСемействоОписание
anyelementПростоеУказывает, что функция принимает любой тип данных
anyarrayПростоеУказывает, что функция принимает любой тип массива
anynonarrayПростоеУказывает, что функция принимает любой тип, отличный от массива
anyenumПростоеУказывает, что функция принимает любой перечислимый тип (см. раздел Перечислимые типы)
anyrangeПростоеУказывает, что функция принимает любой диапазонный тип (см. раздел Диапазонные типы)
anymultirangeПростоеУказывает, что функция принимает любой мультидиапазонный тип (см. раздел Диапазонные типы)
anycompatibleОбщееУказывает, что функция принимает любой тип данных, с автоматическим приведением нескольких аргументов к общему типу
anycompatiblearrayОбщееУказывает, что функция принимает любой тип массива, с автоматическим приведением нескольких аргументов к общему типу
anycompatiblenonarrayОбщееУказывает, что функция принимает любой тип, отличный от массива, с автоматическим приведением нескольких аргументов к общему типу
anycompatiblerangeОбщееУказывает, что функция принимает любой диапазонный тип, с автоматическим приведением нескольких аргументов к общему типу
anycompatiblemultirangeОбщееУказывает, что функция принимает любой мультидиапазонный тип, с автоматическим приведением нескольких аргументов к общему типу

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

Для «простого» семейства полиморфных типов правила сочетания и выведения типов действуют следующим образом:

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

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

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

Таким образом, когда несколько аргументов объявлены с полиморфным типом, в конечном счете допускается только одна определенная комбинация фактических типов аргументов. Например, функция, объявленная как equal(anyelement, anyelement), примет в аргументах любые два значения, но только если они принадлежат к одному типу данных.

Если возвращаемое значение функции объявляется как полиморфный тип, должна быть хотя бы одна позиция аргумента, тоже относящаяся к полиморфному типу, а предоставленный в качестве аргумента фактический тип данных определяет фактический тип результата для этого вызова. Например, если бы уже не существовало механизма индексирования массива, можно было бы создать функцию, реализующую индексирование как subscript(anyarray, integer) returns anyelement. Это объявление ограничивает первый фактический аргумент типом массива и позволяет синтаксическому анализатору вывести из него правильный тип результата. Другой пример: функция, объявленная как f(anyarray) returns anyenum будет принимать только массивы перечислимого типа.

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

Обратите внимание, что псевдотипы anynonarray и anyenum представляют не отдельные типовые переменные; они принадлежат к тому же типу, что и anyelement, всего лишь с одним дополнительным ограничением. Например, объявление функции как f(anyelement, anyenum) равнозначно объявлению ее как f(anyenum, anyenum): оба фактических аргумента должны относиться к одному перечислимому типу.

Для «общего» семейства полиморфных типов правила сочетания и выведения типов работают примерно так же, как для «простого» семейства, с единственным главным отличием: фактическим типам аргументов необязательно быть идентичными, если их можно неявно привести к одному общему типу. Этот общий тип выбирается по тем же правилам, что действуют в UNION и связанных конструкциях (см. раздел UNION, CASE и связанные конструкции). При выборе общего типа учитываются фактические типы аргументов anycompatible и anycompatiblenonarray, типы элементов массива аргументов anycompatiblearray, диапазонные подтипы аргументов anycompatiblerange и мультидиапазонные подтипы аргументов anycompatiblemultirange. Если присутствует anycompatiblenonarray, то общий тип должен быть отличным от массива. После определения общего типа аргументы в позициях anycompatible и anycompatiblenonarray автоматически приводятся к этому типу, а аргументы в позициях anycompatiblearray автоматически приводятся к типу массива для этого типа.

Поскольку невозможно выбрать диапазонный тип, зная только его подтип, использование anycompatiblerange и/или anycompatiblemultirange требует, чтобы все аргументы, объявленные с этим типом, имели один фактический диапазонный и/или мультидиапазонный тип, и чтобы этот тип соответствовал выбранному общему типу, поэтому в приведении типов для диапазонных значений нет необходимости. Как и в случае с anyrange и anymultirange, использование anycompatiblerange и anymultirange в качестве типа результата функции требует наличия у нее аргумента типа anycompatiblerange или anycompatiblemultirange.

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

«Простое» и «общее» полиморфные семейства представляют два независимых набора переменных типов. Рассмотрим, например, объявление функции:

CREATE FUNCTION myfunc(a anyelement, b anyelement,
                       c anycompatible, d anycompatible)
RETURNS anycompatible AS ...

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

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

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

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

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

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

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

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

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

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

  • Процедуры определяются командой CREATE PROCEDURE, а не CREATE FUNCTION.

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

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

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

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

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

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

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

Функции SQL выполняют произвольный список операторов SQL, возвращая результат последнего запроса в списке. В простом случае (не с множеством) будет возвращена первая строка результата последнего запроса. (Следует учесть, что понятие «первая строка» в результате из нескольких строк определено точно, только если используется ORDER BY). Если последний запрос вообще не вернет никаких строк, будет возвращен NULL.

Как вариант, можно объявить функцию SQL как возвращающую множество (то есть несколько строк), указав в качестве возвращаемого типа функции SETOF некий_тип или объявив ее с указанием RETURNS TABLE(столбцы). В этом случае будут возвращены все строки результата последнего запроса. Более подробная информация приведена ниже.

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

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

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

SELECT clean_emp();

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

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

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

CALL clean_emp();

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

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

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

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

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

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

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

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

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

INSERT INTO mytable VALUES ($1);

а это не будет работать:

INSERT INTO $1 VALUES (42);

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

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

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

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

SELECT one();

 one
-----
   1

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

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

CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
    SELECT x + y;
$$ LANGUAGE SQL;

SELECT add_em(1, 2) AS answer;

 answer
--------
      3

Как вариант, можно обойтись без имен аргументов и использовать номера:

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;

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

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 в функции SQL не возвращает в точности объявленный этой функцией тип результата, QHB автоматически приведет это значение к требуемому типу, если это возможно с неявным приведением или приведением присваиванием. В противном случае следует написать явное приведение. Например, предположим, что мы захотели, чтобы функция add_em из предыдущего примера вернула тип float8. Для этого достаточно написать

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

поскольку сумма типа integer может быть неявно приведена к типу float8. (Подробную информацию о приведениях см. в главе Преобразование типов или на справочной странице команды CREATE CAST.)

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

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

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

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

Иногда удобно создавать значение составного аргумента на ходу. Это можно сделать с помощью конструкции 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;

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

Обратите внимание на два важных условия в определении функции:

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

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

    ERROR:  return type mismatch in function declared to return emp
DETAIL:  Final statement returns text instead of point at column 4.
-- ОШИБКА: возвращаемый тип не совпадает в функции, объявленной как возвращающая тип emp
-- ДЕТАЛИЗАЦИЯ: Последний оператор возвращает text вместо point в столбце 4.

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

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

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

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

Эту функцию можно вызвать напрямую, указав ее в выражении значения:

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)

Второй способ более подробно описан в подразделе Функции SQL как источники таблиц.

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

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, показанной в подразделе Функции SQL c базовыми типами. Реальная ценность выходных параметров в том, что они предоставляют удобный способ определения функций, возвращающих несколько столбцов. Например,

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

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 являются входными параметрами, но обрабатываются особым образом, описанным ниже.

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

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

CREATE PROCEDURE tp1 (accountno integer, debit numeric, OUT new_balance numeric) AS $$
    UPDATE bank
        SET balance = balance - debit
        WHERE accountno = tp1.accountno
    RETURNING balance;
$$ LANGUAGE SQL;

Для вызова этой процедуры необходимо добавить аргумент, соответствующий параметру OUT. Обычно принято писать NULL:

CALL tp1(17, 100.0, NULL);

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

При вызове процедуры из PL/pgSQL вместо NULL следует написать переменную, которая будет получать вывод процедуры. Подробную информацию см. в подразделе Вызов процедуры.

Функции 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 в вызове также является единственным способом передачи пустого массива в функцию с переменным числом аргументов, например:

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

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

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

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

а эти варианты — нет:

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

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

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

Например:

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
-- ОШИБКА: функция foo() не существует

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

Функции SQL как источники таблиц

Все функции 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 некий_тип, итоговый запрос функции выполняется полностью, и каждая выводимая ей строка возвращается как элемент результирующего множества.

Это свойство обычно используется при вызове функции в предложении 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)

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

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

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 возвращает пустое множество для этих аргументов, поэтому строки результата не генерируются. Такое же поведение мы получили при внутреннем соединении с результатом функции с использованием синтаксиса 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, поэтому такие функции выполняются полностью, прежде чем начинается рассмотрение следующей строки предложения FROM.

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

Функции, возвращающие множества, могут быть вложенными в списке выборки, хотя это не допускается в элементах предложения 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 вообще будет рассмотрено, запрос должен был бы создать по пять копий каждой входной строки. Во избежание путаницы в таких случаях выдается ошибка при синтаксическом анализе запроса.

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

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

Есть еще один способ объявить функцию как возвращающую множество — использовать синтаксис RETURNS TABLE(столбцы). Это равнозначно использованию одного или нескольких параметров OUT с обозначением функции как возвращающей SETOF record (или SETOF тип единственного выходного параметра, если применимо). Эта запись указана в последних версиях стандарта 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;

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

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

Функции SQL могут принимать и возвращать полиморфные типы, описанные в подразделе Полиморфные типы. Вот пример полиморфной функции 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, а массив типов unknown является недопустимым. Без приведения типа вы будете получать ошибки вроде этой:

ERROR:  could not determine polymorphic type because input has type "unknown"
-- ОШИБКА: не удалось определить полиморфный тип, так как входные аргументы имеют тип «unknown»

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

SELECT make_array(1, 2.5) AS numericarray;
ERROR:  function make_array(integer, numeric) does not exist
-- ОШИБКА: функции make_array(integer, numeric) не существует

Альтернативный подход заключается в использовании «общего» семейства полиморфных типов, позволяющего системе попытаться определить подходящий общий тип:

CREATE FUNCTION make_array2(anycompatible, anycompatible)
RETURNS anycompatiblearray AS $$
    SELECT ARRAY[$1, $2];
$$ LANGUAGE SQL;

SELECT make_array2(1, 2.5) AS numericarray;
 numericarray
--------------
 {1,2.5}
(1 row)

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

SELECT make_array2('a', 'b') AS textarray;
 textarray
-----------
 {a,b}
(1 row)

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

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 result of type anyelement requires at least one input of type anyelement, anyarray, anynonarray, anyenum, or anyrange.
-- ОШИБКА: не удалось определить тип данных результата
-- ДЕТАЛИЗАЦИЯ: Для результата типа anyelement требуется хотя бы один аргумент типа anyelement, anyarray, anynonarray, anyenum или anyrange.

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

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 имеет один или несколько параметров сортируемых типов данных, правило сортировки выбирается при каждом вызове функции, в зависимости от правил сортировки, связанных с фактическими аргументами, как описано в разделе Поддержка правил сортировки. Если правило сортировки определено успешно (т. е. отсутствуют конфликты между неявно установленными правилами сортировки аргументов), то все сортируемые параметры неявно воспринимаются как имеющие такое правило. Это повлияет на поведение операций, связанных с сортировкой, внутри функции. Например, для описанной выше функции anyleast результат

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

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

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

Либо, если вы хотите, чтобы функция работала с конкретным правилом сортировки, независимо от того, с каким она была вызвана, вставьте в определение функции нужное количество предложений COLLATE. Эта версия 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;

Но обратите внимание, что применение правила к несортируемому типу данных вызовет ошибку.

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

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

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

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

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

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

то нельзя сразу понять, какая функция вызовется с какими-нибудь простыми входными аргументами вроде test(1, 1.5). Применяемые в текущей версии правила преобразования описаны в главе Преобразование типов, но неразумно разрабатывать систему, которая будет подспудно полагаться на такие зыбкие механизмы.

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

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

При перегрузке функций на нативном языке существует дополнительное ограничение: имя уровня С каждой функции в семействе перегруженный функций должно отличаться от имен уровня С всех остальных функций, как внутренних, так и загружаемых динамически. При нарушении этого правила поведение будет зависеть от платформы. Можно получить ошибку компоновщика во время выполнения, либо будет вызвана одна из функций (обычно внутренняя). Альтернативная форма предложения AS команды SQL CREATE FUNCTION отделяет имя функции SQL от имени в исходном коде на C/RUST. Например:

CREATE FUNCTION test(int) RETURNS int
    AS 'имя_файла', 'test_1arg'
    LANGUAGE C;
CREATE FUNCTION test(int, int) RETURNS int
    AS 'имя_файла', 'test_2arg'
    LANGUAGE C;

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

Категории изменчивости функций

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

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

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

  • Постоянная функция (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 получают новый снимок в начале каждого запроса, который они выполняют.

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

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

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

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

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

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

QHB позволяет писать пользовательские функции на других языках, помимо SQL и C/RUST. Эти языки обычно называются процедурными языками (procedural language, PL). Процедурные языки не встроены в сервер QHB; они реализованы в подгружаемых модулях. Дополнительную информацию см. в главе Процедурные языки и следующих за ней главах.

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

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

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

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

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

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

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

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

В настоящее время для нативных функций применяется только одно соглашение о вызовах, называемое «версия 1» («extern C», все означенные языки имеют возможность следовать такому соглашению). Поддержка этого соглашения (для языка С) обозначается вызовом функции с макросом PG_FUNCTION_INFO_V1(), как показано ниже.

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

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

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

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

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

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

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

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

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

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

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

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

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

PG_MODULE_MAGIC;

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

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

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

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

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

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

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

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

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

/* 4-байтовое целое, передаваемое по значению */
typedef int int4;

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

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

/* 16-байтовая структура, передаваемая по ссылке */
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 "qhb.h"
...
char buffer[40]; /* наши исходные данные */
...
text *destination = (text *) palloc(VARHDRSZ + 40);
SET_VARSIZE(destination, VARHDRSZ + 40);
memcpy(destination->data, buffer, 40);
...

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

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

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

Тип SQLТип CОпределен в
booleanboolqhb.h (возможно, встроенный компилятор)
boxBOX*utils/geo_decls.h
byteabytea*qhb.h
"char"char(встроенный компилятор)
characterBpChar*qhb.h
cidCommandIdqhb.h
dateDateADTutils/date.h
float4 (real)float4*qhb.h
float8 (double precision)float8*qhb.h
int2 (smallint)int16qhb.h
int4 (integer)int32qhb.h
int8 (bigint)int64qhb.h
intervalInterval*datatype/timestamp.h
lsegLSEG*utils/geo_decls.h
nameNameqhb.h
numericNumericutils/numeric.h
oidOidqhb.h
oidvectoroidvector*qhb.h
pathPATH*utils/geo_decls.h
pointPOINT*utils/geo_decls.h
regprocregprocqhb.h
texttext*qhb.h
tidItemPointerstorage/itemptr.h
timeTimeADTutils/date.h
time with time zoneTimeTzADT***utils/date.h ***
timestampTimestamp*datatype/timestamp.h
timestamp with time zoneTimestampTzdatatype/timestamp.h
varcharVarChar*qhb.h
xidTransactionIdqhb.h

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

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

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

Datum funcname(PG_FUNCTION_ARGS)

И дополнительно в том же исходном файле должен присутствовать вызов макроса:

PG_FUNCTION_INFO_V1(funcname);

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

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

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

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

PG_MODULE_MAGIC;

/* по значению */

PG_FUNCTION_INFO_V1(add_one);

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

    PG_RETURN_INT32(arg + 1);
}

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

PG_FUNCTION_INFO_V1(add_one_float8);

Datum
add_one_float8(PG_FUNCTION_ARGS)
{
    /* Макрос для FLOAT8 скрывает свою способность передачи по ссылке. */
    float8   arg = PG_GETARG_FLOAT8(0);

    PG_RETURN_FLOAT8(arg + 1.0);
}

PG_FUNCTION_INFO_V1(makepoint);

Datum
makepoint(PG_FUNCTION_ARGS)
{
    /* Здесь способность Point к передаче по ссылке не скрыта. */
    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);
}

/* по ссылке, переменной длины */

PG_FUNCTION_INFO_V1(copytext);

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

    /*
     * VARSIZE_ANY_EXHDR — это размер структуры в байтах минус VARHDRSZ или VARHDRSZ_SHORT
     * ее заголовочного файла. Постройте копию с заголовочным файлом полной длины.
     */
    text     *new_t = (text *) palloc(VARSIZE_ANY_EXHDR(t) + VARHDRSZ);
    SET_VARSIZE(new_t, VARSIZE_ANY_EXHDR(t) + VARHDRSZ);

    /*
     * VARDATA — это указатель на область данных новой структуры. Источником может быть
     * короткий элемент данных, поэтому извлекайте его данные посредством VARDATA_ANY.
     */
    memcpy((void *) VARDATA(new_t), /* цель */
           (void *) VARDATA_ANY(t), /* источник */
           VARSIZE_ANY_EXHDR(t));   /* сколько байтов */
    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 'КАТАЛОГ/funcs', 'add_one'
     LANGUAGE C STRICT;

-- обратите внимание — это перегрузка имени функции SQL "add_one"
CREATE FUNCTION add_one(double precision) RETURNS double precision
     AS 'КАТАЛОГ/funcs', 'add_one_float8'
     LANGUAGE C STRICT;

CREATE FUNCTION makepoint(point, point) RETURNS point
     AS 'КАТАЛОГ/funcs', 'makepoint'
     LANGUAGE C STRICT;

CREATE FUNCTION copytext(text) RETURNS text
     AS 'КАТАЛОГ/funcs', 'copytext'
     LANGUAGE C STRICT;

CREATE FUNCTION concat_text(text, text) RETURNS text
     AS 'КАТАЛОГ/funcs', 'concat_text'
     LANGUAGE C STRICT;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

FreeBSD

Флаг компилятора для создания PIC — -fPIC. Флаг компилятора для создания разделяемой библиотеки — -shared.

gcc -fPIC -c foo.c
gcc -shared -o foo.so foo.o

Это применимо, начиная с FreeBSD версии 3.0.

HP-UX

Флаг системного компилятора для создания PIC — +z, а при использовании GCC — -fPIC. Флаг компоновщика для разделяемых библиотек — -b. Таким образом, следует написать:

cc +z -c foo.c

или:

gcc -fPIC -c foo.c

а затем:

ld -b -o foo.sl foo.o

В отличие от большинства других систем, HP-UX использует для разделяемых библиотек расширение .sl.

Linux

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

cc -fPIC -c foo.c
cc -shared -o foo.so foo.o

macOS

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

cc -c foo.c
cc -bundle -flat_namespace -undefined suppress -o foo.so foo.o

NetBSD

Флаг компилятора для создания PIC — -fPIC. В системах ELF для компоновки разделяемых библиотек используется компилятор с флагом -shared. В более старых системах без ELF применяется команда ld -Bshareable.

gcc -fPIC -c foo.c
gcc -shared -o foo.so foo.o

OpenBSD

Флаг компилятора для создания PIC — -fPIC. Для компоновки разделяемых библиотек используется команда ld -Bshareable.

gcc -fPIC -c foo.c
ld -Bshareable -o foo.so foo.o

Solaris

Флаг для создания PIC с компилятором Sun — -KPIC, с компилятором GCC — -fPIC. Для компоновки разделяемых библиотек с обоими компиляторами можно использовать флаг -G, а с GCC — флаг -shared.

cc -KPIC -c foo.c
cc -G -o foo.so foo.o

или:

gcc -fPIC -c foo.c
gcc -G -o foo.so foo.o

Совет
Если для вас это слишком сложно, следует рассмотреть возможность использования средства GNU Libtool, которое скрывает различия платформ за единым интерфейсом.

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

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

Аргументы составного типа

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

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

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

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

#include "qhb.h"
#include "executor/executor.h"  /* для 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);
    /* Как вариант, можно записать PG_RETURN_NULL() для значения поля «зарплата», равного NULL. */

    PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
}

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

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

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

CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
    AS 'КАТАЛОГ/funcs', 'c_overpaid'
    LANGUAGE C STRICT;

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

Возврат строк (составных типов)

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

#include "funcapi.h"

Существует два способа создания составного значения данных (далее «кортеж»): его можно построить из массива значений Datum или из массива строк C/RUST, которые можно передать во функции преобразования ввода для типов данных столбца кортежа. В любом случае сначала необходимо получить или создать дескриптор TupleDesc для структуры кортежа. При работе со значениями Datum вы передаете TupleDesc функции BlessTupleDesc, а затем вызываете для каждой строки heap_form_tuple. При работе со строками C/RUST вы передаете 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. (Если это не так, вы можете сообщить об ошибке по образцу: «функция, возвращающая запись, вызвана в контексте, не допускающем запись типа»).

Совет
Из функции 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)

если планируете работать со значениями Datum, или:

AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)

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

При работе с Datum используйте функцию:

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

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

При работе со строками C/RUST используйте функцию:

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

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

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

HeapTupleGetDatum(HeapTuple tuple)

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

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

Возврат множеств

Для функций на нативном языке существует две возможности возврата множеств (нескольких строк). Первый метод, называемый режимом ValuePerCall (значение за вызов), состоит в многократном вызове функции, возвращающей множество (при этом ей каждый раз передаются одни и те же аргументы), и при каждом вызове она возвращает по одной новой строке, пока те не закончатся, о чем функция просигнализирует, возвращая NULL. Таким образом, функция, возвращающая множество (Set-Returning Function, SRF), должна от вызова к вызову сохранять свое состояние в достаточной степени, чтобы помнить, что она делает, и при каждом вызове возвращать надлежащие данные. Другой метод, называемый режимом Materialize (материализация), состоит в том, что SRF заполняет и возвращает объект tuplestore, содержащий сразу все результирующее множество; таким образом, для получения всего результата производится всего один вызов, и никакое состояние между вызовами сохранять не нужно.

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

В оставшейся части данного раздела описывается ряд наиболее часто применяющихся (хотя и необязательных) вспомогательных макросов для SRF, реализующих режим ValuePerCall. Кроме того, в модулях share/extension установки QHB содержится много примеров SRF, реализующих как режим ValuePerCall, так и режим Materialize.

Для использования описанных здесь макросов поддержки ValuePerCall включите funcapi.h. Эти макросы работают со структурой FuncCallContext, содержащей состояние, которое нужно сохранять между вызовами. Внутри вызываемой SRF указатель на FuncCallContext удерживается между вызовами в поле fcinfo->flinfo->fn_extra. Макросы автоматически заполняют данное поле при первом использовании и в при последующих вызовах ожидают обнаружить в нем этот же указатель.

typedef struct FuncCallContext
{
    /*
     * Счетчик числа произведенных ранее вызовов
     *
     * посредством макроса SRF_FIRSTCALL_INIT() call_cntr присваивается начальное
     * значение 0 , которое увеличивается на 1 при каждом вызове SRF_RETURN_NEXT().
     */
    uint64 call_cntr;

    /*
     * (НЕОБЯЗАТЕЛЬНО) максимальное число вызовов
     *
     * max_calls не является обязательным и присутствует здесь только для удобства.
     * Если это значение не задано, вы должны предоставить альтернативный способ
     * определить, когда функция закончила работу.
     */
    uint64 max_calls;

    /*
     * (НЕОБЯЗАТЕЛЬНО) указатель на разнообразную дополнительную информацию,
     * предоставленную пользователем
     *
     * user_fctx используется как указатель на ваши собственные данные, позволяя
     * сохранять произвольную контекстную информацию между вызовами вашей функции.
     */
    void *user_fctx;

    /*
     * (НЕОБЯЗАТЕЛЬНО) указатель на структуру, содержащую метаданные ввода типа
     * атрибута
     *
     * attinmeta применяется при возврате кортежей (т. е. составных типов данных)
     * и не применяется при возврате базовых типов данных. Он нужен, только
     * если вы намерены использовать BuildTupleFromCStrings() для создания
     * возвращаемого кортежа.
     */
    AttInMetadata *attinmeta;

    /*
     * Контекст памяти, используемый для структур, которые должны пережить
     * несколько вызовов
     *
     * multi_call_memory_ctx настраивается при помощи SRF_FIRSTCALL_INIT() и
     * используется SRF_RETURN_DONE() для очистки. Это наиболее подходящий
     * контекст для любых блоков памяти, которые предназначены для многократного
     * использования при повторных вызовах SRF.
     */
    MemoryContext multi_call_memory_ctx;

    /*
     * (НЕОБЯЗАТЕЛЬНО) указатель на структуру, содержащую описание кортежа
     *
     * tuple_desc применяется при возврате кортежей (т. е. составных типов данных)
     * и требуется, только если вы собираетесь формировать кортежи с помощью функции
     * heap_form_tuple(), а не BuildTupleFromCStrings().  Обратите внимание, что
     * сохраняемый здесь указатель TupleDesc обычно должен сначала пройти через
     * вызов BlessTupleDesc().
     */
    TupleDesc tuple_desc;

} FuncCallContext;

Для SRF предоставляется несколько макросов, использующих эту инфраструктуру:

SRF_IS_FIRSTCALL()

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

SRF_FIRSTCALL_INIT()

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

SRF_PERCALL_SETUP()

чтобы подготовиться к применению FuncCallContext.

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

SRF_RETURN_NEXT(funcctx, result)

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

SRF_RETURN_DONE(funcctx)

чтобы провести очистку и завершить работу SRF.

Текущий контекст памяти, в котором вызывается SRF, является временным и будет очищаться между вызовами. Это означает, что вам не нужно вызывать pfree для всех блоков памяти, которые вы выделили с помощью palloc; они все равно будут освобождены. Однако если вы хотите распределить какие-либо структуры данных между вызовами, вам следует поместить их в другое место. Подходящим местом для любых данных, которые должны сохраняться до завершения работы SRF, является контекст памяти, на который ссылается multi_call_memory_ctx. В большинстве случаев это означает, что вы должны переключиться на multi_call_memory_ctx при выполнении настройки первого вызова. Для сохранения указателя на такие долгоживущие структуры данных используйте поле funcctx->user_fctx. (Данные, выделенные в контексте 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;
    другие необходимые объявления

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

        funcctx = SRF_FIRSTCALL_INIT();
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
        /* Здесь содержится код подготовки при первом вызове: */
        пользовательский код
        если возвращается составной тип
            сформировать TupleDesc и, возможно, AttInMetadata
        конец условия возвращаемого составного типа
        пользовательский код
        MemoryContextSwitchTo(oldcontext);
    }

    /* Здесь содержится код подготовки при каждом вызове: */
    пользовательский код
    funcctx = SRF_PERCALL_SETUP();
    пользовательский код

    /* это единственный способ, которым мы можем проверить, последний ли это вызов: */
    if (funcctx->call_cntr < funcctx->max_calls)
    {
        /* Здесь мы возвращаем еще один результат: */
        пользовательский код
        получение результирующих значений Datum
        SRF_RETURN_NEXT(funcctx, result);
    }
    else
    {
        /* Здесь мы прекращаем возвращать результаты, так что просто отражаем этот факт. */
        /* (Воздержаться от искушения вставить сюда код очистки.) */
        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;

    /* действия, производимые только при первом вызове функции */
    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext   oldcontext;

        /* создать контекст функции для сохранения данных между вызовами */
        funcctx = SRF_FIRSTCALL_INIT();

        /* переключить на контекст памяти, подходящий для нескольких вызовов функции */
        oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);

        /* общее число кортежей, подлежащих возврату */
        funcctx->max_calls = PG_GETARG_UINT32(0);

        /* Сформировать дескриптор кортежей для нашего результирующего типа */
        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")));
                            /* "функция, возвращающая запись, вызвана в контексте, который не может принять запись типа" /*

        /*
         * создать метаданные атрибута, которые позже понадобятся для формирования
         * кортежей из исходных строк C/RUST
         */
        attinmeta = TupleDescGetAttInMetadata(tupdesc);
        funcctx->attinmeta = attinmeta;

        MemoryContextSwitchTo(oldcontext);
    }

    /* действия, производимые при каждом вызове функции */
    funcctx = SRF_PERCALL_SETUP();

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

    if (call_cntr < max_calls)    /* выполняется, когда предстоит еще несколько вызовов */
    {
        char       **values;
        HeapTuple    tuple;
        Datum        result;

        /*
         * подготовить массив значений для формирования возвращаемого кортежа.
         * Это должен быть массив строк C/RUST, который
         * позже будет обработан функциями ввода для типов данных.
         */
        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));

        /* сформировать кортеж */
        tuple = BuildTupleFromCStrings(attinmeta, values);

        /* преобразовать кортеж в datum */
        result = HeapTupleGetDatum(tuple);

        /* провести очистку (на самом деле в ней нет необходимости) */
        pfree(values[0]);
        pfree(values[1]);
        pfree(values[2]);
        pfree(values);

        SRF_RETURN_NEXT(funcctx, result);
    }
    else    /* выполняется, когда вызовов больше не осталось */
    {
        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 'имя_файла', '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 'имя_файла', 'retcomposite'
    LANGUAGE C IMMUTABLE STRICT;

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

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

Функции на нативном языке можно объявить как принимающие и возвращающие полиморфные типы, описанные в подразделе Полиморфные типы. Когда аргументы или возвращаемые типы функции определены как полиморфные типы, автор функции не может заранее знать, с каким типом данных она будет вызываться или какой должна возвращать. В fmgr.h предусмотрены две подпрограммы, позволяющие функции C/RUST версии 1 определить фактические типы данных своих аргументов и тип, который ей нужно вернуть. Эти подпрограммы называются get_fn_expr_rettype(FmgrInfo *flinfo) и get_fn_expr_argtype(FmgrInfo *flinfo, int argnum). Они возвращают OID типа результата или аргумента, либо InvalidOid, если информация недоступна. Структура flinfo обычно доступна по ссылке fcinfo->flinfo. Параметр argnum (номер аргумента) задается, начиная с нуля. В качестве альтернативы get_fn_expr_rettype также можно использовать get_call_result_type. Помимо этого, существует функция 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");

    /* получить предоставляемый элемент; будьте осторожны, если это NULL */
    isnull = PG_ARGISNULL(0);
    if (isnull)
        element = (Datum) 0;
    else
        element = PG_GETARG_DATUM(0);

    /* мы имеем дело с одной размерностью */
    ndims = 1;
    /* и одним элементом */
    dims[0] = 1;
    /* и нижняя граница равна 1 */
    lbs[0] = 1;

    /* получить требуемую информацию о типе элемента */
    get_typlenbyvalalign(element_type, &typlen, &typbyval, &typalign);

    /* теперь сформировать массив */
    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 'КАТАЛОГ/funcs', 'make_array'
    LANGUAGE C IMMUTABLE;

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

Разделяемая память и легкие блокировки

Встраиваемые расширения могут резервировать легкие блокировки (LWLock) и область в разделяемой памяти при запуске сервера. Разделяемую библиотеку расширения нужно загрузить заранее, указав ее в параметре shared_preload_libraries. Разделяемая библиотека должна зарегистрировать обработчик shmem_request_hook в своей функции _PG_init. Этот shmem_request_hook может резервировать легкие блокировки или разделяемую память. Разделяемая память резервируется путем вызова:

void RequestAddinShmemSpace(int size)

из вашего shmem_request_hook.

Легкие блокировки резервируются путем вызова:

void RequestNamedLWLockTranche(const char *tranche_name, int num_lwlocks)

из shmem_request_hook. Это обеспечит доступность массива легких блокировок num_lwlocks под именем tranche_name. Для получения указателя на этот массив воспользуйтесь функцией GetNamedLWLockTranche.

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

static mystruct *ptr = NULL;

if (!ptr)
{
        bool    found;

        LWLockAcquire(AddinShmemInitLock, LW_EXCLUSIVE);
        ptr = ShmemInitStruct("my struct name", size, &found);
        if (!found)
        {
             /* инициализировать содержимое области shmem (разделяемой памяти); */
             /* получить все требуемые легкие блокировки с помощью: */
                ptr->locks = GetNamedLWLockTranche("my tranche name");
        }
        LWLockRelease(AddinShmemInitLock);
}

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

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

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

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

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

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

Резюмируя вышесказанное, лучше всего поместить код C++ за стену из функций extern C, которые взаимодействуют с сервером и предотвращают возникновение исключений, утечки памяти и потери стека вызовов.

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

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

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

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

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

supportfn(internal) returns internal

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

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

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

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

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

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

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

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

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

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

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

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. Возможно, в таком случае у нас возникнет желание вернуть вместо этого NULL — по стандарту SQL ожидается, что sum будет вести себя именно так. Мы можем добиться этого, просто опустив фразу initcond, так что начальным значением состояния будет NULL. Обычно это будет означать, что функции sfunc понадобится проверить входное значение состояния на NULL. Но для sum и некоторых других простых агрегатов вроде max и min достаточно вставить в переменную состояния первое входное значение не NULL, а затем начать применять функцию перехода со второго значения не NULL. QHB сделает это автоматически, если значение начального состояния равно NULL и функция перехода помечена как «strict» (т. е. не должна вызываться для входных значений NULL).

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

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

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

Примечание
Для 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 указывает, что функция завершения помимо значения состояния получает дополнительные фиктивные аргументы, соответствующие входным аргументам агрегата. Дополнительный аргумент anynonarray позволяет сделать объявление array_agg_finalfn допустимым.

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

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

SELECT myaggregate(a ORDER BY a, b, c) FROM ...

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

SELECT myaggregate(a, b, c ORDER BY a) FROM ...

Если функция myaggregate является VARIADIC, то оба этих вызова совершенно корректны.

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

Сортирующие агрегаты

Все вышеописанные агрегаты относятся к «нормальным» агрегатам. QHB также поддерживает сортирующие агрегаты, которые имеют два ключевых отличия от нормальных. Во-первых, помимо обычных агрегируемых аргументов, которые вычисляются для каждой входной строки, сортирующий агрегат может принимать «непосредственные» аргументы, которые вычисляются только один раз за всю операцию агрегирования. Во-вторых, синтаксис обычных агрегируемых аргументов задает порядок их сортировки явно. Сортирующий агрегат обычно используется для выполнения вычисления, которое зависит от конкретного порядка строк, например ранг или процентиль, поэтому порядок сортировки является обязательным аспектом каждого вызова. К примеру, встроенное определение функции 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 — это типичный случай.) Кроме того, поскольку сортировку выполняет функция завершения, невозможно продолжать добавление строк, повторно вызывая функцию перехода. Это означает, что функция завершения не может иметь характеристику READ_ONLY; она должна объявляться в CREATE AGGREGATE с характеристикой READ_WRITE или SHAREABLE (если для дополнительных вызовов функции завершения можно использовать уже отсортированное состояние).

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

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

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

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

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

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

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

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

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

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

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

Вспомогательные функции для агрегатов

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

if (AggCheckCallContext(fcinfo, NULL))

Единственная причина такой проверки состоит в том, что в случае положительного результата первый входной аргумент должен быть временным значением состояния, которое можно безопасно изменить на месте, вместо того чтобы размещать новую копию. Пример можно увидеть в функции int8inc(). (Хотя агрегатным функциям перехода всегда разрешено изменять значение перехода на месте, агрегатным функциям завершения в целом следует этого избегать; если они это делают, такое поведение должно быть объявлено при создании агрегата. Более подробную информацию см. на справочной странице команды 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 'имя_файла'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_out(complex)
    RETURNS cstring
    AS 'имя_файла'
    LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_recv(internal)
   RETURNS complex
   AS 'имя_файла'
   LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION complex_send(complex)
   RETURNS bytea
   AS 'имя_файла'
   LANGUAGE C IMMUTABLE STRICT;

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

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

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

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

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

Более подробную информацию см. на справочной странице команды CREATE TYPE.

Особенности TOAST

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

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

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

Примечание
В более старом коде поле vl_len_ часто объявлялось как int32, а не char[4]. Это нормально, пока в структуре есть другие поля с выравниванием как минимум int32. Но при работе с потенциально невыровненными данными такое строго определение использовать опасно; компилятор может воспринять его как право предполагать, что данные действительно выровнены, что приведет к аварийным выключениям ядра в архитектурах, строгих к выравниванию.

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

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

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

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

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

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

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

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

CREATE FUNCTION complex_add(complex, complex)
    RETURNS complex
    AS 'имя_файла', '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. Единственными обязательными элементами в команде CREATE OPERATOR являются предложение function и объявления аргументов. Предложение commutator, показанное в примере, служит дополнительной подсказкой оптимизатору запросов. Более подробно о предложении commutator и других подсказках для оптимизатора рассказывается в следующем разделе.

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

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

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

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

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

column OP constant

для текущего оператора и определенного значения константы. Это помогает оптимизатору, давая ему некоторое представление о том, сколько строк будет исключено предложениями WHERE такого вида. (А что произойдет, если константа будет слева, спросите вы? Ну, как раз для таких случаев и предназначен COMMUTATOR...)

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

eqsel для =
neqsel для <>
scalarltsel для <
scalarlesel для <=
scalargtsel для >
scalargesel для >=

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

Для сравнения типов данных, которые можно каким-либо разумным способом преобразовать в числовые скалярные значения для сравнения диапазонов, можно использовать функции scalarltsel, scalarlesel, scalargtsel иscalargesel. По возможности, добавьте свой тип данных в число типов, которые понимает функция convert_to_scalar() в src/backend/utils/adt/selfuncs.c. (В конечном итоге эту функцию следует заменить функциями для каждого типа данных, указанного в столбце системного каталога pg_type, но пока этого не произошло.) Если вы этого не сделаете, все будет работать, но оценки оптимизатора будут не так хороши, как могли бы быть.

Еще одна полезная встроенная функция оценки избирательности — matchingsel, которая будет работать практически со всеми бинарными операторами, если для их входных типов данных собирается стандартная статистика MCV и/или строится гистограмма. Ее оценка по умолчанию в два раза больше оценки по умолчанию, выдаваемой eqsel, благодаря чему эта функция лучше всего подходит для операторов сравнения, несколько менее строгих, чем оператор равенства. (Либо можно вызвать нижележащую функцию generic_restriction_selectivity, предоставив ей другую оценку по умолчанию.)

JOIN

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

ON table1.column1 OP table2.column2

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

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

eqjoinsel для =
neqjoinsel для <>
scalarltjoinsel для <
scalarlejoinsel для <=
scalargtjoinsel для >
scalargejoinsel для >=
matchingjoinsel для типовых операторов сопоставления
areajoinsel для сравнений областей в плоскости
positionjoinsel для сравнений позиций в плоскости
contjoinsel для сравнений включения в плоскости

HASHES

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

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

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

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

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

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

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

MERGES

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 3. Стратегии В-дерева

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

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

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

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

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

Таблица 5. Стратегии двумерного R-дерева GiST

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

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

Таблица 6. Стратегии SP-GiST для точек

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

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

Таблица 7. Стратегии GIN для массивов

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

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

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

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

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

Вспомогательные процедуры индексного метода

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

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

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

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

Таблица 9. Вспомогательные функции B-деревьев

ФункцияНомер функции
Сравнивает два ключа и возвращает целое число меньше нуля, ноль или целое число больше нуля, показывающее, что первый ключ меньше, равен или больше второго1
Возвращает адреса вызываемых из C/RUST вспомогательных функций сортировки (необязательная)2
Сравнивает значение теста с базовым значением плюс/минус смещение и возвращает true или false в зависимости от результата сравнения (необязательная)3
Определяет, безопасно ли для индексов, использующих этот класс операторов, применять оптимизацию с дедупликацией, реализованную в btree (необязательная)4
Определяет параметры, специфичные для этого класса операторов (необязательная)5

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

Таблица 10. Вспомогательные функции хеша

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

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

Таблица 11. Вспомогательные функции GiST

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

Для индексов SP-GiST имеется шесть вспомогательных функций, одна из которых является необязательной, как показано в Таблице 12. (Дополнительную информацию см. в главе Индексы SP-GiST.)

Таблица 12. Вспомогательные функции SP-GiST

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

Индексы GIN имеют семь вспомогательных функций, четыре из которых являются необязательными, как показано в Таблице 13. (Дополнительную информацию см. в главе Индексы GIN.)

Таблица 13. Вспомогательные функции GIN

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

Индексы BRIN имеют пять основных вспомогательных функций, одна из которых является необязательной, как показано в Таблице 14; для некоторых версий этих основных функций может потребоваться предоставить дополнительные вспомогательные функции. (Дополнительную информацию см. в разделе Расширяемость.)

Таблица 14. Вспомогательные функции BRIN

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

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

Пример

Теперь, ознакомившись с основными идеями, можно перейти к обещанному примеру создания нового класса операторов. Класс операторов включает операторы, которые сортируют комплексные числа в порядке их абсолютных значений, поэтому мы выбираем для него имя 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 'имя_файла', '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/RUST может работать только с одной глобальной функцией, имеющей данное имя. Поэтому не следует давать функции на C/RUST какое-то простое имя вроде abs_eq. Обычно во избежание конфликтов с функциями для других типов данных рекомендуется включать в имя функции на C/RUST имя типа данных.

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

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

CREATE FUNCTION complex_abs_cmp(complex, complex)
    RETURNS integer
    AS 'имя_файла'
    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
  -- стандартные сравнения int8
  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
  -- стандартные сравнения int4
  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
  -- стандартные сравнения int2
  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
  -- межтиповые сравнения int8 и 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),

  -- межтиповые сравнения int8 и 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),

  -- межтиповые сравнения int4 и 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),

  -- межтиповые сравнения int4 и 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),

  -- межтиповые сравнения int2 и 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),

  -- межтиповые сравнения int2 и 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),

  -- межтиповые функции in_range
  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-дерева по умолчанию для этого типа. Если таковой отсутствует, но есть класс операторов хеширования по умолчанию, то поддерживается равенство массивов, но не упорядочивание.

Еще одна особенность SQL, которая требует даже больших знаний о типе данных, — это режим определения рамки RANGE смещение 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, в которой объединены сложение и вычитание, подходящие для порядка сортировки. Он даже способен предоставить более одной такой функции, в случае если в качестве смещения в предложениях RANGE имеет смысл использовать несколько разных типов данных. Если в классе операторов B-деревьев, связанном с указанным для окна предложением ORDER BY, нет соответствующей вспомогательной функции in_range, режим RANGE смещение PRECEDING FOLLOWING не поддерживается.

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

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

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

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

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

В то время как операторы поиска должны возвращать логические результаты, операторы упорядочивания обычно возвращают какой-то другой тип, например float или numeric для расстояний. Этот тип обычно не совпадает с типом индексируемых данных. Чтобы избежать жестко зафиксированных в коде предположений о поведении различных типов данных, при определении оператора упорядочивания необходимо указать семейство операторов 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, которое проверяет пересечение между непрямоугольными объектами, такими как многоугольники. Тем не менее этот индекс можно применить для поиска объектов, у которых ограничивающий прямоугольник пересекает ограничивающий прямоугольник целевого объекта, а затем выполнить точную проверку пересечения только для объектов, найденных по индексу. Если этот сценарий применим, такой индекс считается «неполным» для оператора. Поиск по неполному индексу реализуется с помощью индексного метода, возвращающего флаг recheck (перепроверить), когда строка действительно может или не может удовлетворять условию запроса. Затем базовая система проверит извлеченную строку по исходному условию запроса, чтобы понять, следует ли возвращать ее как действительного соответствующую ему. Этот подход работает, если индекс гарантированно возвращает все необходимые строки плюс, возможно, некоторые дополнительные строки, которые можно отбросить, вызвав исходный оператор. Индексные методы, поддерживающие неполный поиск (в настоящее время это 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 должны заниматься вспомогательные процедуры compress и decompress. В GIN тип STORAGE определяет тип значений «ключа», который обычно отличается от типа индексируемого столбца — например, в классе операторов для столбцов с целочисленным массивом ключами могут быть просто целые числа. В GIN за извлечение ключей из индексированных значений отвечают вспомогательные функции extractValue и extractQuery. BRIN аналогичен GIN: тип STORAGE определяет тип хранимых обобщенных значений, а вспомогательные процедуры классов операторов отвечают за их правильную интерпретацию.

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

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

При загрузке группы «слабосвязанных» объектов в вашу базу данных основное преимущество расширений перед обычным скриптом SQL заключается в том, что в первом случае QHB будет понимать, что объекты расширения связаны между собой. Все эти объекты можно удалить одной командой DROP EXTENSION (нет необходимости разрабатывать отдельный скрипт «uninstall» (деинсталляции)). Еще полезнее то, что утилита 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. Ожидается, что они будут установлены после создания расширения. В дампы, созданные qhb_dump, будут включены все политики RLS и метки безопасности на объектах расширения.

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

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

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

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

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

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

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

directory (string)
Каталог, содержащий скрипт-файл(ы) SQL расширения. Если не задан абсолютный путь, имя относится к серверному каталогу SHAREDIR. Поведение по умолчанию равнозначно указанию directory = 'extension'.

default_version (string)
Версия расширения по умолчанию (та, которая будет установлена, если в CREATE EXTENSION не указана никакая версия). Хотя этот параметр можно опустить, но в отсутствие указания VERSION это приведет к сбою CREATE EXTENSION, поэтому обычно так делать не рекомендуется.

comment (string)
Комментарий (любая строка) о расширении. Комментарий применяется при изначальном создании расширения, но не при его обновлении (поскольку при этом могут замениться комментарии, добавленные пользователем). Кроме того, комментарий расширения можно задать, написав команду COMMENT в скрипт-файле.

encoding (string)
Кодировка набора символов, используемая в скрипт-файлах. Этот параметр следует указывать, если скрипт-файлы содержат символы, не относящиеся к ASCII. В противном случае предполагается, что в этих файлах используется кодировка базы данных.

module_pathname (string)
Значение этого параметра будет подставляться вместо каждого вхождения MODULE_PATHNAME в скрипт-файлах. Если этот параметр не задан, замена не производится. Как правило, для этого параметра устанавливается значение $libdir/имя_разделяемой_библиотеки, а затем в командах CREATE FUNCTION для функций на нативном языке используется MODULE_PATHNAME, поэтому скрипт- файлам не нужно жестко задавать имя разделяемой библиотеки.

requires (string)
Список имен расширений, от которых зависит это расширение, например requires = 'foo, bar'. Эти расширения должны быть установлены до того, как можно будет установить это расширение.

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

trusted (boolean)
Если этот параметр имеет значение true (это не значение по умолчанию), это позволяет некоторым обычными пользователям устанавливать расширение, для которого параметр superuser равен true. В частности, установка будет разрешена любому с правом CREATE в текущей базе данных. Когда пользователь, выполняющий CREATE EXTENSION, не является суперпользователем, но ему разрешена установка данного расширения посредством этого параметра, то скрипт установки или обновления запускается от имени инициализирующего суперпользователя, а не вызывающего пользователя. Этот параметр не имеет смысла, если superuser равен false. В целом, в этом параметре нельзя устанавливать true для расширений, которые могут открыть доступ к возможностям, иначе доступным только суперпользователям, например к файловой системе. Кроме того, если расширение помечается как доверенное, написание для него безопасных скриптов установки и обновления требует значительных дополнительных усилий; см. подраздел Примечания о безопасности расширений.

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

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

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

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

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

Если скрипт расширения содержит строку @extowner@, эта строка заменяется именем (при необходимости заключенным в кавычки) пользователя, выполняющего команду CREATE EXTENSION or ALTER 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 автоматически добавляется схема pg_temp.

Хотя неперемещаемое расширение может содержать объекты, распределенные по нескольким схемам, обычно желательно поместить все объекты, предназначенные для внешнего использования, в одну схему, которая считается целевой схемой расширения. Такое расположение удачно сочетается со значением 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, которые определяют объекты расширения. Это делается путем назначения имени или номера каждой выпущенной версии скрипта установки расширения. Кроме того, если вы хотите, чтобы пользователи могли динамически обновлять свои базы данных от одной версии до другой, нужно предоставить скрипты обновления, которые вносят необходимые изменения для перехода с одной версии на другую. Скриптам обновления назначаются имена вида расширение--старая_версия--целевая_версия.sql (например, foo--1.0--1.1.sql содержит команды для изменения версии расширения foo с 1.0 на 1.1).

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

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

Чтобы достигнуть запрошенной версии, команда 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('имя_расширения');

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

Установка расширений с помощью скриптов обновления

Расширение, существующее уже некоторое время, вероятно, будет иметь несколько версий, для которых автору потребуется написать скрипт обновления. Например, если вы выпустили расширение foo в версиях 1.0, 1.1 и 1.2, для них понадобятся скрипты обновления foo--1.0--1.1.sql и foo--1.1--.2.sql. Прежде нужно было также создавать новые скрипт-файлы foo--.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 до другой.

Примечания о безопасности расширений

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

Расширение, у которого свойство superuser имеет значение true, также должно быть защищено от угроз безопасности, связанных с действиями, которые происходят в скриптах установки и обновления этого расширения. Злонамеренному пользователю не составит особого труда создать объекты типа троянского коня, которые впоследствии скомпрометируют выполнение небрежно написанного скрипта, позволив этому пользователю получить права суперпользователя.

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

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

Примечания о безопасности функций в расширениях

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

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

Если вы не можете настроить search_path так, чтобы он содержал только безопасные схемы, считайте, что каждое неполное имя может быть преобразовано в объект, определенный злонамеренным пользователем. Избегайте конструкций, явно зависящих от search_path; например, IN и CASE выражение WHEN всегда выбирают оператор по пути поиска. Вместо них используйте OPERATOR(схема.=) ANY и CASE WHEN выражение.

Обычно расширение общего назначения не должно рассчитывать на то, что оно будет установлено в безопасной схеме, что означает, что даже уточненные схемой ссылки на его собственные объекты не вполне безопасны. Например, если в расширении есть функция myschema.myfunc(bigint), то вызовы вроде myschema.myfunc(42) могут быть перехвачены вредоносной функцией myschema.myfunc(integer). Позаботьтесь о том, чтобы типы данных функции и параметров оператора точно соответствовали объявленным типам их аргументов, и при необходимости используйте явные приведения.

Примечания о безопасности скриптов в расширениях

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

Команды DDL, например CREATE FUNCTION и CREATE OPERATOR CLASS, в целом безопасны, но избегайте любых команд, содержащих расширения общего назначения. Например, требуют проверки команда CREATE VIEW, а также выражение DEFAULT в команде CREATE FUNCTION.

Иногда скрипту расширения может понадобиться выполнить SQL общего назначения, например, чтобы внести в каталог изменения, невозможные через DDL. Обязательно выполняйте такие команды с безопасным search_path; не полагайтесь на защищенность пути, установленного при выполнении CREATE/ALTER EXTENSION. Наилучшим выходом будет временно задать в search_path значение 'pg_catalog, pg_temp' и везде, где потребуется, добавить явные указания схемы, в которую устанавливается расширение. (Этот прием также может быть полезен при создании представлений.) Примеры можно найти в модулях share/extension в дистрибутиве исходного кода QHB.

Перекрестные ссылки между расширениями крайне сложно полностью обезопасить, отчасти из-за отсутствия уверенности, в какой схеме находится другое расширения. Риски уменьшаются, если оба расширения устанавливаются в одну схему, так как при этом вредоносный объект не сможет оказаться перед целевым расширением в используемом во время установки search_path. Однако в настоящее время нет механизма, который бы это требовал. На данным момент рекомендуется не помечать расширение как доверенное, если оно зависит от других расширений, за исключением тех, которые уже установлены в схему pg_catalog.

Пример расширения

Вот полный пример расширения, созданного исключительно средствами SQL, составного типа с двумя элементами, который может хранить в своих слотах, названных «k» и «v», значения любого типа. Для хранения все нетекстовые значения автоматически преобразуются в текст.

Скриптовый файл pair--1.0.sql выглядит так:

-- выразит недовольство, если скрипт получен из psql, а не посредством команды CREATE EXTENSION
\echo Use "CREATE EXTENSION pair" to load this file. \quit
-- (Используйте "CREATE EXTENSION pair", чтобы загрузить этот файл )

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" легче корректировать, но полные имена работают лучше.
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'
# cannot be relocatable because of use of @extschema@
/* не может быть перемещаемым, так как использует @extschema@ */
relocatable = false

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

EXTENSION = pair
DATA = pair--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Этот сборочный файл опирается на инфраструктуру PGXS, которая описывается в следующем подразделе. Команда make install установит управляющие файлы и скрипт-файлы в правильный каталог, который определит pg_config.

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

Инфраструктура сборки расширений

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

Чтобы использовать инфраструктуру PGXS для вашего расширения, следует написать простой сборочный файл. В нем нужно установить некоторые переменные и включить глобальный сборочный файл PGXS. Вот пример, который создает модуль расширения с именем isbn_issn, состоящий из разделяемой библиотеки, содержащей некоторый код на C, управляющего файла расширения, скрипта 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
имена расширений; для каждого имени нужно предоставить файл расширение.control, который будет установлен в префикс/share/extension

MODULEDIR
подкаталог в каталоге префикс/share в который должны быть установлены файлы DATA и DOCS (если не задан, по умолчанию используется extension, если установлена переменная EXTENSION, или contrib, если нет)

DATA
произвольные файлы для установки в префикс/share/$MODULEDIR

DATA_built
произвольные файлы для установки в префикс/share/$MODULEDIR, которые сначала нужно собрать

DATA_TSEARCH
произвольные файлы для установки в префикс/share/tsearch_data

DOCS
произвольные файлы для установки в префикс/doc/$MODULEDIR

HEADERS
HEADERS_built
Файлы для установки (и, возможно, сборки) в префикс/include/server/$MODULEDIR/$MODULE_big.
В отличие от файлов DATA_built, файлы в HEADERS_built не удаляются целью clean; если хотите их удалить, добавьте их в EXTRA_CLEAN или напишите собственные правила для этого.

HEADERS_$MODULE
HEADERS_built_$MODULE
Файлы для установки (после сборки, если указано) в префикс/include/server/$MODULEDIR/$MODULE, где $MODULE должно быть именем модуля, используемым в MODULES или MODULE_big.
В отличие от файлов DATA_built, файлы в HEADERS_built_$MODULE не удаляются целью clean; если вы хотите их удалить, добавьте их в EXTRA_CLEAN или напишите собственные правила для этого.
Допускается использовать для одного модуля обе переменные или любую их комбинацию, если только в списке MODULES нет двух имен модулей, которые отличаются только наличием префикса built_, что может привести к неоднозначности. В этом (будем надеяться, маловероятном) случае следует использовать только переменные HEADERS_built_$MODULE.

SCRIPTS
скрипт-файлы (не двоичные файлы) для установки в префикс/bin

SCRIPTS_built
скрипт-файлы (не двоичные файлы) для установки в префикс/bin, которые сначала нужно собрать

REGRESS
список регрессионных тестов (без суффикса), см. ниже

REGRESS_OPTS
дополнительные параметры для передачи pg_regress

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

ISOLATION_OPTS
дополнительные параметры для передачи pg_isolation_regress

TAP_TESTS
флаг, определяющий, нужно ли запускать тесты TAP; см. ниже

NO_INSTALL
не определять цель install; полезна для модулей тестирования, для которых не требуется установка результатов сборки

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 в каталог, содержащий ваше расширение. Затем можно выполнить make для компиляции, а потом make install для установки вашего модуля. По умолчанию расширение компилируется и устанавливается для той установки QHB, которая соответствует первой программе pg_config, найденной при поиске по пути в вашем PATH. Можно воспользоваться и другой установкой, настроив PG_CONFIG так, чтобы тот указывал на ее программу pg_config, либо в сборочном файле, либо в командной строке 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. После этого можно выполнить сборку, установив переменную VPATH для make следующим образом:

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 в сборочном файле. Для каждого теста также должен создаваться файл с ожидаемым выводом в подкаталоге expected/, с тем же основанием и расширением .out. Команда make installcheck выполняет каждый тестовый скрипт с помощью psql и сравнивает полученный вывод с соответствующим ожидаемым файлом. Все обнаруженные различия будут записаны в файл regression.diffs в формате diff -c. Обратите внимание, что при попытке запустить тест, в котором отсутствует ожидаемый файл, этот тест будет отображаться как «проблемный», поэтому убедитесь, что у вас есть все ожидаемые файлы.

Скрипты, перечисленные в переменной ISOLATION, используются для нагрузочного тестирования работы модуля при проведении параллельного сеанса, и вызвать их можно командой make installcheck после выполнения команды make install. Чтобы запустить тестирование, необходим работающий сервер QHB. Скрипт-файлы, перечисленные в ISOLATION, должны находиться в подкаталоге specs/ в каталоге вашего расширения. Эти файлы должны иметь расширение .spec, которое нельзя включать в список ISOLATION в сборочном файле. Для каждого теста также должен быть файл с ожидаемым выводом в подкаталоге expected/, с тем же основанием и расширением .out. Команда make installcheck выполняет каждый тестовый скрипт и сравнивает полученный вывод с соответствующим ожидаемым файлом. Все обнаруженные различия будут записаны в файл output_iso/regression.diffs в формате diff -c. Обратите внимание, что при попытке запустить тест, в котором отсутствует ожидаемый файл, этот тест будет отображаться как «проблемный», поэтому убедитесь, что у вас есть все ожидаемые файлы.

Совет
Самый простой способ создать ожидаемые файлы — создать пустые файлы, а затем выполнить тестовый прогон (который, конечно, выявит различиях). Проверьте фактические файлы результатов, сохраненные в каталоге results/ (для тестов REGRESS) или каталоге output_iso/results/ (для тестов ISOLATION), а затем, если они соответствуют вашим ожиданиям от теста, скопируйте их в expected/.

Copyright © KVANTOM LLC, 2019-2024