Postgresql Function Define

Posted on Edited on Word count in article: 3.6k Reading time ≈ 3 mins.
In Postgresql, If you want to do something like "select Hello(a) from A;". You should define your own function Hello().

First, go to postgresql root directory and go into contrib file folder. Create your own directory, and add hello.h , hello.c, Makefile, hello.sql.in and uninstall_Hello.sql.in

Makefile is like the following:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MODULE_big  = Hello // this is module name
OBJS = Hello.o // those will be create by execute "make"

DATA_built = hello.sql // install your function using this sql file
DATA = uninstall_Hello.sql
SHLIB_LINK = $(BE_DLLLIBS)

ifdef USE_PGXS // this is a build infrastructure of extentions in postgreSQL
PGXS = $(shell pg_config --pgxs)
include $(PGXS)
else
subdir = contrib/Hello // here is your code files
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif

// the following code fragment is copying content of *.sql.in into *.sql
%.sql: %.source
	rm -f $@; \
	C=`pwd`; \
	sed -e "s:_OBJWD_:$$C:g" < $< > $@

Attention!!! In Makefile those command should be with no blank at end.
Here we can write hello.sql.in:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
SET search_path = public; --you should add this code first.
--functions
--take care of those functions, if those functions are used for data input and out, their args or return type must be cstring or varchar

-- arg type is cstring
CREATE OR REPLACE FUNCTION hello_in(cstring)
   RETURNS HelloS
   AS 'MODULE_PATHNAME'
   LANGUAGE C IMMUTABLE STRICT;
-- return type is cstring
CREATE OR REPLACE FUNCTION hello_out(HelloS)
   RETURNS cstring
   AS 'MODULE_PATHNAME'
   LANGUAGE C IMMUTABLE STRICT;

--data type
CREATE TYPE HelloS (
   internallength = 52,
   input = hello_in,
   output = hello_out,
   alignment = double
);
--size is 52 bytes and alignment is 8 bytes.
--if you don't know its real size, use internallength = VARIABLE
--if you want use binary array as its input and out, you should use receive=XXX_function, and send = XXX_function

--outer function
--this function can use kinds of types
CREATE OR REPLACE FUNCTION Hello(HelloS)
   RETURNS cstring
   AS 'MODULE_PATHNAME'
   LANGUAGE C IMMUTABLE STRICT;

Here is the uninstall_Hello.sql.in:

1
2
3
4
5
6
SET search_path = public;

DROP FUNCTION hello_in(cstring);
DROP FUNCTION hello_out(HelloS);
DROP TYPE HelloS;
DROP FUNCTION Hello(HelloS);

In hello.h

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#ifndef HELLO_H
#define HELLO_H
#include<stdio.h>
#include<stdlib.h>

#include "postgres.h"
#include "fmgr.h"
#include "funcapi.h"
// you should take care of the order of those head files!!!

typedef struct{
	char content[52];
}HelloS;

Datum		hello_in(PG_FUNCTION_ARGS);
Datum		hello_out(PG_FUNCTION_ARGS);
Datum               Hello(PG_FUNCTION_ARGS);

#endif

hello.c

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#include “hello.h”

#ifndef MOD_FOR_PG
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(hello_in);
Datum
hello_in(PG_FUNCTION_ARGS)
{
	// get args[0]
	char * str = PG_GETARG_CSTRING(0);
    HelloS * hs = palloc(sizeof(HelloS));
    if(NULL != str){
        strcpy(hs->content,str);
        PG_RETURN_POINTER(hs)
    }
    PG_RETURN_NULL();
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
PG_FUNCTION_INFO_V1(hello_out);
Datum
hello_out(PG_FUNCTION_ARGS)
{
	// get args[0]
    Hello * hs = PG_GETARG_POINTER(0);

    if(NULL != hs){
        char * content = palloc(sizeof(HelloS));
        strcpy(content,hs->content);
        PG_RETURN_CSTRING(content);
    }
    PG_RETURN_CSTRING("");
}

PG_FUNCTION_INFO_V1(Hello);
Datum
Hello(PG_FUNCTION_ARGS)
{
   HelloS* hs = PG_GETARG_POINTER(0);
 if(NULL != hs){
    char * res = palloc(sizeof(HelloS));
    sprintf(res,"hello %s",hs->content);
    PG_RETURN_CSTRING(res);
 }
 PG_RETURN_CSTRING("hello nobody");
}

Then cd your folder contrib/Hello, make, and make install

Then start psql to execute :

create table A(a HelloS);

insert into A values(‘cc’);

insert into A values(‘ff’);

select Hello(a) from A;

result:

Hello cc

Hello ff

select * from A;

result:

cc

ff