Extending PostgreSQL with own C functions

Power of open source, if you are not happy with what PostgreSQL currently offers, you write own extension in C. Compile code with your functions into shared library, install it and they will become available from PostgreSQL. OK there are some rules and procedures. Once we are inside PostgreSQL we are using its types, interfaces and utilities. Let us do Hello World example. To build example you will need postgresql-server-dev-9.1 or whatever version you are using, installed on your Linux box.

#include "postgres.h" #include <string.h> #include "fmgr.h" #include "utils/builtins.h" #ifdef PG_MODULE_MAGIC PG_MODULE_MAGIC; #endif PG_FUNCTION_INFO_V1(say_hello_n); Datum say_hello_n(PG_FUNCTION_ARGS) { text *arg1 = cstring_to_text("Hello "); text *arg2 = PG_GETARG_TEXT_P(0); 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); }

As we can see we are using new V1 spec. That is modified concat_text example from PostgreSQL documentation, look for 35.9.4. Version 1 Calling Conventions. We have load of different VAR macros. For example:

VARHDRSZ is the same as sizeof(int4), but it's considered good style to use the macro VARHDRSZ to refer to the size of the overhead for a variable-length type.

That is from PostgreSQL documentation. SET_VARSIZE we can find in includes postgres.h

#define SET_VARSIZE_4B(PTR,len) \ (((varattrib_4b *) (PTR))->va_4byte.va_header = (((uint32) (len)) << 2))

Unless you are on big endian. Going through header file one also can read in comments more about Datum and varlena datatypes. Then we got palloc which corresponds to malloc, memcpy you already know and GET and RETURN macros. It is obvious that for writing extensions one needs to familiarize himself with PostgreSQL internals. Power without knowledge and responsibility exists only in fery tails told by “software evangelists” at annual developers developers developers meetings.
Variables passed around by PostgreSQL may be on the disc, do not change them.
To build shared library I used the following Makefile:

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

Rather long story to get location of pgxs. That pgxs is location of makefiles for building extensions. It is not trivial build and using provided mk files is right way to do it. After that we can copy say_hello.so to some reasonable location or give full path to it in create function declaration.

CREATE FUNCTION say_hello(text) RETURNS text AS '/opt/pg/ext/say_hello', 'say_hello_n' LANGUAGE C STRICT; ------- select say_hello('Filip');

PostgreSQL already allows Python through untrusted language PL/Python. One can utilize power of Python for functions or triggers without learning much about PostgreSQL internals. But again if you need power and speed, you can use what PostgreSQL speaks internally and that is C.