Creating Extensions in PostgreSQL


    Hello, habracheloveki! The topic of this article will be creating 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 stored functions will be in the C language. I hope elephant friends will help 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 as a script, a dynamically loaded library (if necessary) and a control file that indicates the script name, library path, default version, etc. options. Using extensions allows you to easily deploy additional logic in the database, migrate to a newer version and, when removing an extension, correctly delete dependent objects.

    We need to create a new vector3 data type and define the following operations:
    • vector addition
    • subtraction of vectors
    • scalar vector multiplication
    • scalar product
    • vector product
    • finding the length of the vector
    • vector normalization
    • determination of the distance between vectors

    To improve performance, we will write all the logic in C and format it in the form of a dynamically loaded math3d library. Also, where it will be intuitive, we will create operators. Well, finally, let's wrap it all up 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 that work with the type in a lower level language than SQL, as a rule, in C. The definition of a user type requires at least two functions: input and output. The input function has one parameter of type C-string (an array of bytes ending in zero) and returns a user-defined type. The output function has a parameter with a custom type and returns a C-string. These functions are required for conversion from an external (text) display to an internal representation and vice versa.

    Some type parameters from the DBMS side:
    • internallength - size of the internal representation
    • alignment - alignment, valid values ​​are 1, 2, 4, 8 bytes
    • storage - select storage, plain (the only possible option for types with a fixed size) for storage without compression, extended allows compression and movement outside the row of the table 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, define the type vector3, the text input / output functions and the 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;
    #endiftypedefstruct
    {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 type vector3 will be in the form of "(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, such as a C-string, using sscanf and the created vector (more precisely, a pointer to it) is returned as the result of the function. In vector3_out , the opposite effect occurs - the conversion of the vector to a string and its return.

    Now let's go to the console, collect the dynamically loaded math3d library and put it in the $ libdir directory (which 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 a vector3 type in the database:
    CREATETYPE vector3;
    CREATEORREPLACEFUNCTION vector3_in ( s cstring )
    RETURNS vector3 AS'math3d', 'vector3_in'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_out ( v vector3 )
    RETURNS cstring AS'math3d', 'vector3_out'LANGUAGE C IMMUTABLE STRICT;
    CREATEFUNCTION vector3_recv ( p internal )
    RETURNS vector3 AS'math3d', 'vector3_recv'LANGUAGE C IMMUTABLE STRICT;
    CREATEFUNCTION vector3_send ( v vector3 )
    RETURNS bytea AS'math3d', 'vector3_send'LANGUAGE C IMMUTABLE STRICT;
    CREATETYPE vector3
    (
    	internallength = 24,
    	input = vector3_in,
    	output = vector3_out,
    	receive = vector3_recv,
    	send = vector3_send
    );
    

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

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

    Vector operations



    The type is created, but for more than the reduction to the text form and vice versa, it is not yet capable. Let's make it more functional, 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 to allocate memory , to free pfree , respectively . Why the pairs of functions vector3_mul_left / vector3_mul_right and vector3_div_left / vector3_div_right are needed will be explained later.

    We 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 to create operations
    CREATEORREPLACEFUNCTION vector3_minus ( v0 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_minus'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_add ( v0 vector3, v1 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_add'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_sub ( v0 vector3, v1 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_sub'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_mul_left ( v0 vector3, k doubleprecision )
    RETURNS vector3 AS'math3d', 'vector3_mul_left'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_mul_right ( k doubleprecision, v0 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_mul_right'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_div_left ( v0 vector3, k doubleprecision )
    RETURNS vector3 AS'math3d', 'vector3_div_left'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_div_right ( k doubleprecision, v0 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_div_right'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_equal ( v0 vector3, v1 vector3 )
    RETURNSbooleanAS'math3d', 'vector3_equal'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_not_equal ( v0 vector3, v1 vector3 )
    RETURNSbooleanAS'math3d', 'vector3_not_equal'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_dot ( v0 vector3, v1 vector3 )
    RETURNSdoubleprecisionAS'math3d', 'vector3_dot'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_cross ( v0 vector3, v1 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_cross'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTIONlength ( v0 vector3 )
    RETURNSdoubleprecisionAS'math3d', 'vector3_length'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION normalize ( v0 vector3 )
    RETURNS vector3 AS'math3d', 'vector3_normalize'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION distance ( v0 vector3, v1 vector3 )
    RETURNSdoubleprecisionAS'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)SELECTlength ( '(0.705,0.705,0.0)'::vector3 ); -- 0.9970206

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

    User statements



    In PostgreSQL has the ability to determine their operators , using the sequence of the symbols + - * / <> = ~! @ #% ^ & | `? with a maximum length of 63. They are unary or binary. You can create overloaded statements that have the same name but different arguments. Here are some important parameters of the operator (for unary operators you need to specify only leftarg or rightarg ):
    • leftarg - type of left argument
    • rightarg - type of right argument
    • procedure - a function that has one (unary operator) or two parameters (binary operator) with types corresponding to the leftarg and rightarg operator
    • commutator - hint to the optimizer that the expression x A y is equivalent to y B x , where A is the declared operator, B is the operator for commutator
    • negator - hint to the optimizer that the expression x A y is equivalent ! (x B y) , where A is the operator to declare, B is the operator for negator, and both operators must return boolean

    Let's create some operators:
    SQL code to create statements
    -- унарный минусCREATEOPERATOR -
    (
        rightarg = vector3,
        procedure = vector3_minus
    );
    -- сложение векторовCREATEOPERATOR +
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_add,
        commutator = +
    );
    -- вычитание векторовCREATEOPERATOR -
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_sub
    );
    -- умножение вектора на скалярCREATEOPERATOR *
    (
        leftarg = vector3,
        rightarg = doubleprecision,
        procedure = vector3_mul_left
    );
    -- умножение скаляра на векторCREATEOPERATOR *
    (
        leftarg = doubleprecision,
        rightarg = vector3,
        procedure = vector3_mul_right
    );
    -- деление вектора на скалярCREATEOPERATOR /
    (
        leftarg = vector3,
        rightarg = doubleprecision,
        procedure = vector3_div_left
    );
    -- деление скаляра на векторCREATEOPERATOR /
    (
        leftarg = doubleprecision,
        rightarg = vector3,
        procedure = vector3_div_right
    );
    -- проверка векторов на равенствоCREATEOPERATOR =
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_equal
    );
    -- проверка векторов на неравенствоCREATEOPERATOR !=
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_not_equal
    );
    -- скалярное произведениеCREATEOPERATOR *
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_dot
        commutator = 
    );
    -- векторное произведениеCREATEOPERATOR **
    (
        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)SELECT5.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.1312SELECT'(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 declared two operators for multiplication with an argument of the scalar type, for the case when the scalar is on the left of the operator and when it is on the right. And similarly for division. Therefore, we needed pairs 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? 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 noteworthy feature in PostgreSQL is the creation of custom casts . 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 cast occurs and can have from one to three parameters:
    • type which is given
    • integer - the modifier associated with the type to which the conversion occurs or -1
    • boolean - true if cast is explicit; otherwise false

    A cast can only participate in an assignment context or in any context. This behavior is specified using the AS ASSIGNMENT and AS IMPLICIT parameters. The WITH INOUT parameter specifies the use of I / O functions for the cast.

    Define a new composite type vector3c and cast to it type vector3 (and vice versa):
    CREATETYPE vector3c AS
    (
    	x doubleprecision,
    	y doubleprecision,
    	z doubleprecision
    );
    CREATEORREPLACEFUNCTION 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;
    CREATEORREPLACEFUNCTION vector3c_cast_vector3 ( v0 vector3c )
    RETURNS vector3 AS
    $BODY$
    DECLARE
    	v vector3;
    BEGIN
    	v := v0::text;
    	RETURN v;
    END
    $BODY$
    LANGUAGE plpgsql IMMUTABLE;
    CREATECAST ( vector3 AS vector3c )
    WITHFUNCTION  vector3_cast_vector3c ( v0 vector3 )
    AS IMPLICIT;
    CREATECAST ( vector3c AS vector3 )
    WITHFUNCTION  vector3c_cast_vector3 ( v0 vector3c )
    AS IMPLICIT;
    

    In the vector3_cast_vector3c function , we first cast vector3 to text, remove the first and last brackets, and then, using a comma delimiter, 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 cast to vector3 (the textual representation for vector3c and vector3 has the same form).

    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)


    Create extension



    When everything is ready and tested, it remains to wrap our library in an extension. Let's collect all the C-code in one file:
    The math3d.c file (source code for the dynamically loaded math3d extension library)
    #include<postgres.h>#include<fmgr.h>#include<libpq/pqformat.h>#include<math.h>#ifdef PG_MODULE_MAGIC
    	PG_MODULE_MAGIC;
    #endif// typestypedefstruct
    {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);
    // implementationDatum 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);
    }
    


    Let's collect the dynamically loaded library itself and put it in the PGDIR / lib directory. Similarly, create a file with SQL code:
    The math3d file is 1.0.sql (SQL script for the math3d extension)
    CREATETYPE vector3;
    CREATEORREPLACEFUNCTION vector3_in ( s cstring )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_in'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_out ( v vector3 )
    RETURNS cstring AS'MODULE_PATHNAME', 'vector3_out'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_recv ( p internal )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_recv'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION vector3_send ( v vector3 )
    RETURNS bytea AS'MODULE_PATHNAME', 'vector3_send'LANGUAGE C IMMUTABLE STRICT;
    CREATETYPE vector3
    (
    	internallength = 24,
    	input = vector3_in,
    	output = vector3_out,
    	receive = vector3_recv,
    	send = vector3_send
    );
    CREATEORREPLACEFUNCTION vector3_minus ( v0 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_minus'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR -
    (
        rightarg = vector3,
        procedure = vector3_minus
    );
    CREATEORREPLACEFUNCTION vector3_add ( v0 vector3, v1 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_add'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR +
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_add,
        commutator = +
    );
    CREATEORREPLACEFUNCTION vector3_sub ( v0 vector3, v1 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_sub'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR -
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_sub
    );
    CREATEORREPLACEFUNCTION vector3_mul_left ( v0 vector3, k doubleprecision )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_mul_left'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR *
    (
        leftarg = vector3,
        rightarg = doubleprecision,
        procedure = vector3_mul_left,
        commutator = *
    );
    CREATEORREPLACEFUNCTION vector3_mul_right ( k doubleprecision, v0 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_mul_right'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR *
    (
        leftarg = doubleprecision,
        rightarg = vector3,
        procedure = vector3_mul_right,
        commutator = *
    );
    CREATEORREPLACEFUNCTION vector3_div_left ( v0 vector3, k doubleprecision )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_div_left'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR /
    (
        leftarg = vector3,
        rightarg = doubleprecision,
        procedure = vector3_div_left
    );
    CREATEORREPLACEFUNCTION vector3_div_right ( k doubleprecision, v0 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_div_right'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR /
    (
        leftarg = doubleprecision,
        rightarg = vector3,
        procedure = vector3_div_right
    );
    CREATEORREPLACEFUNCTION vector3_equal ( v0 vector3, v1 vector3 )
    RETURNSbooleanAS'MODULE_PATHNAME', 'vector3_equal'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR =
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_equal
    );
    CREATEORREPLACEFUNCTION vector3_not_equal ( v0 vector3, v1 vector3 )
    RETURNSbooleanAS'MODULE_PATHNAME', 'vector3_not_equal'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR !=
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_not_equal
    );
    CREATEORREPLACEFUNCTION vector3_dot ( v0 vector3, v1 vector3 )
    RETURNSdoubleprecisionAS'MODULE_PATHNAME', 'vector3_dot'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR *
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_dot,
        commutator = *
    );
    CREATEORREPLACEFUNCTION vector3_cross ( v0 vector3, v1 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_cross'LANGUAGE C IMMUTABLE STRICT;
    CREATEOPERATOR **
    (
        leftarg = vector3,
        rightarg = vector3,
        procedure = vector3_cross,
        commutator = **
    );
    CREATEORREPLACEFUNCTIONlength ( v0 vector3 )
    RETURNSdoubleprecisionAS'MODULE_PATHNAME', 'vector3_length'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION normalize ( v0 vector3 )
    RETURNS vector3 AS'MODULE_PATHNAME', 'vector3_normalize'LANGUAGE C IMMUTABLE STRICT;
    CREATEORREPLACEFUNCTION distance ( v0 vector3, v1 vector3 )
    RETURNSdoubleprecisionAS'MODULE_PATHNAME', 'vector3_distance'LANGUAGE C IMMUTABLE STRICT;
    CREATETYPE vector3c AS
    (
    	x doubleprecision,
    	y doubleprecision,
    	z doubleprecision
    );
    CREATEORREPLACEFUNCTION 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;
    CREATEORREPLACEFUNCTION vector3c_cast_vector3 ( v0 vector3c )
    RETURNS vector3 AS
    $BODY$
    DECLARE
    	v vector3;
    BEGIN
    	v := v0::text;
    	RETURN v;
    END
    $BODY$
    LANGUAGE plpgsql IMMUTABLE;
    CREATECAST ( vector3 AS vector3c )
    WITHFUNCTION  vector3_cast_vector3c ( v0 vector3 )
    AS IMPLICIT;
    CREATECAST ( vector3c AS vector3 )
    WITHFUNCTION  vector3c_cast_vector3 ( v0 vector3c )
    AS IMPLICIT;
    


    The file name should be in the form <extension_name> - <version> .sql. We will have a version 1.0. Place this file in the PGDIR / share / extension directory. Note that the name of the dynamically loaded library in function declarations has changed to the MODULE_PATHNAME variable, 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 available options are the following:
    • default_version - default version
    • comment - comment
    • encoding - script encoding, if not specified, database encoding is used
    • module_pathname - name of the dynamically loaded library (substituted into the MODULE_PATHNAME variable in the script)
    • requires - list of extensions the current depends on
    • relocatable - if true, then the extension does not bind to a specific scheme and objects, after creation, can be moved to another scheme (false by default)
    • schema - the scheme in which extension objects are created (the parameter matters 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. Put it in the PGDIR / share / extension directory. Basically, the extension is ready to 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 vector3 type - declare it as a table or composite type field, use functions in parameters, and elsewhere. Removing the extension and dependent objects is done by a similar command:
    DROP EXTENSION math3d;
    

    An extension may contain configuration tables that are modified after the extension is installed. Since regular tables created in the extension script and its data do not fall into the dump, the configuration tables must be specially marked:
    CREATETABLE user_setting ( username text, keytext, valuetext );
    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 that falls 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 a math3d file - 1.0--1.1.sql and execute the SQL command in the database:
    ALTER EXTENSION math3d UPDATETO'1.1'

    The remaining commands for changing the extension (described in more detail here ):
    ALTER EXTENSION <имя_расширения> SETSCHEMA <новая схема>; -- перемещение расширения в указанную схемуALTER EXTENSION <имя_расширения> ADD <объект>; -- добавление объекта в расширениеALTER EXTENSION <имя_расширения> DROP <объект>; -- удаление объекта из расширения

    Another good thing in the extension is that you cannot accidentally delete the object that is part of the extension with the usual command (DROP TABLE, DROP FUNCTION, etc.).

    Conclusion



    If functionality based on stored functions and updated views is actively used in your database, it can be taken out into the extension, having received some isolation from other objects in the database and simplified extensibility.

    PS Thank you for your attention.

    References:

    Also popular now: