不得不说,在这点上,PostgreSQL功能非常强大,不亚于任何家大型商业数据库。要添加一些自定义函数,不是那么复杂。
下边的示例会添加如下函数:test(), concat_text(string, string) 还有另外两个,不作举例,您可以自行尝试
1. 代码如下:
-
-
-
-
-
-
-
-
-
-
-
-
-
-
#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;
-
-
-
-
-
-
-
-
-
-
-
-
-
-
PG_FUNCTION_INFO_V1(add_one);
-
-
__declspec (dllexport) Datum
-
add_one(PG_FUNCTION_ARGS)
-
{
-
int32 arg = PG_GETARG_INT32(0);
-
-
PG_RETURN_INT32(arg + 1);
-
}
-
-
-
-
PG_FUNCTION_INFO_V1(add_one_float8);
-
-
__declspec (dllexport) Datum
-
add_one_float8(PG_FUNCTION_ARGS)
-
{
-
-
float8 arg = PG_GETARG_FLOAT8(0);
-
-
PG_RETURN_FLOAT8(arg + 1.0);
-
}
-
-
PG_FUNCTION_INFO_V1(makepoint);
-
-
__declspec (dllexport) Datum
-
makepoint(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);
-
}
-
-
-
-
PG_FUNCTION_INFO_V1(copytext);
-
-
__declspec (dllexport) Datum
-
copytext(PG_FUNCTION_ARGS)
-
{
-
text *t = PG_GETARG_TEXT_P(0);
-
-
-
-
-
text *new_t = (text *) palloc(VARSIZE(t));
-
-
SET_VARSIZE(new_t, VARSIZE(t));
-
-
-
-
-
memcpy((void *) VARDATA(new_t),
-
(void *) VARDATA(t),
-
VARSIZE(t) - VARHDRSZ);
-
PG_RETURN_TEXT_P(new_t);
-
}
-
-
PG_FUNCTION_INFO_V1(concat_text);
-
-
__declspec (dllexport) Datum
-
concat_text(PG_FUNCTION_ARGS)
-
{
-
text *arg1 = PG_GETARG_TEXT_P(0);
-
text *arg2 = PG_GETARG_TEXT_P(1);
-
int32 arg1_size = VARSIZE(arg1) - VARHDRSZ;
-
int32 arg2_size = VARSIZE(arg2) - VARHDRSZ;
-
int32 new_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);
-
}
-
-
-
-
PG_FUNCTION_INFO_V1(c_overpaid);
-
-
__declspec (dllexport) Datum
-
c_overpaid(PG_FUNCTION_ARGS)
-
{
-
HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
-
int32 limit = PG_GETARG_INT32(1);
-
bool isnull;
-
int32 salary;
-
-
salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull));
-
if (isnull)
-
PG_RETURN_BOOL(false);
-
-
-
-
-
-
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();
-
-
-
}
2. 编译过程
要注意的是编译过程,需要添加头文件路径,lib路径,bin路径,以完成编译,最后得到func1.dll,以本例为例,过程并不复杂。在linux平台,相信更简单。
以windows为例,您可以先从 下载整个示例工程,应该是vs2005的vc++工程,添加如下include路径,最前头带PG(9.0或以上)的安装路径:
include\server
include\server\port\win32
include\server\port\win32_msvc
include\
最后得到func1.dll,将其复制到${PG_HOME}\bin下边。
3. 验证创建的函数
-
"code" class="java">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 FUNCTION
-
iihero=# 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. 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 FUNCTION
-
iihero=# select myconcat('abc###, ', 'bcd1234');
-
myconcat
-
-----------------
-
abc###, bcd1234
-
(1 row)
-
-
-
iihero=#
其实,在其它商业数据库里,至少Sybase ASE/ASA, Oracle是支持这种C函数扩展的,就是编写起来,可能更麻烦一些。
阅读(2227) | 评论(0) | 转发(0) |