由于种种原因,我需要把postgresql的服务器运行在windows环境下,并且,我需要向postgresql中添加自己的定义的c函数和数据类型。postgresql的中文文档有几个例子,我按照例子上的步骤,先把C源代码编译成DLL,然后用CREATE TYPE 和CREATE FUNCTION添加。这个时候问题出现了。首先,如果用VS2008编译,命名冲突。我解决的办法是,放弃VS2008。使用code::blocks+gcc编译。关键是编译器不用vc而改成gcc。编译顺利通过。创建函数的部分代码如下#define HAVE_LONG_INT_64#include "postgres.h" /* general Postgres declarations */#include "executor/executor.h" /* for GetAttributeByName() */
#include "utils/geo_decls.h" /* for point type */
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 concat_text(PG_FUNCTION_ARGS);/* By Value */PG_FUNCTION_INFO_V1(add_one);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);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);
}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);
}
内存的问题???concat_text()函数用到了palloc函数分配内存,但是add_one_float8()呢?我google了很久,中文e文能找的都找了http://www.spinics.net/lists/pgsql/msg80194.html这一篇曾今给我希望。我有按照他们讨论的内容,把我的代码改为
#define HAVE_LONG_INT_64
#define _USE_32BIT_TIME_T/* Ensure that Pg_module_function and friends are declared __declspec(dllexport) */
#ifndef BUILDING_MODULE
#define BUILDING_MODULE
#endif#include "postgres.h"
#include "fmgr.h"/*--------------- BEGIN REDEFINITION OF PG MACROS -------------------
*
* These rewritten versions of PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1
* declare the module functions as __declspec(dllexport) when building
* a module. They also provide PGMODULEEXPORT for exporting functions
* in user DLLs.
*/
#undef PG_MODULE_MAGIC
#undef PG_FUNCTION_INFO_V1#define PGMODULEEXPORT __declspec (dllexport)#define PG_MODULE_MAGIC \
PGMODULEEXPORT const Pg_magic_struct * \
PG_MAGIC_FUNCTION_NAME(void) \
{ \
static const Pg_magic_struct Pg_magic_data = PG_MODULE_MAGIC_DATA; \
return &Pg_magic_data; \
} \
extern int no_such_variable#define PG_FUNCTION_INFO_V1(funcname) \
PGMODULEEXPORT const Pg_finfo_record * \
CppConcat(pg_finfo_,funcname) (void) \
{ \
static const Pg_finfo_record my_finfo = { 1 }; \
return &my_finfo; \
} \
extern int no_such_variable/*--------------- END REDEFINITION OF PG MACROS -------------------*/
PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(add_one);PGMODULEEXPORT Datum add_one(PG_FUNCTION_ARGS)
{
int32 arg = PG_GETARG_INT32(0);
PG_RETURN_INT32(arg + 1);
}PG_FUNCTION_INFO_V1(add_one_float8);PGMODULEEXPORT 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(concat_text);PGMODULEEXPORT Datum concat_text(PG_FUNCTION_ARGS)
{
text *arg1 = PG_GETARG_TEXT_P(0);
text *arg2 = PG_GETARG_TEXT_P(1);
int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
text *new_text = (text *) palloc(new_text_size); SET_VARSIZE(new_text, new_text_size);
memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
PG_RETURN_TEXT_P(new_text);
}
再次编译,并且重新创建函数,问题依旧。我已经被这个问题困扰一周了,希望大家踊跃讨论,不吝赐教相信CSDN上的大侠们一定愿意帮助我这个新手,先谢谢各位...
#include "utils/geo_decls.h" /* for point type */
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 concat_text(PG_FUNCTION_ARGS);/* By Value */PG_FUNCTION_INFO_V1(add_one);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);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);
}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);
}
内存的问题???concat_text()函数用到了palloc函数分配内存,但是add_one_float8()呢?我google了很久,中文e文能找的都找了http://www.spinics.net/lists/pgsql/msg80194.html这一篇曾今给我希望。我有按照他们讨论的内容,把我的代码改为
#define HAVE_LONG_INT_64
#define _USE_32BIT_TIME_T/* Ensure that Pg_module_function and friends are declared __declspec(dllexport) */
#ifndef BUILDING_MODULE
#define BUILDING_MODULE
#endif#include "postgres.h"
#include "fmgr.h"/*--------------- BEGIN REDEFINITION OF PG MACROS -------------------
*
* These rewritten versions of PG_MODULE_MAGIC and PG_FUNCTION_INFO_V1
* declare the module functions as __declspec(dllexport) when building
* a module. They also provide PGMODULEEXPORT for exporting functions
* in user DLLs.
*/
#undef PG_MODULE_MAGIC
#undef PG_FUNCTION_INFO_V1#define PGMODULEEXPORT __declspec (dllexport)#define PG_MODULE_MAGIC \
PGMODULEEXPORT const Pg_magic_struct * \
PG_MAGIC_FUNCTION_NAME(void) \
{ \
static const Pg_magic_struct Pg_magic_data = PG_MODULE_MAGIC_DATA; \
return &Pg_magic_data; \
} \
extern int no_such_variable#define PG_FUNCTION_INFO_V1(funcname) \
PGMODULEEXPORT const Pg_finfo_record * \
CppConcat(pg_finfo_,funcname) (void) \
{ \
static const Pg_finfo_record my_finfo = { 1 }; \
return &my_finfo; \
} \
extern int no_such_variable/*--------------- END REDEFINITION OF PG MACROS -------------------*/
PG_MODULE_MAGIC;PG_FUNCTION_INFO_V1(add_one);PGMODULEEXPORT Datum add_one(PG_FUNCTION_ARGS)
{
int32 arg = PG_GETARG_INT32(0);
PG_RETURN_INT32(arg + 1);
}PG_FUNCTION_INFO_V1(add_one_float8);PGMODULEEXPORT 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(concat_text);PGMODULEEXPORT Datum concat_text(PG_FUNCTION_ARGS)
{
text *arg1 = PG_GETARG_TEXT_P(0);
text *arg2 = PG_GETARG_TEXT_P(1);
int32 new_text_size = VARSIZE(arg1) + VARSIZE(arg2) - VARHDRSZ;
text *new_text = (text *) palloc(new_text_size); SET_VARSIZE(new_text, new_text_size);
memcpy(VARDATA(new_text), VARDATA(arg1), VARSIZE(arg1) - VARHDRSZ);
memcpy(VARDATA(new_text) + (VARSIZE(arg1) - VARHDRSZ),
VARDATA(arg2), VARSIZE(arg2) - VARHDRSZ);
PG_RETURN_TEXT_P(new_text);
}
再次编译,并且重新创建函数,问题依旧。我已经被这个问题困扰一周了,希望大家踊跃讨论,不吝赐教相信CSDN上的大侠们一定愿意帮助我这个新手,先谢谢各位...
SQL code
iihero=# CREATE FUNCTION one() RETURNS integer
iihero-# AS 'SELECT 1 as ONE' LANGUAGE SQL;
CREATE FUNCTION
iihero=# SELECT one() AS answer;
answer
1
(1 row)
iihero=# \encoding gbk
iihero=# SELECT one() AS answer;
answer
--------
1
(1 row)
iihero=# CREATE TABLE EMP (
iihero(# name text,
iihero(# salary integer,
iihero(# age integer,
iihero(# cubicle point
iihero(# );
CREATE TABLE
iihero=# INSERT INTO EMP VALUES ('Sam', 1200, 16, '(1,1)');
INSERT 0 1
iihero=# INSERT INTO EMP VALUES ('Claire', 5000, 32, '(1,2)');
INSERT 0 1
iihero=# INSERT INTO EMP VALUES ('Andy', -1000, 2, '(1,3)');
INSERT 0 1
iihero=# INSERT INTO EMP VALUES ('Bill', 4200, 36, '(2,1)');
INSERT 0 1
iihero=# INSERT INTO EMP VALUES ('Ginger', 4800, 30, '(2,4)');
INSERT 0 1
iihero=#
iihero=# CREATE FUNCTION double_salary(EMP) RETURNS integer
iihero-# AS 'SELECT $1.salary * 2 AS salary' LANGUAGE SQL;
CREATE FUNCTION
iihero=# SELECT name, double_salary(EMP) AS dream
iihero-# FROM EMP
iihero-# WHERE EMP.cubicle ~= '(2,1)'::point;
name | dream
------+-------
Bill | 8400
(1 row)
iihero=# CREATE FUNCTION add_one(integer) RETURNS integer as 'func1' LANGUAGE C;
CREATE FUNCTION
iihero=# CREATE FUNCTION makepoint(point, point) RETURNS point
iihero-# AS 'func1' LANGUAGE C;
CREATE FUNCTION
iihero=# CREATE FUNCTION copytext(text) RETURNS text
iihero-# AS 'func1' LANGUAGE C;
CREATE FUNCTION
iihero=#
iihero=# CREATE FUNCTION c_overpaid(EMP, integer) RETURNS boolean
iihero-# AS 'func1' LANGUAGE C;
CREATE FUNCTION
iihero=# select add_one(3) as four;
four
------
4
(1 row)
iihero=# SELECT makepoint('(1,2)'::point, '(3,4)'::point ) AS newpoint;
newpoint
----------
(1,4)
(1 row)
iihero=# SELECT copytext('hello world!');
copytext
--------------
hello world!
(1 row)
iihero=# SELECT name, c_overpaid(EMP, 1500) AS overpaid
iihero-# FROM EMP
iihero-# WHERE name = 'Bill' or name = 'Sam';
name | overpaid
------+----------
Sam | f
Bill | t
(2 rows)中间创建函数用的func1.dll,你要把它编译到目录pgsql_home\lib下边。必须是MD(多线程链接库)方式。
整个过程很简单。使用vs2005编译即可。
你稍后在我的资源里头应该能下载到完整的工程,比较简单的。我已经上传了。
PostgreSQL简单函数创建: http://download.csdn.net/source/2979577