Use the dynamic link library written in C to add custom functions to the PostgreSQL database

I have to say that at this point, PostgreSQL is very powerful and no less powerful than any large commercial database. It is not that complicated to add custom functions.

The following example adds the following functions: Test (), concat_text (string, string). If this is not an example, you can try it by yourself.

1. The Code is as follows:

/* $PostgreSQL: pgsql/src/tutorial/funcs_new.c,v 1.13 2007/02/27 23:48:10 tgl Exp $ *//******************************************************************************  These are user-defined functions that can be bound to a Postgres backend  and called by Postgres to execute SQL functions of the same name.  The calling format for these functions is defined by the CREATE FUNCTION  SQL statement that binds them to the backend.  NOTE: this file shows examples of "new style" function call conventions.  See funcs.c for examples of "old style".*****************************************************************************/#include "postgres.h"/* general Postgres declarations */#include "executor/executor.h"/* for GetAttributeByName() */#include "utils/geo_decls.h"/* for point type */#include "executor/spi.h"PG_MODULE_MAGIC;/* These prototypes just prevent possible warnings from gcc. */////Datumadd_one(PG_FUNCTION_ARGS);//Datumadd_one_float8(PG_FUNCTION_ARGS);//Datummakepoint(PG_FUNCTION_ARGS);//Datumcopytext(PG_FUNCTION_ARGS);//Datumconcat_text(PG_FUNCTION_ARGS);//Datumc_overpaid(PG_FUNCTION_ARGS);/* By Value */PG_FUNCTION_INFO_V1(add_one);__declspec (dllexport) Datumadd_one(PG_FUNCTION_ARGS){int32arg = PG_GETARG_INT32(0);PG_RETURN_INT32(arg + 1);}/* By Reference, Fixed Length */PG_FUNCTION_INFO_V1(add_one_float8);__declspec (dllexport) Datumadd_one_float8(PG_FUNCTION_ARGS){/* The macros for FLOAT8 hide its pass-by-reference nature */float8arg = PG_GETARG_FLOAT8(0);PG_RETURN_FLOAT8(arg + 1.0);}PG_FUNCTION_INFO_V1(makepoint);__declspec (dllexport) Datummakepoint(PG_FUNCTION_ARGS){Point   *pointx = PG_GETARG_POINT_P(0);Point   *pointy = PG_GETARG_POINT_P(1);Point   *new_point = (Point *) palloc(sizeof(Point));new_point->x = pointx->x;new_point->y = pointy->y;PG_RETURN_POINT_P(new_point);}/* By Reference, Variable Length */PG_FUNCTION_INFO_V1(copytext);__declspec (dllexport) Datumcopytext(PG_FUNCTION_ARGS){text   *t = PG_GETARG_TEXT_P(0);/* * VARSIZE is the total size of the struct in bytes. */text   *new_t = (text *) palloc(VARSIZE(t));SET_VARSIZE(new_t, VARSIZE(t));/* * VARDATA is a pointer to the data region of the struct. */memcpy((void *) VARDATA(new_t),/* destination */   (void *) VARDATA(t), /* source */   VARSIZE(t) - VARHDRSZ);/* how many bytes */PG_RETURN_TEXT_P(new_t);}PG_FUNCTION_INFO_V1(concat_text);__declspec (dllexport) Datumconcat_text(PG_FUNCTION_ARGS){text   *arg1 = PG_GETARG_TEXT_P(0);text   *arg2 = PG_GETARG_TEXT_P(1);int32arg1_size = VARSIZE(arg1) - VARHDRSZ;int32arg2_size = VARSIZE(arg2) - VARHDRSZ;int32new_text_size = arg1_size + arg2_size + VARHDRSZ;text   *new_text = (text *) palloc(new_text_size);SET_VARSIZE(new_text, new_text_size);memcpy(VARDATA(new_text), VARDATA(arg1), arg1_size);memcpy(VARDATA(new_text) + arg1_size, VARDATA(arg2), arg2_size);PG_RETURN_TEXT_P(new_text);}/* Composite types */PG_FUNCTION_INFO_V1(c_overpaid);__declspec (dllexport) Datumc_overpaid(PG_FUNCTION_ARGS){HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);int32limit = PG_GETARG_INT32(1);boolisnull;int32salary;salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));if (isnull)PG_RETURN_BOOL(false);/* * Alternatively, we might prefer to do PG_RETURN_NULL() for null salary */PG_RETURN_BOOL(salary > limit);}PG_FUNCTION_INFO_V1(test);__declspec (dllexport)  Datum test(PG_FUNCTION_ARGS){       char *command;       int ret;       int proc;       command = "SELECT 1";       elog(INFO,"1");       SPI_connect();       elog(INFO,"2");       ret = SPI_exec(command, 1);       elog(INFO,"3");       proc = SPI_processed;       elog(INFO,"4");       if (ret > 0 && SPI_tuptable != NULL)       {           TupleDesc tupdesc = SPI_tuptable->tupdesc;           SPITupleTable *tuptable = SPI_tuptable;           char buf[8192];           int i, j;           elog(INFO,"5");           for (j = 0; j < proc; j++)           {               HeapTuple tuple = tuptable->vals[j];               elog(INFO,"6");               for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)               {                       snprintf(buf + strlen (buf), sizeof(buf) - strlen(buf), " %s%s",                                SPI_getvalue(tuple, tupdesc, i),                                (i == tupdesc->natts) ? " " : " |");                       elog(INFO,"7");               }               elog(INFO, "EXECQ: %s", buf);           }       }       SPI_finish();       // pfree(command);}

2. compilation process

Note that in the compilation process, you must add the header file path, lib path, and bin path to complete compilation and finally obtain func1.dll. In this example, the process is not complex. On the Linux platform, I believe it is simpler.

Take windows as an example, you can first download the entire example project from the http://download.csdn.net/detail/iihero/2979577, it should be vs2005 VC ++ project, add the following include path, the first with PG (9.0 or more) installation path:

Include \ Server
Include \ Server \ port \ Win32
Include \ Server \ port \ win32_msvc
Include \

Finally, copy func1.dll to $ {pg_home} \ bin.

3. Verify the created Function

Create Function mytest () returns varchar (256) as 'd:/pg921/bin/func1 ', 'test' Language C strict; Description: add an SQL function mytest without parameters, its implementation is test in func1.dll, which has a very clear meaning. Iihero = # create function mytest () returns varchar (256) iihero-# As 'd:/pg921/bin/func1 ', 'test' iihero-# Language C strict; create functioniihero = # select mytest (); Info: 1 info: 2 info: 3 info: 4 info: 5 info: 6 info: 7 Info: execq: 1 server closed the connection unexpectedly this probably means the server terminated abnormally before or while processing the request. the connection to the server was lost. ATT Empting Reset: succeeded. create Function myconcat (varchar (256), varchar (256) returns varchar (512) as 'd:/pg921/bin/func1 ', 'concat _ text' Language C strict; note: The add function myconcat contains two string parameters. The implementation of this function is concat_text in func1.dll. The meaning is very clear. Iihero = # create function myconcat (varchar (256), varchar (256) returns varchar (512) iihero-# As 'd:/pg921/bin/func1 ', 'concat _ text' iihero-# Language C strict; Create functioniihero = # select myconcat ('abc ###, ', 'bcd1234'); myconcat --------------- ABC ###, bcd1234 (1 row) iihero = #
In fact, in other commercial databases, at least Sybase ASE/ASA, Oracle supports this c function extension, which is written and may be more troublesome.