PG編寫擴充函數之通過C語言實作

執行個體1:手動編譯

編寫C檔案

• test.c

#include "postgres.h" 
#include "fmgr.h"


#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(squares_return_int);

Datum squares_return_int(PG_FUNCTION_ARGS)
{
    int32 arg = PG_GETARG_INT32(0);
    PG_RETURN_INT32(arg * arg);
}

PG_FUNCTION_INFO_V1(add);
Datum add(PG_FUNCTION_ARGS)
{
    int32 a = PG_GETARG_INT32(0);
    int32 b = PG_GETARG_INT32(1);
    PG_RETURN_INT32(a+b);
}      

編譯C檔案

gcc -I`pg_config --includedir-server` -fPIC  -c test.c
gcc -shared test.o -o test.so
cp test.so `pg_config --libdir`      

在psql中建立函數

[pgsql@node3 ~]$psql
psql (12.3)
Type "help" for help.

postgres=# create or replace  function func_test_square(int) returns int as '$libdir/test.so', 'squares_return_int' LANGUAGE 'c'  STRICT; 
CREATE FUNCTION
postgres=# create or replace function func_add(int) returns int as '$libdir/test.so', 'add' LANGUAGE 'c'  STRICT; 
CREATE FUNCTION
postgres=# 
postgres=# select func_add(1,2);
 func_add 
----------
        3
(1 row)

postgres=# select func_test_square(2);
 func_test_square 
------------------
                4
(1 row)

      

執行個體2:通過Makefile編譯

編寫C檔案 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);
}      

編寫Makefile 檔案

MODULES = add_func

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

編譯安裝

make && make install
[pgsql@node3 ~/test]$make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/postgresql/pgsql-12/include/server -I/postgresql/pgsql-12/include/internal  -D_GNU_SOURCE   -c -o add_func.o add_func.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC add_func.o -L/postgresql/pgsql-12/lib   -Wl,--as-needed -Wl,-rpath,'/postgresql/pgsql-12/lib',--enable-new-dtags  -shared -o add_func.so
[pgsql@node3 ~/test]$make install
/usr/bin/mkdir -p '/postgresql/pgsql-12/lib'
/usr/bin/install -c -m 755  add_func.so '/postgresql/pgsql-12/lib/'
      

在psql中建立函數 add_ab

create function add_ab(int,int)
        returns int
as '$libdir/add_func','add_ab'
language C strict;
postgres=# select add_ab(1,3);
 add_ab 
--------
      4
(1 row)