Compiling and Linking Dynamically-Loaded Functions

Before you are able to use your PostgreSQL extension function written in C, it needs to be compiled and linked in a special way in order to allow it to be dynamically loaded as needed by the server. To be precise, you need to create a shared library.

For more information, read your system's documentation, in particular the manual pages for the C compiler, gcc (or cc), and the link editor, ld. In addition, the Red Hat Database source code contains several working examples in the contrib directory.

Creating shared libraries is generally analogous to linking executables: first the source files are compiled into object files, then the object files are linked together. The object files need to be created as position-independent code (PIC), which conceptually means that they can be placed at an arbitrary location in memory when they are loaded by the executable. (Object files intended for executables are not compiled that way.) The command to link a shared library contains special flags to distinguish it from linking an executable. (At least this is the theory; on some systems the practice is more difficult.)

In the following example we assume that your source code is in a file foo.c and we will create an shared library foo.so. The intermediate object file will be called foo.o unless otherwise noted. A shared library can contain more than one object file, but we use only one here.

On Linux, the compiler flag to create PIC is -fpic. Refer to the GCC manual for more information. The compiler flag to create a shared library is -shared. A complete example looks like this:

cc -fpic -c foo.c cc -shared -o foo.so foo.o

The resulting shared library file can then be loaded into Red Hat Database. When specifying the file name to the CREATE FUNCTION command, give it the name of the shared library file (ending in .so) rather than the simple object file.

Paths given to the CREATE FUNCTION command must be absolute paths (that is, start with /) that refer to directories visible on the machine on which the Red Hat Database server is running. Relative paths do in fact work, but are relative to the directory where the database resides (which is generally invisible to the frontend application). Obviously, it makes no sense to make the path relative to the directory in which the user started the frontend application, since the server could be running on a completely different machine! The user id the PostgreSQL server runs as must be able to traverse the path given to the CREATE FUNCTION command and be able to read the shared library file. (Making the file or a higher-level directory not readable and/or not executable by the "postgres" user is a common mistake.)

An alternative to dynamic loading is to link your functions into the backend statically and declare them as INTERNAL functions. The functions must all have distinct C names but they can be declared with the same SQL names (as long as their argument types differ, of course). This requires more effort as you have to prepare a custom backend executable, so dynamic loading is the recommended solution.