Расширение SQL

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

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

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

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

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

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

Базовые типы

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

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

Типы контейнеров

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

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

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

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

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

Домены

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

Псевдо-типы

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

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

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

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

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

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

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

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

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

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

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

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

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

В этой главе может быть полезно просмотреть справочную страницу команды CREATE FUNCTION, чтобы лучше понять примеры. Некоторые примеры из этой главы можно найти в funcs.sql и funcs.c в funcs.c src/tutorial в исходном дистрибутиве QHB .

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

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

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

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

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

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

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

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

Любая коллекция команд на языке SQL может быть упакована вместе и определена как функция. Помимо запросов SELECT, команды могут включать запросы на изменение данных ( INSERT, UPDATE и DELETE ), а также другие команды SQL. (Вы не можете использовать команды управления транзакциями, например COMMIT, SAVEPOINT, и некоторые служебные команды, например, VACUUM, в функциях SQL). Однако последняя команда должна быть командой 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
-----------

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

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

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

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

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

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

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

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

INSERT INTO mytable VALUES ($1);

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

INSERT INTO $1 VALUES (42);

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

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

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

-- Alternative syntax for string literal:
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;

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

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;

Функция SQL должна возвращать точно объявленный тип результата. Это может потребовать вставки явного приведения. Например, предположим, что мы хотели, чтобы предыдущая функция add_em возвращала тип float8. Это не сработает:

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

хотя в других контекстах QHB хотел бы вставить неявное приведение для преобразования integer в float8. Нам нужно написать это как

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

Функции 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 использует table_name.* Чтобы выбрать всю текущую строку таблицы в качестве составного значения. На строку таблицы можно альтернативно ссылаться, используя только имя таблицы, например так:

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:  function declared to return emp returns varchar instead of text at column 1

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

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

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

Здесь мы написали 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)

Второй способ более подробно описан в разделе 6.5.7.

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

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

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 с переменным числом аргументов

Функции 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]); -- doesn't work

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

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

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

Это предотвращает расширение параметра функции variadic в его тип элемента, тем самым позволяя массиву значений аргумента обрабатываться корректно. 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();  -- fails since there is no default for the first argument
ERROR:  function foo() does not exist

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

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

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

SELECT * FROM getfoo(1) AS t1;

Тогда мы получим:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заметка
До PostgreSQL 10 размещение более одной возвращающей множество функции в одном и том же списке выбора не велось очень разумно, если только они не всегда создавали одинаковое количество строк. В противном случае вы получили число выходных строк, равное наименьшему общему кратному количеству строк, созданных функциями, возвращающими множество. Кроме того, вложенные функции, возвращающие множество, не работали, как описано выше; вместо этого функция, возвращающая множество, может иметь не более одного аргумента, возвращающего множество, и каждое гнездо функций, возвращающих множество, выполнялось независимо. Также ранее было разрешено условное выполнение (функции, возвращающие множество внутри CASE т. д.), Что еще больше усложняло ситуацию. Использование синтаксиса LATERAL рекомендуется при написании запросов, которые должны работать в более старых версиях PostgreSQL, потому что это даст согласованные результаты для разных версий. Если у вас есть запрос, основанный на условном выполнении функции, возвращающей множество, вы можете исправить ее, переместив условный тест в пользовательскую функцию, возвращающую множество. Например,

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

could become

CREATE FUNCTION case_generate_series(cond bool, start int, fin int, els int)
  RETURNS SETOF int AS $$
BEGIN
  IF cond THEN
    RETURN QUERY SELECT generate_series(start, fin);
  ELSE
    RETURN QUERY SELECT els;
  END IF;
END$$ LANGUAGE plpgsql;

SELECT x, case_generate_series(y > 0, 1, z, 5) FROM tab;

Эта формулировка будет работать одинаково во всех версиях QHB.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Функции SQL с сопоставлениями

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

Функция, которая принимает один аргумент составного типа, обычно не должна иметь того же имени, что и любой attribute (field) этого типа. Напомним, что attribute(table) считается эквивалентным table.attribute. В случае неоднозначности между функцией составного типа и атрибутом составного типа, всегда будет использоваться атрибут. Можно отменить этот выбор, уточняя имя функции (то есть, schema.func(table) ), но лучше избежать проблемы, не выбирая конфликтующие имена.

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

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

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

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

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

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

  • Функция 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 (см. Главу 13 ): функции STABLE и IMMUTABLE используют моментальный снимок, созданный в начале вызывающего запроса, тогда как функции VOLATILE получают новый снимок в начале каждого выполняемого ими запроса.

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

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

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

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

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

Процедурные языковые функции

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

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

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

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

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

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

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

Функции языка C/RUST

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

PG_MODULE_MAGIC;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Datum funcname(PG_FUNCTION_ARGS)

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

PG_FUNCTION_INFO_V1(funcname);

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

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

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

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

PG_MODULE_MAGIC;

/* by value */

PG_FUNCTION_INFO_V1(add_one);

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

    PG_RETURN_INT32(arg + 1);
}

/* by reference, fixed length */

PG_FUNCTION_INFO_V1(add_one_float8);

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

    PG_RETURN_FLOAT8(arg + 1.0);
}

PG_FUNCTION_INFO_V1(makepoint);

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

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

    PG_RETURN_POINT_P(new_point);
}

/* by reference, variable length */

PG_FUNCTION_INFO_V1(copytext);

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

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

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

PG_FUNCTION_INFO_V1(concat_text);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Linux

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

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

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

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

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

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

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

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

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

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(c_overpaid);

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

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

    PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
}

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

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

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

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

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

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

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

#include "funcapi.h"

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

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

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

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

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

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

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

TupleDesc RelationNameGetTupleDesc(const char *relname)

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

TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)

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

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

TupleDesc BlessTupleDesc(TupleDesc tupdesc)

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

AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)

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

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

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

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

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

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

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

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

HeapTupleGetDatum(HeapTuple tuple)

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

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

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

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

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

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

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

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

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

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

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

} FuncCallContext;

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

SRF_IS_FIRSTCALL()

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

SRF_FIRSTCALL_INIT()

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

SRF_PERCALL_SETUP()

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

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

SRF_RETURN_NEXT(funcctx, result)

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

SRF_RETURN_DONE(funcctx)

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

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

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

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

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

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

    if (SRF_IS_FIRSTCALL())
    {
        MemoryContext oldcontext;

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

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

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

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

PG_FUNCTION_INFO_V1(retcomposite);

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

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

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

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

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

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

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

        MemoryContextSwitchTo(oldcontext);
    }

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    PG_RETURN_ARRAYTYPE_P(result);
}

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

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

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

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

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

void RequestAddinShmemSpace(int size)

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

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

void RequestNamedLWLockTranche(const char *tranche_name, int num_lwlocks)

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

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

static mystruct *ptr = NULL;

if (!ptr)
{
        bool    found;

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

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

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

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

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

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

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

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

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

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

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

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

Функция поддержки планировщика должна иметь подпись SQL

supportfn(internal) returns internal

Он присоединяется к своей целевой функции путем указания предложения SUPPORT при создании целевой функции.

Подробную информацию об API для функций поддержки планировщика можно найти в файле src/include/nodes/supportnodes.h в исходном коде QHB. Здесь мы даем только обзор того, что могут делать функции поддержки планировщика. Набор возможных запросов к функции поддержки является расширяемым, поэтому в будущих версиях может быть больше возможностей.

Некоторые вызовы функций могут быть упрощены во время планирования на основе свойств, специфичных для этой функции. Например, 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 - стандарт SQL ожидает, что sum будет вести себя таким образом. Мы можем сделать это, просто опуская фразу initcond, чтобы начальное значение состояния было нулевым. Обычно это будет означать, что sfunc должен будет проверить наличие нулевого значения состояния. Но для sum и некоторых других простых агрегатов, таких как max и min, достаточно вставить первое ненулевое входное значение в переменную состояния и затем начать применять функцию перехода со второго ненулевого входного значения. QHB сделает это автоматически, если значение начального состояния равно нулю, а функция перехода помечена как «строгая» (т. е. не должна вызываться для 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 в качестве нового значения состояния. Если функция обратного перехода возвращает ноль, это принимается как указание на то, что обратная функция не может сторнировать вычисление состояния для этого конкретного входа, и поэтому совокупный расчет будет переделан с нуля для текущей начальной позиции кадра. Это соглашение позволяет использовать режим движущегося агрегата в ситуациях, когда есть редкие случаи, которые нецелесообразно возвращать из значения рабочего состояния. Функция обратного перехода в этих случаях может «сдвинуть с места», и все же выйти вперед, пока она может работать в большинстве случаев. В качестве примера, агрегат, работающий с числами с плавающей запятой, может выбрать вытеснение, когда вход 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будет отклонять на том основании, что тип результата не может быть выведен из вызова. Но иногда неудобно использовать тип полиморфного состояния. Наиболее распространенным случаем является то, что агрегатные вспомогательные функции должны быть написаны на C/RUST, а тип состояния должен быть объявлен как internal поскольку для него нет эквивалента на уровне SQL. Для решения этого случая можно объявить конечную функцию как принимающую дополнительные «фиктивные» аргументы, которые соответствуют входным аргументам агрегата. Такие фиктивные аргументы всегда передаются как нулевые значения, так как при вызове конечной функции нет конкретного значения. Их единственное использование состоит в том, чтобы позволить типу результата полиморфной конечной функции быть подключенным к входному типу(ам) агрегата. Например, определение встроенного агрегата 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 указывает, что функция final получает в дополнение к значению состояния дополнительные фиктивные аргументы, соответствующие входным аргументам агрегата. Дополнительный аргумент anynonarray позволяет объявить array_agg_finalfn действительным.

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

Заметка
Агрегаты 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 или другом языке PL, агрегаты с упорядоченным набором обычно должны записываться на C/RUST, поскольку их значения состояния не могут быть определены как какой-либо тип данных SQL. (В приведенном выше примере обратите внимание, что значение состояния объявлено как тип internal - это типично). Кроме того, поскольку последняя функция выполняет сортировку, невозможно продолжить добавление входных строк, выполнив функцию перехода позже. Это означает, что конечная функция не READ_ONLY; он должен быть объявлен в CREATE AGGREGATE как READ_WRITE или как SHAREABLE если для дополнительных вызовов конечной функции возможно использование уже отсортированного состояния.

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

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

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

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

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

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

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

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

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

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

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

Функции поддержки для агрегатов

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

if (AggCheckCallContext(fcinfo, NULL))

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

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

Другая подпрограмма поддержки, доступная для агрегатных функций, написанных на C/RUST, - это AggGetAggref, который возвращает узел разбора Aggref который определяет агрегированный вызов. Это в основном полезно для агрегатов с упорядоченным набором, которые могут проверить подструктуру узла Aggref чтобы выяснить, какой порядок сортировки они должны реализовать. Примеры можно найти в orderedsetaggs.c в исходном коде QHB.

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

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

Примеры в этом разделе можно найти в complex.sql и complex.c в каталоге src/tutorial исходного дистрибутива. Смотрите файл README в этом каталоге для инструкций по запуску примеров.

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

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

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

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

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

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

PG_FUNCTION_INFO_V1(complex_in);

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

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

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

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

PG_FUNCTION_INFO_V1(complex_out);

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

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

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

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

PG_FUNCTION_INFO_V1(complex_recv);

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

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

PG_FUNCTION_INFO_V1(complex_send);

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

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

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

CREATE TYPE complex;

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы поддерживать хранение TOAST, функции C, работающие с типом данных, всегда должны быть осторожны, чтобы распаковать любые значения toasted, которые они передают с помощью 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. Но использовать такое определение структуры опасно при работе с потенциально не выровненным datum; компилятор может взять это как лицензию, чтобы предположить, что данные фактически выровнены, что приводит к дампам ядра на архитектурах, которые строго относятся к выравниванию.

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

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

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

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

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

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

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

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

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

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

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

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

SELECT (a + b) AS c FROM test_complex;

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

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

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

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

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

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

COMMUTATOR

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

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

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

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

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

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

NEGATOR

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

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

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

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

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

RESTRICT

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

column OP constant

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

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

eqsel for =
neqsel for <>
scalarltsel for <
scalarlesel for <=
scalargtsel for >
scalargesel for >=

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

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

В src/backend/utils/adt/geo_selfuncs.c есть дополнительные функции оценки селективности, разработанные для геометрических операторов: areasel, positionsel и contsel. На момент написания статьи это всего лишь заглушки, но вы можете использовать их (или, что еще лучше, улучшить их) в любом случае.

JOIN

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

table1.column1 OP table2.column2

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

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

eqjoinsel for =
neqjoinsel for <>
scalarltjoinsel for <
scalarlejoinsel for <=
scalargtjoinsel for >
scalargejoinsel for >=
areajoinsel for 2D area-based comparisons
positionjoinsel for 2D position-based comparisons
contjoinsel for 2D containment-based comparisons

HASHES

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

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

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

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

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

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

Заметка
Если оператор hash-joinable имеет базовую функцию, помеченную как строгую, функция также должна быть завершенной: то есть она должна возвращать истину или ложь, а не null, для любых двух nonnull входных данных. Если это правило не соблюдается, хэш-оптимизация операций IN может привести к неверным результатам. (В частности, IN может вернуть false, если правильный ответ в соответствии со стандартом будет null; или может выдать ошибку, сообщающую, что он не подготовлен к null результату).

MERGES

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример

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

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

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

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

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

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

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

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

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

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

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

PG_FUNCTION_INFO_V1(complex_abs_lt);

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

OPERATOR 1 < (complex, complex),

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

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

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

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

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

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

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

CREATE OPERATOR FAMILY integer_ops USING btree;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT * FROM table WHERE integer_column < 4;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SET LOCAL search_path TO @extschema@;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обновления расширений

Одним из преимуществ механизма расширения является то, что он предоставляет удобные способы управления обновлениями команд SQL, которые определяют объекты расширения. Это делается путем привязки имени или номера версии к каждой выпущенной версии сценария установки расширения. Кроме того, если вы хотите, чтобы пользователи могли динамически обновлять свои базы данных с одной версии на другую, вы должны предоставить сценарии обновления, которые вносят необходимые изменения для перехода с одной версии на другую. Сценарии обновления имеют имена, следующего шаблона extension--old_version--target_version.sql (например, foo--1.0--1.1.sql содержит команды для изменения версии 1.0 расширения foo в версию 1.1 ).

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

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

Механизм обновления может использоваться для решения важного особого случая: преобразования «свободной» коллекции объектов в расширение. До того, как механизм расширения был добавлен в PostgreSQL (в 9.1), многие люди писали модули расширения, которые просто создавали разные неупакованные объекты. Учитывая существующую базу данных, содержащую такие объекты, как мы можем преобразовать объекты в правильно упакованное расширение? Удаление их, а затем выполнение простого CREATE EXTENSION - один из способов, но нежелательно, если у объектов есть зависимости (например, если существуют столбцы таблицы типа данных, созданные расширением). Чтобы исправить эту ситуацию, нужно создать пустое расширение, затем использовать ALTER EXTENSION ADD чтобы присоединить каждый существующий объект к расширению, а затем, наконец, создать любые новые объекты, которые находятся в текущей версии расширения, но отсутствуют в распакованном выпуске. CREATE EXTENSION поддерживает этот случай с опцией FROM old_version, которая заставляет его не запускать обычный скрипт установки для целевой версии, а вместо этого сценарий обновления с именем extension--old_version--target_version.sql. Выбор имени фиктивной версии для использования в качестве old_version зависит от автора расширения, хотя unpackaged является общим соглашением. Если у вас есть несколько предыдущих версий, вы должны иметь возможность обновиться до стиля расширения, используйте несколько фиктивных названий версий для их идентификации.

ALTER EXTENSION может выполнять последовательности файлов сценариев обновления для достижения запрошенного обновления. Например, если доступны только foo--1.0--1.1.sql и foo--1.1--2.0.sql, ALTER EXTENSION будет применять их последовательно, если будет запрошено обновление до версии 2.0, а в данный момент установлена версия 1.0.

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

Иногда полезно предоставить сценарии «понижения», например, foo--1.1--1.0.sql чтобы позволить отменить изменения, связанные с версией 1.1. Если вы это сделаете, будьте осторожны с возможностью неожиданного применения скрипта понижения, поскольку он дает более короткий путь. Рискованный случай - это сценарий обновления «быстрого пути», который переходит вперед на несколько версий, а также сценарий перехода к начальной точке быстрого пути. Может потребоваться меньше шагов, чтобы применить понижение и затем быстрый путь, чем продвигаться вперед по одной версии за раз. Если скрипт понижения удаляет незаменимые объекты, это приведет к нежелательным результатам.

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

SELECT * FROM pg_extension_update_paths('extension_name');

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

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

Расширение, которое существует уже некоторое время, вероятно, будет существовать в нескольких версиях, для которых автору потребуется написать сценарии обновления. Например, если вы выпустили расширение foo в версиях 1.0, 1.1 и 1.2, должны быть сценарии обновления foo--1.0--1.1.sql и foo--1.1--1.2.sql. До PostgreSQL 10 необходимо было также создавать новые файлы сценариев foo--1.1.sql и foo--1.2.sql которые напрямую собирали более новые версии расширений, иначе более новые версии не могли быть установлены напрямую, только путем установки 1.0 и затем обновление. Это было утомительно и дублировало, но теперь это не нужно, потому что CREATE EXTENSION может автоматически следовать цепочкам обновлений. Например, если доступны только файлы сценариев foo--1.0.sql, foo--1.0--1.1.sql и foo--1.1--1.2.sql, то запрос на установку версии 1.2 выполняется с помощью запуска этих трех сценарии в последовательности. Обработка такая же, как если бы вы сначала установили 1.0 а затем обновили до 1.2. (Как и в случае с ALTER EXTENSION UPDATE, если доступно несколько путей, предпочтительнее использовать кратчайший путь). Размещение файлов сценариев расширения в этом стиле может уменьшить объем работ по обслуживанию, необходимых для создания небольших обновлений.

Если вы используете вторичные (зависящие от версии) контрольные файлы с расширением, поддерживаемым в этом стиле, имейте в виду, что каждой версии нужен контрольный файл, даже если у него нет отдельного сценария установки, поскольку этот контрольный файл будет определять, как неявное обновление на эту версию выполняется. Например, если foo--1.0.control указывает requires = 'bar' но другие управляющие файлы foo этого не делают, зависимость расширения от bar будет сброшена при обновлении с 1.0 до другой версии.

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

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

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

-- complain if script is sourced in qsql, rather than via CREATE EXTENSION
\echo Use "CREATE EXTENSION pair" to load this file. \quit

CREATE TYPE pair AS ( k text, v text );

CREATE OR REPLACE FUNCTION pair(text, text)
RETURNS pair LANGUAGE SQL AS 'SELECT ROW($1, $2)::@extschema@.pair;';

CREATE OPERATOR ~> (LEFTARG = text, RIGHTARG = text, FUNCTION = pair);

-- "SET search_path" is easy to get right, but qualified names perform better.
CREATE OR REPLACE FUNCTION lower(pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW(lower($1.k), lower($1.v))::@extschema@.pair;'
SET search_path = pg_temp;

CREATE OR REPLACE FUNCTION pair_concat(pair, pair)
RETURNS pair LANGUAGE SQL
AS 'SELECT ROW($1.k OPERATOR(pg_catalog.||) $2.k,
               $1.v OPERATOR(pg_catalog.||) $2.v)::@extschema@.pair;';

Управляющий файл pair.control выглядит так:

# pair extension
comment = 'A key/value pair data type'
default_version = '1.0'
relocatable = false

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

EXTENSION = pair
DATA = pair--1.0.sql

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

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

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

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

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

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

MODULES = isbn_issn
EXTENSION = isbn_issn
DATA = isbn_issn--1.0.sql
DOCS = README.isbn_issn
HEADERS_isbn_issn = isbn_issn.h

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

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

Установите одну из этих трех переменных, чтобы указать, что собирается:

MODULES
список объектов разделяемой библиотеки, которые будут построены из исходных файлов с одинаковым основанием (не включайте суффиксы библиотеки в этот список)
MODULE_big
общая библиотека для сборки из нескольких исходных файлов (список объектных файлов в OBJS )
PROGRAM
исполняемая программа для сборки (список объектных файлов в OBJS )

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

EXTENSION
имена расширений; для каждого имени вы должны предоставить файл extension.control, который будет установлен в prefix/share/extension
MODULEDIR
подкаталог prefix/share в который должны быть установлены файлы DATA и DOCS (если не установлен, по умолчанию используется extension если установлено EXTENSION, или contrib если нет)
DATA
случайные файлы для установки в prefix/share/$MODULEDIR
DATA_built
случайные файлы для установки в prefix/share/$MODULEDIR, которые нужно сначала собрать
DATA_TSEARCH
случайные файлы для установки c prefix/share/tsearch_data
DOCS
случайные файлы для установки с prefix/doc/$MODULEDIR
HEADERS
HEADERS_built
Файлы (возможно, для сборки и) устанавливаются с prefix/include/server/$MODULEDIR/$MODULE_big .
В отличие от DATA_built, файлы в HEADERS_built не удаляются целью очистки; если вы хотите, чтобы они были удалены, также добавьте их в EXTRA_CLEAN или добавьте свои собственные правила, чтобы сделать это.
HEADERS_$MODULE
HEADERS_built_$MODULE
Файлы для установки (после сборки, если указано) с prefix/include/server/$MODULEDIR/$MODULE , где $MODULE должно быть именем модуля, используемым в MODULES или MODULE_big.
В отличие от DATA_built, файлы в HEADERS_built_$MODULE не удаляются с целью очистки; если вы хотите, чтобы они были удалены, также добавьте их в EXTRA_CLEAN или добавьте свои собственные правила, чтобы сделать это.
Разрешается использовать обе переменные для одного и того же модуля или любой комбинации, если только в списке модулей нет двух имен модулей, которые отличаются только наличием префикса built_, что может привести к неоднозначности. В этом (надеюсь маловероятном) случае вы должны использовать только переменные HEADERS_built_$MODULE .
SCRIPTS
файлы сценариев (не двоичные файлы) для установки в prefix/bin
SCRIPTS_built
файлы сценариев (не двоичные файлы) для установки в prefix/bin, которые должны быть собраны в первую очередь
REGRESS
список регрессионных тестов (без суффикса), см. ниже
REGRESS_OPTS
дополнительные ключи для перехода на pg_regress
ISOLATION
список тестовых случаев изоляции, см. ниже для более подробной информации
ISOLATION_OPTS
дополнительные ключи для передачи в pg_isolation_regress
TAP_TESTS
переключатель, определяющий, нужно ли запускать тесты TAP, см. ниже
NO_INSTALLCHECK
не определять цель installcheck, полезно, например, если тесты требуют специальной конфигурации или не используют pg_regress
EXTRA_CLEAN
дополнительные файлы для удаления в make clean
PG_CPPFLAGS
будет добавлен к CPPFLAGS
PG_CFLAGS
будет добавлен к CFLAGS
PG_CXXFLAGS
будет добавлен в CXXFLAGS
PG_LDFLAGS
будет добавлен к LDFLAGS
PG_LIBS
будет добавлен в строку ссылки PROGRAM
SHLIB_LINK
будет добавлен в строку ссылки MODULE_big
PG_CONFIG
путь к программе pg_config для сборки установки QHB (обычно просто pg_config для использования первым в вашем PATH)

Поместите этот makefile как Makefile в каталог, содержащий ваше расширение. Затем вы можете выполнить make для компиляции, а затем make install для установки вашего модуля. По умолчанию расширение компилируется и устанавливается для установки QHB, которая соответствует первой программе pg_config найденной в вашем PATH. Вы можете использовать другую установку, установив PG_CONFIG чтобы он указывал на ее программу pg_config, либо внутри makefile, либо в командной строке make.

Вы также можете запустить make в каталоге за пределами исходного дерева вашего расширения, если вы хотите сохранить каталог сборки отдельно. Эта процедура также называется VPATH построить. Вот как:

mkdir build_dir
cd build_dir
make -f /path/to/extension/source/tree/Makefile
make -f /path/to/extension/source/tree/Makefile install

Кроме того, вы можете настроить каталог для сборки VPATH аналогично тому, как это делается для основного кода. Один из способов сделать это - использовать основной скрипт config/prep_buildtree. Как только это будет сделано, вы можете собрать, установив переменную make VPATH следующим образом:

make VPATH=/path/to/extension/source/tree
make VPATH=/path/to/extension/source/tree install

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

Сценарии, перечисленные в переменной REGRESS, используются для регрессионного тестирования вашего модуля, который может быть вызван командой make installcheck после выполнения команды make install. Чтобы это работало, у вас должен быть запущен сервер QHB. Файлы сценариев, перечисленные в REGRESS должны появляться в подкаталоге с именем sql/ в каталоге вашего расширения. Эти файлы должны иметь расширение .sql, которое не должно быть включено в список REGRESS в makefile. Для каждого теста также должен быть файл, содержащий ожидаемый результат в подкаталоге с именем expected/, с тем же основанием и расширением .out. make installcheck выполняет каждый тестовый сценарий с помощью qsql и сравнивает полученный результат с соответствующим ожидаемым файлом. Любые различия будут записаны в файл regression.diffs в формате diff -c. Обратите внимание, что попытка запустить тест, в котором отсутствует ожидаемый файл, будет отображаться как "trouble", поэтому убедитесь, что у вас есть все ожидаемые файлы.

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

TAP_TESTS позволяет использовать тесты TAP. Данные каждого прогона присутствуют в подкаталоге с именем tmp_check/.

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