Creating extensions in PostgreSQL

Hello, habracheloveki! The topic of this article will be the creation of extensions for PostgreSQL . As an example, we are implementing a small library for working with 3D vectors. In parallel, user types, operators, and type casts will be considered. It will not be superfluous to familiarize yourself with this material, since the implementation of the stored functions will be in the C language. I hope the friends of the elephants will help to brighten up the gray technical text of the article.

Description

An extension in PostgreSQL is a collection of several SQL objects (data types, functions, operators) combined into a script, a dynamically loaded library (if necessary) and a control file that specifies the name of the script, the path to the library, the default version, and others. options. The use of extensions makes it easy to deploy additional logic in the database, do a migration to a newer version and, when the extension is deleted, correctly delete the dependent objects.

We need to create a new vector3 data type and define such operations:

vector addition
vectors subtraction
multiplication of vector by scalar
scalar product
vector product
finding the length of the vector
vector normalization
determining the distance between vectors

To improve performance, we will write all the logic in the C language and design it as a dynamically loadable math3d library. Also, where it will be intuitive, create the operators. And finally, we wrap it all in an extension.

Type creation

PostgreSQL DBMS allows you to define, in addition to composite types, enumeration types and range types , new data types . The latter require the implementation of functions working with a type in a lower-level language than SQL, as a rule, in C. Defining a custom type requires at least two functions: input and output. The input function has one parameter with the type C-string (byte array, ending in zero) and returns a custom type. The output function has a parameter with a custom type and returns a C-string. These functions are required to convert from external (text) display to internal representation and vice versa.
')
Some type parameters from the DBMS side:

internallength - the size of the internal representation
alignment - alignment, valid values 1, 2, 4, 8 bytes
storage - storage selection, plain (the only possible option for types with a fixed size) for storage without compression, extended allows compression and movement outside the table row where the type is declared, main allows compression but prohibits movement
receive - receive function for binary input / output
send - send function for binary input / output

In the source file math3d.c, we define the vector3 type, text input / output functions, and binary input / output functions for this type:

#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)); }

We agree that the textual representation of the vector3 type will be in the form "(x, y, z)", where x, y, z, in fact, are the components of the vector. In the vector3_in function, the components of the vector are extracted from the argument, of type C-string, using sscanf and the created vector (or more precisely, a pointer to it) is returned as the result of the function. In vector3_out , the reverse action occurs - the vector is converted into a string and returned.

Now let's go to the console, build the dynamically loadable math3d library and place it in the $ libdir directory (you can find out by running the pg_config --pkglibdir command ):

 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/

Now, let's create the vector3 type in the database:

 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 );

Note that you first need to declare a type in order to be able to create input and output functions. When determining the type, we specify the size of the internal representation and the functions for input / output from the parameters.

Perform a test query:

 SELECT '(0.0,1.0,0.0)'::vector3; -- (0.000000,1.000000,0.000000)   vector3

Vector operations

The type is created, but it is more than a cast to a textual view and vice versa, it is not yet capable. We will make it more functional by expanding it with the required operations:

Implementing operations in 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); }

Let me remind you that palloc should be used for memory allocation, for freeing, respectively pfree . Why couples of functions vector3_mul_left / vector3_mul_right and vector3_div_left / vector3_div_right are needed will be explained further.

Let's rebuild the math3d.so library and create these functions in the database, in a new session, so that the PostgreSQL server loads the new version of the library:

SQL code for creating operations

 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;

Now you can do different operations on the vector:

 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

The functionality is available, but it doesn’t look very good, for example, to multiply a scalar by a vector, the record 5.0 * '(0.2.0.2,1.33)' :: vector3 would be more intuitive. Let's define operators for this.

User Operators

PostgreSQL has the ability to define its own operators using a sequence of characters + - * / <> = ~! @ #% ^ & | `? with a maximum length of 63. They are unary or binary. You can create overloaded operators that have the same name but different arguments. Here are some important operator parameters (for unary operators, you only need to specify leftarg or rightarg ):

leftarg - the type of the left argument
rightarg - the type of the right argument
procedure is a function that has one (unary operator) or two parameters (binary operator) with types that correspond to the leftarg and rightarg operator
commutator - the hint to the optimizer that the expression x A y is equivalent to y B x , where A is the operator being declared, B is the operator for commutator
negator is a hint to the optimizer that the expression x A y is equivalent ! (x B y) , where A is the operator being declared, B is the operator for negator and both operators must return boolean

Create several statements:

SQL code to create statements

 --   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 );

And check their performance:

 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)

Already better. By the way, we have declared two operators for multiplication with a scalar type argument, for the case when the scalar is on the left of the operator and when it is on the right. And likewise for division. Therefore, we needed a pair of functions vector3_mul_left / vector3_mul_right and vector3_div_left / vector3_div_right.

The question may arise: how to get access to the components of the vector? One could declare three C functions vector3_x, vector3_y and vector3_z, which would return each component, but there is a better way.

Cast

Another notable feature in PostgreSQL is the creation of custom type casting. If both types have the same internal representation (for example, varchar and text), then there is no need for a function for type conversion. Otherwise, this function must be defined. It must return the type to which the conversion occurs and can have from one to three parameters:

type which is given
integer - the modifier associated with the type to which the cast occurs or -1
boolean - true if the cast is explicit, false otherwise

A cast can participate only in an assignment context or in any context. This behavior is indicated by the AS ASSIGNMENT and AS IMPLICIT parameters. The WITH INOUT parameter instructs to use input / output functions for the cast.

Define a new vector3c composite type and cast vector3 to it (and vice versa):

 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 ), ',' ); vx := s[1]; vy := s[2]; vz := 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;

In the vector3_cast_vector3c function , we first cast vector3 to the text, remove the first and last brackets, and then, using a comma separator, convert to an array of three elements, from which we take the components of the vector. In vector3c_cast_vector3 , for clarity, you can immediately convert vector3c to text and then result in vector3 (the text view for vector3c and vector3 has the same look).

Check the type conversion:

 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)

Creating an extension

When everything is ready and tested, it remains to wrap our library in the extension. Collect all C-code in one file:

The math3d.c file (source C-code of the dynamically loaded extension library 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); }

We compile the dynamically loaded library itself and place it in the PGDIR / lib directory. Similarly, create a file with SQL code:

File math3d - 1.0.sql (SQL script for math3d extension)

 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 ), ',' ); vx := s[1]; vy := s[2]; vz := 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;

The file name should be in the form <extension_name> - <version> .sql. The version we will have is 1.0. Place this file in the PGDIR / share / extension directory. Notice that the name of the dynamically loaded library in the function declarations has changed to the variable MODULE_PATHNAME, which will be declared in the extension control file. Create this file:

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

Among the options available are the following:

default_version - default version
comment - comment
encoding - script encoding if database encoding is not specified
module_pathname is the name of the dynamically loaded library (it is inserted into the MODULE_PATHNAME variable in the script)
requires - a list of extensions that the current one depends on
relocatable - if true, the extension is not tied to a specific scheme and objects, after creation, can be moved to another scheme (false by default)
schema - the schema in which extension objects are created (parameter has value if relocatable is set to false)

The name of the control file should be in the form <extension_name> .control, in this case math3d.control. Place it in the directory PGDIR / share / extension. In principle, the extension is ready for use.

Create a new database, connect to it and load our extension:

 CREATE EXTENSION math3d;

If there are no problems, you can use our new type vector3 - declare as a field of a table or a composite type, use functions in parameters and in other places. Deleting an extension and dependent objects is done in the same way:

 DROP EXTENSION math3d;

An extension may contain configuration tables that are modified after the extension is installed. Since ordinary tables created in the extension script and its data do not fall into the dump, the configuration tables must be specially marked:

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

The second parameter pg_catalog.pg_extension_config_dump can contain a condition that filters the data falling into the dump, for example, 'WHERE username =' 'administrator' ''. In our case, there is no need for configuration tables.

When upgrading an extension to a newer version, a script is created that has a name in the form <extension_name> - <old_version> - <new_version> .sql, which contains SQL commands for updating. If we wanted to update math3d to version 1.1, we would need to create the file math3d - 1.0--1.1.sql and execute the SQL command in the database:

 ALTER EXTENSION math3d UPDATE TO '1.1'

The remaining commands for changing the extension (described in more detail here ):

 ALTER EXTENSION <_> SET SCHEMA < >; --      ALTER EXTENSION <_> ADD <>; --     ALTER EXTENSION <_> DROP <>; --

Another good thing about an extension is that you can't accidentally delete an object that is part of an extension with a normal command (DROP TABLE, DROP FUNCTION, etc.).

Conclusion

If your database actively uses functionality based on stored functions and updatable views, it can be extended to an extension, getting some separation from other objects in the database and simplifying extensibility.

PS Thank you for your attention.

References:

PostgreSQL Official Documentation

Source: https://habr.com/ru/post/198332/

All Articles