使用C编写的动态链接库为PostgreSQL数据库添加自定义函数
使用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. */
- //
- //Datum add_one(PG_FUNCTION_ARGS);
- //Datum add_one_float8(PG_FUNCTION_ARGS);
- //Datum makepoint(PG_FUNCTION_ARGS);
- //Datum copytext(PG_FUNCTION_ARGS);
- //Datum concat_text(PG_FUNCTION_ARGS);
- //Datum c_overpaid(PG_FUNCTION_ARGS);
- /* By Value */
- 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);
- }
- /* By Reference, Fixed Length */
- PG_FUNCTION_INFO_V1(add_one_float8);
- __declspec (dllexport) Datum
- add_one_float8(PG_FUNCTION_ARGS)
- {
- /* The macros for FLOAT8 hide its pass-by-reference nature */
- 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);
- }
- /* By Reference, Variable Length */
- PG_FUNCTION_INFO_V1(copytext);
- __declspec (dllexport) Datum
- copytext(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) 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);
- }
- /* Composite types */
- 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);
- /*
- * 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);
- }
|
评论暂时关闭