Creating extensions in PostgreSQL


Hello, habrachelovek! The theme of this article is to create extensions for PostgreSQL. As an example, we implement a small library for working with 3D vectors. In parallel will be considered user-defined types, operators, and casts. Will not be superfluous oznakomitsya with this material, since the implementation of stored functions would in C. I Hope the elephants will help to brighten up the grey text.

Description



Extension in the PostgreSQL is a collection of multiple SQL objects (data types, functions, operators), combined in a script, dynamically loadable library (if necessary) and the control file, which specifies the name of the script, the library path, the default version and other options. Using extensions allows you to easily deploy additional logic in the DB, do the migration to a newer version and, if you remove the extension properly delete the dependent objects.

We need to create a new data type vector3 and determine the following operations:
the
    the
  • vector addition
    • the
  • subtraction of vectors
    • the
  • multiplication of a vector by a scalar
    • the
  • dot product
    • the
  • cross product
    • the
  • find the length of the vector
    • the
  • normalizing vectors
    • the
  • definition of distance between vectors

To improve performance, we write all the logic in the C language and take the form of dynamically loadable libraries math3d. Also, where it will intuitively create operators. And, finally, wrap it all in the extension.

Creation



DBMS PostgreSQL allows to define, in addition to composite types, enumerations and types-ranges, data types. The latter require the implementation of functions working with type on a lower-level language than SQL, as a rule, on C. define a custom type requires at least two functions: input and output. The input function takes a single parameter of type C-string (array of bytes ending with a 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) representation in the internal representation and Vice versa.

Some of the type parameters from DB:
the

    internallength — the size of the internal representation

    alignment — alignment, valid values are 1, 2, 4, 8 bytes

    storage location selection, plain (the only option for types with a fixed size) for storing uncompressed, extended allow compression and movement outside the row of the table, where the declared type of main allows compression, but prevents movement

    receive — the feature for binary I/o the

  • send — the submit function for the binary I/o

Define in the source file math3d.c type vector3, function text input/output functions and binary I/o for this type:
the 
#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));
}

Agree that the text representation of the type vector3 will be in the form "(x,y,z)" where x, y, z, in fact, the components of the vector. In the function vector3_in from the argument of type C-string, using sscanf is extracted and the components of the vector created by the vector (or rather a pointer to it) will be returned as the function result. In vector3_out is the reverse effect — convert vector to a string and return it.

Now let's move to the console will collect the dynamic link library of math3d and place it in the directory $libdir (to find out which one by running pg_config --by the pkglibdir):
the 
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 a vector3 type in the database:
the 
CREATE TYPE vector3;

CREATE OR REPLACE FUNCTION vector3_in ( cstring s )
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
);

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

Execute a test query:
the 
SELECT '(0.0,1.0,0.0)'::vector3; -- (0.000000,1.000000,0.000000) type vector3

vector Operations



Type created, but more than bringing to mind the text and Vice versa, he is not yet capable of. To make it more functional, enhancing its required operations:

the Implementation of operations in math3d.c
PG_FUNCTION_INFO_V1(vector3_minus); // unary minus
PG_FUNCTION_INFO_V1(vector3_add); // vector addition
PG_FUNCTION_INFO_V1(vector3_sub); // subtraction of vectors
PG_FUNCTION_INFO_V1(vector3_mul_left); // multiplication of a vector by a scalar
PG_FUNCTION_INFO_V1(vector3_mul_right); // multiplication of scalar by a vector
PG_FUNCTION_INFO_V1(vector3_div_left); // division of a vector by a scalar
PG_FUNCTION_INFO_V1(vector3_div_right); // divide a scalar by a vector

PG_FUNCTION_INFO_V1(vector3_equal); // checking vectors for equality
PG_FUNCTION_INFO_V1(vector3_not_equal); // test vectors for inequality

PG_FUNCTION_INFO_V1(vector3_dot); // scalar product
PG_FUNCTION_INFO_V1(vector3_cross); // cross product

PG_FUNCTION_INFO_V1(vector3_length); // vector length
PG_FUNCTION_INFO_V1(vector3_normalize); // normalization vector
PG_FUNCTION_INFO_V1(vector3_distance); // distance between vectors

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


Recall that for memory allocation you should use palloc, to release, respectively, pfree. Why you need a couple of functions vector3_mul_left/vector3_mul_right and vector3_div_left/vector3_div_right will be explained next.

Will personelem the math3d library.so and create these functions in the database in a new session to the server PostgreSQL uploaded a new version of the library:

SQL code to create transactions
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;


You can now do various operations on a vector:
the 
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 he doesn't look very good, for example, to multiply a scalar by a vector intutive would be a record 5.0 * '(0.2,0.2,1.33)'::vector3. Let's do this, let us define the operators.

Custom operators



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

    leftarg — the type of the left argument

    rightarg — type of the right argument

    procedure — a function with one (unary operator) or two parameters (binary operator) with the types, the corresponding leftarg and rightarg operator

    negator — a hint to the optimizer that the expression x A y is equivalent to !(x B y), where A is the declared operator, B — operator for negator, and both operators should return boolean


Create multiple operators:
SQL code to create operators
-- unary minus
CREATE OPERATOR -
(
rightarg = vector3,
procedure = vector3_minus
);

-- addition of vectors
CREATE OPERATOR +
(
leftarg = vector3,
rightarg = vector3,
procedure = vector3_add,
commutator = +
);

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

-- multiplication of a vector by a scalar
CREATE OPERATOR *
(
leftarg = vector3,
rightarg = double precision,
procedure = vector3_mul_left
);

- multiplying a scalar by a vector
CREATE OPERATOR *
(
leftarg = double precision,
rightarg = vector3,
procedure = vector3_mul_right
);

-- the division of a vector by a scalar
CREATE OPERATOR /
(
leftarg = vector3,
rightarg = double precision,
procedure = vector3_div_left
);

- the division of a scalar by a vector
CREATE OPERATOR /
(
leftarg = double precision,
rightarg = vector3,
procedure = vector3_div_right
);

- checking vectors for equality
CREATE OPERATOR =
(
leftarg = vector3,
rightarg = vector3,
procedure = vector3_equal
);

-- test vectors for inequality
CREATE OPERATOR !=
(
leftarg = vector3,
rightarg = vector3,
procedure = vector3_not_equal
);

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

-- cross product
CREATE OPERATOR **
(
leftarg = vector3,
rightarg = vector3,
procedure = vector3_cross
);


And check out their performance:
the 
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)

Better. By the way, we have stated two operators for multiplication with the type argument is a scalar, when the scalar to the left of the operator and when he is on the right. And similarly for division. So we took a couple of functions vector3_mul_left/vector3_mul_right and vector3_div_left/vector3_div_right.

The question may arise: how to access the components of the vector? It would be possible to declare the three C-functions vector3_x, vector3_y and vector3_z that would vozrastali each component, but there is a better way.

Casting



Another notable opportunity in PostgreSQL is a custom cast types. If both types have the same internal representation (for example, varchar, and text), it is necessary in functions to convert types no. Otherwise, this function needs to be defined. It should return the type to which you are cast and can have from one to three parameters:
the
    the
  • given

    • integer — the modifier associated with the type which is cast or -1

    boolean — true if the cast explicitly, otherwise false.


The cast will feature only the context of the assignment or in any context. This behavior is specified using the parameters AS ASSIGNMENT and AS IMPLICIT. The WITH INOUT specifies to use the functions of I/o to bring.

Define the new composite type vector3c and bringing to it the type of vector3 (and Vice versa):
the 
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
vector3 v;
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 function vector3_cast_vector3c we are first given vector3 to the text, remove the first and last brackets and then using the comma delimiters, are converted to an array of three elements, which take the components of the vector. In vector3c_cast_vector3, for clarity, you can immediately convert vector3c in the text and then lead to a vector3 (the text representation for vector3c and vector3 has the same view).
Check the cast:
the 
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)


Create extension



When everything is ready and tested, left to wrap our biblitekami in the expansion. Collect all code in one file:
File math3d.c (With source code dynamic link library of math3d extension)
#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);
}


Gather itself is a dynamically loaded library and place it in the directory of PGDIR/lib. Similarly, create a file with SQL code:
File math3d--1.0.sql (SQL script for the extension of math3d)
CREATE TYPE vector3;

CREATE OR REPLACE FUNCTION vector3_in ( cstring s )
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
vector3 v;
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 must be in the form of <imarengiaye>--<version>.sql. The version we have is 1.0. Place this file in the directory of PGDIR/share/extension. Note that the name of the dynamically loadable library function declarations changed to a variable MODULE_PATHNAME, which will be declared in the control extension file. Create this file:
the 
# math3d extension
comment = '3D mathematics'
default_version = '1.0'
module_pathname = '$libdir/math3d'
relocatable = true

Among the available parameters include the following:
the

    default_version — the default version

    comment — comment

    encoding — the encoding of the script if not specified is the DB encoding

    module_pathname — the name of the dynamically loadable library (is substituted into the variable MODULE_PATHNAME in the script)

    requires — list of extensions depends on the current

    relocatable — if true, the extension is not tied to a specific schema and objects, once created, can be moved to a different schema (defaults to false)


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

Create a new database, connect to it and download our extension:
the 
CREATE EXTENSION math3d;

If there are no problems, you can use our new type vector3 is declared as a field in the table or composite type, to use in function parameters and in other places. To uninstall the extension and its dependent objects is done the same with the command:
the 
DROP EXTENSION math3d;

An extension can contain configuration tables that are modified after installing the extension. As usual the tables you create in the script of the extension and its data is not stored in the dump configuration table, you need a special way to mark:
the 
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 may contain a condition that filters the data, papadema in the dump, for example, 'WHERE username = "administrator"'. In our case, there is no need in the configuration table.

When you upgrade the extension to newer version of the script is created with name in the form <imarengiaye>--<staravia>--<novagerio>.sql, which contains SQL commands to update. If we wanted to upgrade to math3d to version 1.1, we would need to create a file math3d--1.0--1.1.sql and execute the SQL command in the database:
the 
ALTER EXTENSION math3d UPDATE TO '1.1'

The rest of the team to change the extensions (explained in detail here):
the 
ALTER EXTENSION <imarengiaye> SET SCHEMA < new schema>; -- move extensions to the specified schema
ALTER EXTENSION <imarengiaye> ADD <object>; -- add an object to the extension
ALTER EXTENSION <imarengiaye> DROP <object>; -- delete the object from the extension

Another good thing about the extension is that not a normal command (DROP TABLE, DROP FUNCTION, etc.) accidentally delete an object that is part of the extension.

Opinion



If Your database is actively used functionality based on stored functions and updatable views, it can be taken in extension, having some isolation from other objects in the database, and simplifying scalability.

P. S. Thank you for your attention.

Links:
the
Article based on information from habrahabr.ru

Комментарии

Отправить комментарий

Популярные сообщения из этого блога

The release of the new version of the module modLivestreet 0.3.0-rc

Изображение
Continuing our theme of the binding module MODX and Livestreet, present a new version of the module modLivestreet: livestreet-0.3.0-rc.transport.zip What's new? Yes, almost all :-) the Module is rewritten from scratch. 1. Seriously changed (improved) the logic of processing requests LiveStreet. 2. Added synchronized registration of users in MODX and LiveStreet. Now registering user through the admin panel of MODX, the user is automatically created in LiveStreet, and processing the request for LiveStreet on the user registration, the registration is using MODX, which in turn also provides for the simultaneous registration of the user in both engines. This feature can be disabled through configuration. Under the cut diagram (simplified) MODX in the standard version and module modLivestreet and a more detailed description of the synchronous registration of users in MODX and Livestreet (Scheme, how has the registration in the MODX upload later), as well as the sourc
Далее...

mSearch: search + filter for MODX Revolution

Изображение
Something blog MODX habré completely withered, we need to spice things up. I want to introduce you to your component, which has recently been almost completely rewritten and expanded. It's called mSearch and originally conceived as a simple site search with account of morphology of the Russian language. That is, you had a quick and easy solution for any site without installing Sphinx and other major systems. In the process gugleniya, I came across a interesting this task by phpMorphy . Full text search on a table with an index, with the generation of different word forms. The idea I liked, by my criteria it came up and I wrote my solution, basing this method. the mSearch This snippet and plugin. The snippet is looking for the plug-in index documents when saving. Installation, as usual, from the repository, in 4 clicks. At the same time, you will be prompted to download and install for phpMorphy dictionaries of the Russian language — do not give up.
Далее...

Emulator data from GNSS receiver NMEA

Изображение
Introduction I had the need to test an application that uses the GNSS data NMEA. was Due to the fact that worked on the project navigation of the aircraft, test the air expensive naturally on earth to drive a car with a GNSS receiver is not particularly convenient, that like sitting at the table to have a virtual parallel port data Protocol NMEA allegedly moving equipment. First, search for different software had to try and find something suitable, but most paid and control of the emulation data is not very convenient, although practically emulate all settings of the NMEA standard. But I needed something simple emulating coordinates, speed, in principle, nothing more was required quite convenient and logical control. So I had to write an app of this kind in C#. Fly_nmea NMEA ("National Marine Electronics Association") — the full name is "NMEA 0183" is a text communication Protocol, marine (typically navigation) equipment together. Data is transmit
Далее...