To use dynamic functions written in C in postgreSQL, the functions are placed into a file that is compiled and then converted to a shared library (a DLL file in Windows) that's placed into the directory where postgreSQL searches for such libraries.

To illustrate, here are two C functions, both considered to be in the file my_functions.c. The first, add_one, simply adds one to its parameter and returns the result as a way to show how numeric operations are handled. The second, concat, concatenates two strings and returns the result, to show how VARCHAR data types are processed.

VARCHAR variables do not end in the "\0" that terminate standard C strings, so the standard C functions that manipulate strings, such as the string length function strlen(), do not work properly. In particular, using such functions can actually change the contents of the VARCHAR data! (A common effect of using C functions is that, when you call your string-handling procedure the first time, it produces a correct result; then, if you immediately call it again with the same parameters, it produces a different, incorrect result.)

VARCHAR variables consist of a header and the data area. VARSIZE is the size of the entire VARCHAR variable; VARHDRSZ is the size of its header; subtracting the two gives the size of the actual string data. VARDATA() returns the actual contents of the string stored in the VARCHAR.

#include "postgres.h" 
#include "fmgr.h" 
 
PG_FUNCTION_INFO_V1(add_one);
 
Datum add_one(PG_FUNCTION_ARGS)
{ 
  int32 arg = PG_GETARG_INT32(0); 
  PG_RETURN_INT32(arg + 1); 
} 
 
 
PG_FUNCTION_INFO_V1(concat); 
 
Datum concat(PG_FUNCTION_ARGS)
{  
  VarChar *arg1 = (VarChar *)PG_GETARG_VARCHAR_P(0); 
  VarChar *arg2 = (VarChar *)PG_GETARG_VARCHAR_P(1); 
 
  int str1Size = VARSIZE(arg1) - VARHDRSZ; 
  char *str1 = (char *)VARDATA(arg1); 
 
  int str2Size = VARSIZE(arg2) - VARHDRSZ; 
  char *str2 = (char *)VARDATA(arg2); 
 
  VarChar *funcValue = (VarChar *)palloc(str1Size + str2Size+VARHDRSZ); 
  VARATT_SIZEP(funcValue) = str1Size + str2Size + VARHDRSZ; 
  char *newStr = (char *)VARDATA(funcValue); 

  int i; 
  for (i=0; i < str1Size; i++)
  { 
    newStr[i]=str1[i]; 
  } 
   
   
  for (i=str1Size; i < str1Size + str2Size; i++)
  { 
    newStr[i] = str2[i - str1Size]; 
  } 
   
  PG_RETURN_VARCHAR_P(funcValue); 
}

• Compile the file (in C), and link it to become a DLL; using gcc under cygwin, the commands are
  
    gcc -I/usr/include/postgresql/server/ -c my_functions.c
    gcc -shared -o my_functions.dll my_functions.o -lpostgres

• Place my_functions.dll into the cygwin directory /usr/lib/postgresql, which, in a standard Windows installation of cygwin, corresponds to C:\cygwin\lib\postgresql.

• Link the DLL into the database. To do so, run psql on your database (e.g., fabflixs) as a superuser (that is, do not enter a user name):
  

  psql -d fabflixs
  
  and add the functions to the database via the CREATE FUNCTION command:
  
  CREATE FUNCTION add_one(INTEGER) RETURNS INTEGER as 'my_functions', 'add_one' LANGUAGE C STRICT ;
  CREATE FUNCTION concat(VARCHAR, VARCHAR) RETURNS VARCHAR as 'my_functions', 'concat' LANGUAGE C STRICT ;

You can check that the functions were added, and are working properly, by entering psql as a user and executing the functions; for example:

  psql -d fabflixs -U testuse
  SELECT add_one(1) ;
  SELECT concat('Hello ', 'world') ;