Здравствуйте, хабрачеловеки! Темой этой статьи будет создание расширений для PostgreSQL. В качестве примера, мы реализуем небольшую библиотеку для работы с 3D векторами. Параллельно будут рассмотрены пользовательские типы, операторы и приведения типов. Не будет лишним ознакомися с этим материалом, так как реализация хранимых функций будет на языке C. Надеюсь, друзья слонов помогут скрасить серый технический текст статьи.

Описание

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

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

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

Для повышения производительности, напишем всю логику на языке C и оформим в виде динамически загружаемой библиотеки math3d. Также, где это будет интуитивно, создадим операторы. Ну и, напоследок, завернём всё это в расширение.

Создание типа

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

Некоторые параметры типа со стороны СУБД:

internallength — размер внутреннего представления
alignment — выравнивание, допустимые значения 1, 2, 4, 8 байта
storage — выбор хранилища, plain (единственно возможный вариант для типов с фиксированным размером) для хранения без сжатия, extended допускает сжатие и перемещение вне пределов строки таблицы, где объявлен тип, main позволяет сжатие, но запрещает перемещение
receive — функция получения для бинарного ввода/вывода
send — функция отправки для бинарного ввода/вывода

Определим в исходном файле math3d.c тип vector3, функции текстового ввода/вывода и функции бинарного ввода/вывода для этого типа:

#include <postgres.h>
#include <fmgr.h>
#include <libpq/pqformat.h>
#include <math.h>

#ifdef PG_MODULE_MAGIC
	PG_MODULE_MAGIC;
#endif

typedef struct
{
	double x, y, z;
} vector3;

PG_FUNCTION_INFO_V1(vector3_in);
PG_FUNCTION_INFO_V1(vector3_out);

PG_FUNCTION_INFO_V1(vector3_recv);
PG_FUNCTION_INFO_V1(vector3_send);

Datum vector3_in(PG_FUNCTION_ARGS)
{
	char *s = PG_GETARG_CSTRING(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	if (sscanf(s, "(%lf,%lf,%lf)", &(v->x), &(v->y), &(v->z)) != 3)
	{
		ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("Invalid input syntax for vector3: \"%s\"", s)));
	}

	PG_RETURN_POINTER(v);
}

Datum vector3_out(PG_FUNCTION_ARGS)
{
	vector3 *v = (vector3*)PG_GETARG_POINTER(0);

	char *s = (char*)palloc(100);

	snprintf(s, 100, "(%lf,%lf,%lf)", v->x, v->y, v->z);

	PG_RETURN_CSTRING(s);
}

Datum vector3_recv(PG_FUNCTION_ARGS)
{
	StringInfo buffer = (StringInfo)PG_GETARG_POINTER(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = pq_getmsgfloat8(buffer);
	v->y = pq_getmsgfloat8(buffer);
	v->z = pq_getmsgfloat8(buffer);

	PG_RETURN_POINTER(v);
}

Datum vector3_send(PG_FUNCTION_ARGS)
{
	vector3 *v = (vector3*)PG_GETARG_POINTER(0);

	StringInfoData buffer;

	pq_begintypsend(&buffer);

	pq_sendfloat8(&buffer, v->x);
	pq_sendfloat8(&buffer, v->y);
	pq_sendfloat8(&buffer, v->z);

	PG_RETURN_BYTEA_P(pq_endtypsend(&buffer));
}

Условимся, что текстовое представление типа vector3 будет в виде "(x,y,z)", где x, y, z, собственно, компоненты вектора. В функции vector3_in из аргумента, типа C-строка, с помощью sscanf извлекаются компоненты вектора и созданный вектор (точнее указатель на него) возращается в качестве результата функции. В vector3_out происходит обратное действие — преобразование вектора в строку и ее возрат.

Перейдем теперь в консоль, соберём динамически загружаемую библиотеку math3d и поместим ее в каталог $libdir (узнать который можно, выполнив команду pg_config --pkglibdir):

cc -I/usr/local/pgsql/include/server -fpic -c math3d.c
cc -shared -L/usr/local/pgsql/lib -lpq -o math3d.so math3d.o
cp math3d.so /usr/local/pgsql/lib/

Теперь, давайте создадим тип vector3 в БД:

CREATE TYPE vector3;

CREATE OR REPLACE FUNCTION vector3_in ( s cstring )
RETURNS vector3 AS
'math3d', 'vector3_in'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_out ( v vector3 )
RETURNS cstring AS
'math3d', 'vector3_out'
LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION vector3_recv ( p internal )
RETURNS vector3 AS
'math3d', 'vector3_recv'
LANGUAGE C IMMUTABLE STRICT;

CREATE FUNCTION vector3_send ( v vector3 )
RETURNS bytea AS
'math3d', 'vector3_send'
LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE vector3
(
	internallength = 24,
	input = vector3_in,
	output = vector3_out,
	receive = vector3_recv,
	send = vector3_send
);

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

Выполним тестовый запрос:

SELECT '(0.0,1.0,0.0)'::vector3; -- (0.000000,1.000000,0.000000) с типом vector3

Операции с вектором

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

Реализация операций в math3d.c

PG_FUNCTION_INFO_V1(vector3_minus); // унарный минус
PG_FUNCTION_INFO_V1(vector3_add); // сложение векторов
PG_FUNCTION_INFO_V1(vector3_sub); // вычитание векторов
PG_FUNCTION_INFO_V1(vector3_mul_left); // умножение вектора на скаляр
PG_FUNCTION_INFO_V1(vector3_mul_right); // умножение скаляра на вектор
PG_FUNCTION_INFO_V1(vector3_div_left); // деление вектора на скаляр
PG_FUNCTION_INFO_V1(vector3_div_right); // деление скаляра на вектор

PG_FUNCTION_INFO_V1(vector3_equal); // проверка векторов на равенство
PG_FUNCTION_INFO_V1(vector3_not_equal); // проверка векторов на неравенство

PG_FUNCTION_INFO_V1(vector3_dot); // скалярное произведение
PG_FUNCTION_INFO_V1(vector3_cross); // векторное произведение

PG_FUNCTION_INFO_V1(vector3_length); // длина вектора
PG_FUNCTION_INFO_V1(vector3_normalize); // нормализация вектора
PG_FUNCTION_INFO_V1(vector3_distance); // расстояние между векторами

Datum vector3_minus(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = -v0->x;
	v->y = -v0->y;
	v->z = -v0->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_add(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x + v1->x;
	v->y = v0->y + v1->y;
	v->z = v0->z + v1->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_sub(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x - v1->x;
	v->y = v0->y - v1->y;
	v->z = v0->z - v1->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_mul_left(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	double k = PG_GETARG_FLOAT8(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x * k;
	v->y = v0->y * k;
	v->z = v0->z * k;

	PG_RETURN_POINTER(v);
}

Datum vector3_mul_right(PG_FUNCTION_ARGS)
{
	double k = PG_GETARG_FLOAT8(0);
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = k * v0->x;
	v->y = k * v0->y;
	v->z = k * v0->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_div_left(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	double k = PG_GETARG_FLOAT8(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x / k;
	v->y = v0->y / k;
	v->z = v0->z / k;

	PG_RETURN_POINTER(v);
}

Datum vector3_div_right(PG_FUNCTION_ARGS)
{
	double k = PG_GETARG_FLOAT8(0);
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = k / v0->x;
	v->y = k / v0->y;
	v->z = k / v0->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_equal(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	bool equal = true;

	equal &= v0->x == v1->x;
	equal &= v0->y == v1->y;
	equal &= v0->z == v1->z;

	PG_RETURN_BOOL(equal);
}

Datum vector3_not_equal(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	bool not_equal = false;

	not_equal |= v0->x != v1->x;
	not_equal |= v0->y != v1->y;
	not_equal |= v0->z != v1->z;

	PG_RETURN_BOOL(not_equal);
}

Datum vector3_dot(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	double r = v0->x * v1->x + v0->y * v1->y + v0->z * v1->z;

	PG_RETURN_FLOAT8(r);
}

Datum vector3_cross(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->y * v1->z - v0->z * v1->y;
	v->y = v0->z * v1->x - v0->x * v1->z;
	v->z = v0->x * v1->y - v0->y * v1->x;

	PG_RETURN_POINTER(v);
}

Datum vector3_length(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);

	double len = sqrt(v0->x * v0->x + v0->y * v0->y + v0->z * v0->z);

	PG_RETURN_FLOAT8(len);
}

Datum vector3_normalize(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	double len = sqrt(v0->x * v0->x + v0->y * v0->y + v0->z * v0->z);

	if (len > 0.000001)
	{
		v->x = v0->y / len;
		v->y = v0->z / len;
		v->z = v0->x / len;
	}
	else
	{
		v->x = 0.0;
		v->y = 0.0;
		v->z = 0.0;
	}

	PG_RETURN_POINTER(v);
}

Datum vector3_distance(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x - v1->x;
	v->y = v0->y - v1->y;
	v->z = v0->z - v1->z;

	double len = sqrt(v->x * v->x + v->y * v->y + v->z * v->z);

	pfree(v);

	PG_RETURN_FLOAT8(len);
}

Напомню, что для выделения памяти следует использовать palloc, для освобождения, соответсвенно pfree. Почему нужны пары функций vector3_mul_left/vector3_mul_right и vector3_div_left/vector3_div_right будет объяснено далее.

Пересоберём библиотеку math3d.so и создадим эти функции в БД, в новой сессию, чтобы сервер PostgreSQL загрузил новую версию библиотеки:

SQL код для создания операций

CREATE OR REPLACE FUNCTION vector3_minus ( v0 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_minus'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_add ( v0 vector3, v1 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_add'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_sub ( v0 vector3, v1 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_sub'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_mul_left ( v0 vector3, k double precision )
RETURNS vector3 AS
'math3d', 'vector3_mul_left'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_mul_right ( k double precision, v0 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_mul_right'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_div_left ( v0 vector3, k double precision )
RETURNS vector3 AS
'math3d', 'vector3_div_left'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_div_right ( k double precision, v0 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_div_right'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_equal ( v0 vector3, v1 vector3 )
RETURNS boolean AS
'math3d', 'vector3_equal'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_not_equal ( v0 vector3, v1 vector3 )
RETURNS boolean AS
'math3d', 'vector3_not_equal'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_dot ( v0 vector3, v1 vector3 )
RETURNS double precision AS
'math3d', 'vector3_dot'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_cross ( v0 vector3, v1 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_cross'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION length ( v0 vector3 )
RETURNS double precision AS
'math3d', 'vector3_length'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION normalize ( v0 vector3 )
RETURNS vector3 AS
'math3d', 'vector3_normalize'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION distance ( v0 vector3, v1 vector3 )
RETURNS double precision AS
'math3d', 'vector3_distance'
LANGUAGE C IMMUTABLE STRICT;

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

SELECT vector3_add ( '(0.0,1.0,0.0)'::vector3, '(0.5,0.5,0.0)'::vector3 ); -- (0.500000,1.500000,0.000000)
SELECT vector3_mul_right ( 5.0, '(0.2,0.2,1.33)'::vector3 ); -- (1.000000,1.000000,6.650000)
SELECT vector3_cross ( '(1.0,0.0,0.0)'::vector3, '(0.0,1.0,0.0)'::vector3 ); -- (0.000000,0.000000,1.000000)
SELECT length ( '(0.705,0.705,0.0)'::vector3 ); -- 0.9970206

Функционал имеется, но выглядит он не очень хорошо, к примеру, для умножения скаляра на вектор интутивней была бы запись 5.0 * '(0.2,0.2,1.33)'::vector3. Давайте для этого определим операторы.

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

В PostgreSQL есть возможность определять свои операторы, используя последовательность из символов + — * / < > = ~! @ # % ^ & | `? с максимальной длиной 63. Они бывают унарными или бинарными. Можно создавать перегруженные операторы, имеющие одинаковое имя, но разные аргументы. Вот некоторые важные параметры оператора (для унарных операторов необходимо указывать только leftarg или rightarg):

leftarg — тип левого аргумента
rightarg — тип правого аргумента
procedure — функция, имеющая один (унарный оператор) или два параметра (бинарный оператор) с типами, соответсвующими leftarg и rightarg оператора
commutator — подсказка оптимизатору, что выражение x A y эквивалентно y B x, где A — объявляемый оператор, B — оператор для commutator
negator — подсказка оптимизатору, что выражение x A y эквивалентно !(x B y), где A — объявляемый оператор, B — оператор для negator и оба оператора должны возращать boolean

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

SQL код для создания операторов

-- унарный минус
CREATE OPERATOR -
(
    rightarg = vector3,
    procedure = vector3_minus
);

-- сложение векторов
CREATE OPERATOR +
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_add,
    commutator = +
);

-- вычитание векторов
CREATE OPERATOR -
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_sub
);

-- умножение вектора на скаляр
CREATE OPERATOR *
(
    leftarg = vector3,
    rightarg = double precision,
    procedure = vector3_mul_left
);

-- умножение скаляра на вектор
CREATE OPERATOR *
(
    leftarg = double precision,
    rightarg = vector3,
    procedure = vector3_mul_right
);

-- деление вектора на скаляр
CREATE OPERATOR /
(
    leftarg = vector3,
    rightarg = double precision,
    procedure = vector3_div_left
);

-- деление скаляра на вектор
CREATE OPERATOR /
(
    leftarg = double precision,
    rightarg = vector3,
    procedure = vector3_div_right
);

-- проверка векторов на равенство
CREATE OPERATOR =
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_equal
);

-- проверка векторов на неравенство
CREATE OPERATOR !=
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_not_equal
);

-- скалярное произведение
CREATE OPERATOR *
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_dot
    commutator = 
);

-- векторное произведение
CREATE OPERATOR **
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_cross
);

И проверим их работоспособность:

SELECT '(0.0,1.0,0.0)'::vector3 + '(0.5,0.5,0.0)'::vector3; -- (0.500000,1.500000,0.000000)
SELECT 5.0 * '(0.2,0.2,1.33)'::vector3; -- (1.000000,1.000000,6.650000)
SELECT '(1.0,0.5,0.1)'::vector3 * '(0.707,0.707,0.707)'::vector3; -- 1.1312
SELECT '(1.0,0.0,0.0)'::vector3 ** '(0.0,1.0,0.0)'::vector3; -- (0.000000,0.000000,1.000000)

Уже лучше. Кстати, мы обьявили два оператора для умножения с аргументом типа скаляр, для случая, когда скаляр слева оператора и когда он справа. И аналогично для деления. Поэтому нам и потребовались пары функций vector3_mul_left/vector3_mul_right и vector3_div_left/vector3_div_right.

Может возникнуть вопрос: а как получить доступ к компонентам вектора? Можно было бы объявить три C-функции vector3_x, vector3_y и vector3_z, которые бы возращали каждую компоненту, но есть способ лучше.

Приведение типов

Еще одной примечательной возможностью в PostgreSQL является создание пользовательского приведения типов. Если оба типа имеют одинаковое внутреннее представление (к примеру, varchar и text), то необходимости в функции для преобразования типов нет. В противном случае, эту функцию необходимо определить. Она должна возращать тип, к которому происходит приведение и может иметь от одного до трёх параметров:

тип который приводится
integer — модификатор, ассоциированный с типом к которому происходит приведение или -1
boolean — true, если приведение явное, в противном случае false

Приведение может учавствовать только контексте присваивания или же в любом контексте. Это поведение указывается с помощью параметров AS ASSIGNMENT и AS IMPLICIT. Параметр WITH INOUT указывает использовать функции ввода/вывода для приведения.

Определим новый композитный тип vector3c и приведение к нему типа vector3 (и наоборот):

CREATE TYPE vector3c AS
(
	x double precision,
	y double precision,
	z double precision
);

CREATE OR REPLACE FUNCTION vector3_cast_vector3c ( v0 vector3 )
RETURNS vector3c AS
$BODY$
DECLARE
	s text[];
	v vector3c;
BEGIN
	s := string_to_array ( trim ( BOTH '()' FROM v0::text ), ',' );
	v.x := s[1];
	v.y := s[2];
	v.z := s[3];
	RETURN v;
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION vector3c_cast_vector3 ( v0 vector3c )
RETURNS vector3 AS
$BODY$
DECLARE
	v vector3;
BEGIN
	v := v0::text;
	RETURN v;
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;

CREATE CAST ( vector3 AS vector3c )
WITH FUNCTION  vector3_cast_vector3c ( v0 vector3 )
AS IMPLICIT;

CREATE CAST ( vector3c AS vector3 )
WITH FUNCTION  vector3c_cast_vector3 ( v0 vector3c )
AS IMPLICIT;

В функции vector3_cast_vector3c мы сперва приводим vector3 к тексту, убираем первую и последнюю скобки и затем, используя разделитель запятую, преобразовываем к массиву из трёх елементов, из которого берем компоненты вектора. В vector3c_cast_vector3, для наглядности, можно сразу преобразовывать vector3c в текст и затем приводить к vector3 (текстовое представление для vector3c и vector3 имеет один и тот же вид).

Проверим приведение типов:

SELECT ('(0.1,1.0,0.5)'::vector3)::vector3c; -- (0.1,1,0.5)
SELECT ('(0.707,0.0,0.0)'::vector3c)::vector3; -- (0.707000,0.000000,0.000000)

Создание расширения

Когда всё готово и протестировано, осталось обернуть нашу библитеку в расширение. Соберем весь С-код в один файл:

Файл math3d.c (исходный С-код динамически загружаемой библиотеки расширения math3d)

#include <postgres.h>
#include <fmgr.h>
#include <libpq/pqformat.h>
#include <math.h>

#ifdef PG_MODULE_MAGIC
	PG_MODULE_MAGIC;
#endif

// types

typedef struct
{
	double x, y, z;
} vector3;

// declarations

PG_FUNCTION_INFO_V1(vector3_in);
PG_FUNCTION_INFO_V1(vector3_out);

PG_FUNCTION_INFO_V1(vector3_recv);
PG_FUNCTION_INFO_V1(vector3_send);

PG_FUNCTION_INFO_V1(vector3_minus);
PG_FUNCTION_INFO_V1(vector3_add);
PG_FUNCTION_INFO_V1(vector3_sub);
PG_FUNCTION_INFO_V1(vector3_mul_left);
PG_FUNCTION_INFO_V1(vector3_mul_right);
PG_FUNCTION_INFO_V1(vector3_div_left);
PG_FUNCTION_INFO_V1(vector3_div_right);

PG_FUNCTION_INFO_V1(vector3_equal);
PG_FUNCTION_INFO_V1(vector3_not_equal);

PG_FUNCTION_INFO_V1(vector3_dot);
PG_FUNCTION_INFO_V1(vector3_cross);

PG_FUNCTION_INFO_V1(vector3_length);
PG_FUNCTION_INFO_V1(vector3_normalize);
PG_FUNCTION_INFO_V1(vector3_distance);

// implementation

Datum vector3_in(PG_FUNCTION_ARGS)
{
	char *s = PG_GETARG_CSTRING(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	if (sscanf(s, "(%lf,%lf,%lf)", &(v->x), &(v->y), &(v->z)) != 3)
	{
		ereport(ERROR, (errcode(ERRCODE_INVALID_TEXT_REPRESENTATION), errmsg("Invalid input syntax for vector3: \"%s\"", s)));
	}

	PG_RETURN_POINTER(v);
}

Datum vector3_out(PG_FUNCTION_ARGS)
{
	vector3 *v = (vector3*)PG_GETARG_POINTER(0);

	char *s = (char*)palloc(100);

	snprintf(s, 100, "(%lf,%lf,%lf)", v->x, v->y, v->z);

	PG_RETURN_CSTRING(s);
}


Datum vector3_recv(PG_FUNCTION_ARGS)
{
	StringInfo buffer = (StringInfo)PG_GETARG_POINTER(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = pq_getmsgfloat8(buffer);
	v->y = pq_getmsgfloat8(buffer);
	v->z = pq_getmsgfloat8(buffer);

	PG_RETURN_POINTER(v);
}

Datum vector3_send(PG_FUNCTION_ARGS)
{
	vector3 *v = (vector3*)PG_GETARG_POINTER(0);

	StringInfoData buffer;

	pq_begintypsend(&buffer);

	pq_sendfloat8(&buffer, v->x);
	pq_sendfloat8(&buffer, v->y);
	pq_sendfloat8(&buffer, v->z);

	PG_RETURN_BYTEA_P(pq_endtypsend(&buffer));
}

Datum vector3_minus(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = -v0->x;
	v->y = -v0->y;
	v->z = -v0->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_add(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x + v1->x;
	v->y = v0->y + v1->y;
	v->z = v0->z + v1->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_sub(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x - v1->x;
	v->y = v0->y - v1->y;
	v->z = v0->z - v1->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_mul_left(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	double k = PG_GETARG_FLOAT8(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x * k;
	v->y = v0->y * k;
	v->z = v0->z * k;

	PG_RETURN_POINTER(v);
}

Datum vector3_mul_right(PG_FUNCTION_ARGS)
{
	double k = PG_GETARG_FLOAT8(0);
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = k * v0->x;
	v->y = k * v0->y;
	v->z = k * v0->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_div_left(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	double k = PG_GETARG_FLOAT8(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x / k;
	v->y = v0->y / k;
	v->z = v0->z / k;

	PG_RETURN_POINTER(v);
}

Datum vector3_div_right(PG_FUNCTION_ARGS)
{
	double k = PG_GETARG_FLOAT8(0);
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = k / v0->x;
	v->y = k / v0->y;
	v->z = k / v0->z;

	PG_RETURN_POINTER(v);
}

Datum vector3_equal(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	bool equal = true;

	equal &= v0->x == v1->x;
	equal &= v0->y == v1->y;
	equal &= v0->z == v1->z;

	PG_RETURN_BOOL(equal);
}

Datum vector3_not_equal(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	bool not_equal = false;

	not_equal |= v0->x != v1->x;
	not_equal |= v0->y != v1->y;
	not_equal |= v0->z != v1->z;

	PG_RETURN_BOOL(not_equal);
}

Datum vector3_dot(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	double r = v0->x * v1->x + v0->y * v1->y + v0->z * v1->z;

	PG_RETURN_FLOAT8(r);
}

Datum vector3_cross(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->y * v1->z - v0->z * v1->y;
	v->y = v0->z * v1->x - v0->x * v1->z;
	v->z = v0->x * v1->y - v0->y * v1->x;

	PG_RETURN_POINTER(v);
}

Datum vector3_length(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);

	double len = sqrt(v0->x * v0->x + v0->y * v0->y + v0->z * v0->z);

	PG_RETURN_FLOAT8(len);
}

Datum vector3_normalize(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	double len = sqrt(v0->x * v0->x + v0->y * v0->y + v0->z * v0->z);

	if (len > 0.000001)
	{
		v->x = v0->y / len;
		v->y = v0->z / len;
		v->z = v0->x / len;
	}
	else
	{
		v->x = 0.0;
		v->y = 0.0;
		v->z = 0.0;
	}

	PG_RETURN_POINTER(v);
}

Datum vector3_distance(PG_FUNCTION_ARGS)
{
	vector3 *v0 = (vector3*)PG_GETARG_POINTER(0);
	vector3 *v1 = (vector3*)PG_GETARG_POINTER(1);

	vector3 *v = (vector3*)palloc(sizeof(vector3));

	v->x = v0->x - v1->x;
	v->y = v0->y - v1->y;
	v->z = v0->z - v1->z;

	double len = sqrt(v->x * v->x + v->y * v->y + v->z * v->z);

	pfree(v);

	PG_RETURN_FLOAT8(len);
}

Соберём саму динамически загружаемую библиотеку и поместим ее в каталог PGDIR/lib. Аналогично, создадим файл с SQL кодом:

Файл math3d--1.0.sql (скрипт SQL для расширения math3d)

CREATE TYPE vector3;

CREATE OR REPLACE FUNCTION vector3_in ( s cstring )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_in'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_out ( v vector3 )
RETURNS cstring AS
'MODULE_PATHNAME', 'vector3_out'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_recv ( p internal )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_recv'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION vector3_send ( v vector3 )
RETURNS bytea AS
'MODULE_PATHNAME', 'vector3_send'
LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE vector3
(
	internallength = 24,
	input = vector3_in,
	output = vector3_out,
	receive = vector3_recv,
	send = vector3_send
);

CREATE OR REPLACE FUNCTION vector3_minus ( v0 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_minus'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR -
(
    rightarg = vector3,
    procedure = vector3_minus
);

CREATE OR REPLACE FUNCTION vector3_add ( v0 vector3, v1 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_add'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR +
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_add,
    commutator = +
);

CREATE OR REPLACE FUNCTION vector3_sub ( v0 vector3, v1 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_sub'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR -
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_sub
);

CREATE OR REPLACE FUNCTION vector3_mul_left ( v0 vector3, k double precision )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_mul_left'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR *
(
    leftarg = vector3,
    rightarg = double precision,
    procedure = vector3_mul_left,
    commutator = *
);

CREATE OR REPLACE FUNCTION vector3_mul_right ( k double precision, v0 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_mul_right'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR *
(
    leftarg = double precision,
    rightarg = vector3,
    procedure = vector3_mul_right,
    commutator = *
);

CREATE OR REPLACE FUNCTION vector3_div_left ( v0 vector3, k double precision )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_div_left'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR /
(
    leftarg = vector3,
    rightarg = double precision,
    procedure = vector3_div_left
);

CREATE OR REPLACE FUNCTION vector3_div_right ( k double precision, v0 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_div_right'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR /
(
    leftarg = double precision,
    rightarg = vector3,
    procedure = vector3_div_right
);

CREATE OR REPLACE FUNCTION vector3_equal ( v0 vector3, v1 vector3 )
RETURNS boolean AS
'MODULE_PATHNAME', 'vector3_equal'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR =
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_equal
);

CREATE OR REPLACE FUNCTION vector3_not_equal ( v0 vector3, v1 vector3 )
RETURNS boolean AS
'MODULE_PATHNAME', 'vector3_not_equal'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR !=
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_not_equal
);

CREATE OR REPLACE FUNCTION vector3_dot ( v0 vector3, v1 vector3 )
RETURNS double precision AS
'MODULE_PATHNAME', 'vector3_dot'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR *
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_dot,
    commutator = *
);

CREATE OR REPLACE FUNCTION vector3_cross ( v0 vector3, v1 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_cross'
LANGUAGE C IMMUTABLE STRICT;

CREATE OPERATOR **
(
    leftarg = vector3,
    rightarg = vector3,
    procedure = vector3_cross,
    commutator = **
);

CREATE OR REPLACE FUNCTION length ( v0 vector3 )
RETURNS double precision AS
'MODULE_PATHNAME', 'vector3_length'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION normalize ( v0 vector3 )
RETURNS vector3 AS
'MODULE_PATHNAME', 'vector3_normalize'
LANGUAGE C IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION distance ( v0 vector3, v1 vector3 )
RETURNS double precision AS
'MODULE_PATHNAME', 'vector3_distance'
LANGUAGE C IMMUTABLE STRICT;

CREATE TYPE vector3c AS
(
	x double precision,
	y double precision,
	z double precision
);

CREATE OR REPLACE FUNCTION vector3_cast_vector3c ( v0 vector3 )
RETURNS vector3c AS
$BODY$
DECLARE
	s text[];
	v vector3c;
BEGIN
	s := string_to_array ( trim ( BOTH '()' FROM v0::text ), ',' );
	v.x := s[1];
	v.y := s[2];
	v.z := s[3];
	RETURN v;
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION vector3c_cast_vector3 ( v0 vector3c )
RETURNS vector3 AS
$BODY$
DECLARE
	v vector3;
BEGIN
	v := v0::text;
	RETURN v;
END
$BODY$
LANGUAGE plpgsql IMMUTABLE;

CREATE CAST ( vector3 AS vector3c )
WITH FUNCTION  vector3_cast_vector3c ( v0 vector3 )
AS IMPLICIT;

CREATE CAST ( vector3c AS vector3 )
WITH FUNCTION  vector3c_cast_vector3 ( v0 vector3c )
AS IMPLICIT;

Имя файла должно быть в виде <имя_расширения>--<версия>.sql. Версия у нас будет 1.0. Поместим этот файл в каталог PGDIR/share/extension. Заметьте, что имя динамически загружаемой библиотеки в объявлениях функций изменилось на переменную MODULE_PATHNAME, которая будет объявлена в управляющем файле расширения. Создадим этот файл:

# math3d extension
comment = '3D mathematics'
default_version = '1.0'
module_pathname = '$libdir/math3d'
relocatable = true

Среди доступных параметров имеется следующие:

default_version — версия по умолчанию
comment — комментарий
encoding — кодировка скрипта, если не указана испольуется кодировка БД
module_pathname — имя динамически загружаемой библиотеки (подставляется в переменную MODULE_PATHNAME в скрипте)
requires — список расширений, от которых зависит текущее
relocatable — если true, то расширение не привязывается к конкретной схеме и объекты, после создания, могут быть перемещены в другую схему (по умолчанию false)
schema — схема, в которой создаются объекты расширения (параметр имеет значение, если relocatable установлено в false)

Имя управляющего файла должно быть в виде <имя_расширения>.control, в данном случае math3d.control. Поместим его в каталог PGDIR/share/extension. В принципе, расширение готово к использованию.

Создадим новую БД, соединимся с ней и загрузим наше расширение:

CREATE EXTENSION math3d;

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

DROP EXTENSION math3d;

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

CREATE TABLE user_setting ( username text, key text, value text );
SELECT pg_catalog.pg_extension_config_dump ( 'user_setting', '' );

Второй параметр pg_catalog.pg_extension_config_dump может содержать условие, которое фильтрует данные, попадаемые в дамп, к примеру, 'WHERE username = ''administrator'''. В нашем случае, нет необходимости в конфигурационных таблицах.

При обновлении расширения до более новой версии создается скрипт, имеющий имя в виде <имя_расширения>--<старая_версия>--<новая_версия>.sql, в котором содержатся SQL команды для обновления. Если бы мы захотели обновить math3d до версии 1.1, нам нужно было бы создать файл math3d--1.0--1.1.sql и выполнить SQL команду в БД:

ALTER EXTENSION math3d UPDATE TO '1.1'

Остальные команды для изменения расширения (подробней описано тут):

ALTER EXTENSION <имя_расширения> SET SCHEMA <новая схема>; -- перемещение расширения в указанную схему
ALTER EXTENSION <имя_расширения> ADD <объект>; -- добавление объекта в расширение
ALTER EXTENSION <имя_расширения> DROP <объект>; -- удаление объекта из расширения

Еще одной хорошей вещью в расширении есть то, что нельзя обычной командой (DROP TABLE, DROP FUNCTION и т.д.) случайно удалить объект, входящий в состав расширения.

Заключение

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

P.S. Спасибо за внимание.

Ссылки:

Официальная документация по PostgreSQL

Создание расширений в PostgreSQL