postgres 自定义函数

simpleest C function -return (a+b)

add_func.c 's content:

[wln@localhost postgres9.3]$ cat add_func.c

#include "postgres.h"

#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_ab);

Datum

add_ab(PG_FUNCTION_ARGS)

{

    int32 arg_a=PG_GETARG_INT32(0);

    int32 arg_b=PG_GETARG_INT32(1);

    PG_RETURN_INT32(arg_a+arg_b);

}

解释如下：

(1) #include  "postgres.h" : 该头文件包含写C代码的主要的定义和声明

(2) #include "fmgr.h" : 该头文件包含PG_* 宏定义

(3) PG_MODULE_MAGIC: This is a "magic block" defined in fmgr.h. This block

is used by the server to ensure that it does not load code compiled by a

different version of PostgreSQL, potentially crashing the server. It was

introduced in Version 8.2 of PostgreSQL. If you really need to write code

which can also be compiled for PostgreSQL versions before 8.2 you need to

put this between #ifdef PG_MODULE_MAGIC / #endif. You see this a lot in

samples available on the Internet, but you probably will not need to do it for

any new code. The latest pre-8.2 version became officially obsolete (that is

unsupported) in November 2010, and even 8.2 community support ended in

December 2011

(4). PG_FUNCTION_INFO_V1(add_ab) : This introduces the function to

PostgreSQL as Version 1 calling convention function. Without this line, it

will be treated as an old-style Version 0 function. (See the information box

following the Version 0 reference.)

(5). Datum: This is the return type of a C-language PostgreSQL function.

(6). add_ab(PG_FUNCTION_ARGS): The function name is add_ab and the rest are

its arguments. The PG_FUNCTION_ARGS definition can represent any number

of arguments and has to be present, even for a function taking no arguments

(7). int32 arg_a = PG_GETARG_INT32(0);: You need to use the PG_GETARG_

INT32() macro (or corresponding PG_GETARG_xxx() for

other argument types) to get the argument value.

(8). PG_RETURN_INT32(arg_a + arg_b);: Finally, you use the

PG_RETURN_() macro to build and return

a suitable return value.

Makefile 's content:

[wln@localhost postgres9.3]$ cat Makefile 

MODULES = add_func

PG_CONFIG = pg_config

PGXS := $(shell $(PG_CONFIG) --pgxs)

include $(PGXS)

编译及测试

[wln@localhost postgres9.3]$ make

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -I. -I. -I/home/wln/postgres9.3/install/include/server -I/home/wln/postgres9.3/install/include/internal -D_GNU_SOURCE   -c -o add_func.o add_func.c

add_func.c:9: 警告：‘add_ab’先前没有原型

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -L/home/wln/postgres9.3/install/lib -Wl,--as-needed -Wl,-rpath,'/home/wln/postgres9.3/install/lib',--enable-new-dtags  -shared -o add_func.so add_func.o

[wln@localhost postgres9.3]$ ll

总计 21928

-rw-rw-r--  1 wln wln      231 07-12 09:20 add_func.c

-rw-rw-r--  1 wln wln     6844 07-12 09:40 add_func.o

-rwxrwxr-x  1 wln wln     8660 07-12 09:40 add_func.so

drwx------ 15 wln wln     4096 07-12 09:02 data

drwxrwxr-x  6 wln wln     4096 07-08 12:31 install

-rw-rw-r--  1 wln wln       95 07-12 08:58 Makefile

drwxrwxr-x  6 wln wln     4096 07-08 12:22 postgresql-9.3beta2

-rw-rw-r--  1 wln wln 22380613 2013-08-03 postgresql-9.3beta2.tar.gz

[wln@localhost postgres9.3]$ cp add_func.so install/lib/

[wln@localhost postgres9.3]$ psql -d postgres

postgres=# CREATE FUNCTION add(int,int)

RETURNS int

AS '/home/wln/postgres9.3/install/lib/add_func', 'add_ab'

LANGUAGE C STRICT;

CREATE FUNCTION

postgres=# select add(1,2);

 add 

-----

   3

(1 row)

postgres=# 

参考：

（1）《postgresql server programing》eight charpter "writing advanced functions in C "