使用C編寫的動態連結程式庫為PostgreSQL資料庫添加自訂函數

不得不說，在這點上，PostgreSQL功能非常強大，不亞於任何家大型商務資料庫。要添加一些自訂函數，不是那麼複雜。

下邊的樣本會添加如下函數：test(), concat_text(string, string)  還有另外兩個，不作舉例，您可以自行嘗試

1. 代碼如下：

/* $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. 編譯過程

要注意的是編譯過程，需要添加標頭檔路徑，lib路徑，bin路徑，以完成編譯，最後得到func1.dll，以本例為例，過程並不複雜。在linux平台，相信更簡單。

以windows為例，您可以先從http://download.csdn.net/detail/iihero/2979577 下載整個樣本工程，應該是vs2005的vc++工程，添加如下include路徑，最前頭帶PG(9.0或以上)的安裝路徑：

include\server
include\server\port\win32
include\server\port\win32_msvc
include\

最後得到func1.dll，將其複製到${PG_HOME}\bin下邊。

3. 驗證建立的函數

create function mytest() RETURNs VARCHAR(256)AS 'd:/pg921/bin/func1', 'test'LANGUAGE C STRICT;說明： 添加SQL函數mytest, 不帶參數，它的實現是func1.dll中的test，含義非常明確。  iihero=# create function mytest() RETURNs VARCHAR(256)iihero-# AS 'd:/pg921/bin/func1', 'test'iihero-# LANGUAGE C STRICT;CREATE FUNCTIONiihero=# select mytest();INFO:  1INFO:  2INFO:  3INFO:  4INFO:  5INFO:  6INFO:  7INFO:  EXECQ:  1server closed the connection unexpectedly        This probably means the server terminated abnormally        before or while processing the request.The connection to the server was lost. Attempting reset: Succeeded.create function myconcat(varchar(256), varchar(256)) RETURNs VARCHAR(512)AS 'd:/pg921/bin/func1', 'concat_text'LANGUAGE C STRICT;說明： 添加函數myconcat, 帶兩個字串參數，它的實現是func1.dll中的concat_text，含義非常明確。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=#
其實，在其它商務資料庫裡，至少Sybase ASE/ASA, Oracle是支援這種C函數擴充的，就是編寫起來，可能更麻煩一些。