Database Reference
In-Depth Information
Chapter 8. Writing Advanced Functions
in C
In the previous chapter, we introduced you to the possibilities of
untrusted
pluggable
languages being available to a PostgreSQL developer to achieve things impossible in
most other relational databases.
While using a pluggable scripting language is enough for a large class of problems,
there are two main categories, where they may fall short, performance and depth of
functionality. Most scripting languages are quite a bit slower than optimized C code
when executing the same algorithms. For a single function, this may not be the case
because common things such as dictionary lookups or string matching have been op-
timized so well over the years, but in general C code will be faster than scripted code.
Also, in cases where the function is called millions of times per query, the overhead
of actually calling the function and converting the arguments and return values to and
from the scripting language counterparts can be a significant portion of the run time.
The second potential problem with pluggable languages is that most of them just do
not support the full range of possibilities that is provided by PostgreSQL. There are
just some things that simply cannot be coded in anything else but C. For example,
when you define a completely new type for PostgreSQL, the type input and output
functions which convert the type's text representation to internal representation and
back need to handle PostgreSQL's pseudo-type
cstring
. This is basically the C
string or a zero-terminated string. Returning
cstring
is simply not supported by any
of the PL languages included in the core distribution, at least not as of PostgreSQL
Version 9.2. The PL languages also do not support pseudo types
ANYELEMENT
,
ANYARRAY
, and specially
"any" VARIADIC
.
In the following sections, we will go step-by-step through writing some PostgreSQL
extension functions in increasing complexity in C.
We will start from the simplest
add 2 arguments
function which is quite similar to the
one in PostgreSQL manual, but we will present the material in a different order so set-
ting up the build environment comes early enough so that you can follow us hands-on
from the very beginning.
