- publishing free software manuals
The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 478 pages
ISBN 9781906966065
RRP £14.95 ($19.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

5.9.3 Version 0 Calling Conventions

We present the “old style” calling convention first--although this approach is now deprecated, it's easier to get a handle on initially. In the version-0 method, the arguments and result of the C function are just declared in normal C style, but being careful to use the C representation of each SQL data type as shown above.

Here are some examples:

#include "postgres.h"
#include <string.h>
#include "utils/geo_decls.h"


/* by value */

add_one(int arg)
  return arg + 1;

/* by reference, fixed length */

float8 *
add_one_float8(float8 * arg)
  float8 *result = (float8 *) palloc(sizeof(float8));

  *result = *arg + 1.0;

  return result;

Point *
makepoint(Point * pointx, Point * pointy)
  Point *new_point = (Point *) palloc(sizeof(Point));

  new_point->x = pointx->x;
  new_point->y = pointy->y;

  return new_point;

/* by reference, variable length */

text *
copytext(text * t)
  /* VARSIZE is the total size of the struct in bytes. */
  text *new_t = (text *) palloc(VARSIZE(t));
  /* VARDATA is a pointer to the data region of the struct. */
  memcpy((void *) VARDATA(new_t), /* destination */
         (void *) VARDATA(t), /* source */
         VARSIZE(t) - VARHDRSZ);  /* how many bytes */
  return new_t;

text *
concat_text(text * arg1, text * arg2)
  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);
  return new_text;

Supposing that the above code has been prepared in file ‘funcs.c’ and compiled into a shared object, we could define the functions to PostgreSQL with commands like this:

CREATE FUNCTION add_one(integer) RETURNS integer
     AS 'DIRECTORY/funcs', 'add_one'

-- note overloading of SQL function name "add_one"
CREATE FUNCTION add_one(double precision) RETURNS double 
     AS 'DIRECTORY/funcs', 'add_one_float8'

CREATE FUNCTION makepoint(point, point) RETURNS point
     AS 'DIRECTORY/funcs', 'makepoint'

CREATE FUNCTION copytext(text) RETURNS text
     AS 'DIRECTORY/funcs', 'copytext'

CREATE FUNCTION concat_text(text, text) RETURNS text
     AS 'DIRECTORY/funcs', 'concat_text'

Here, DIRECTORY stands for the directory of the shared library file (for instance the PostgreSQL tutorial directory, which contains the code for the examples used in this section). (Better style would be to use just 'funcs' in the AS clause, after having added DIRECTORY to the search path. In any case, we can omit the system-specific extension for a shared library, commonly .so or .sl.)

Notice that we have specified the functions as “strict”, meaning that the system should automatically assume a null result if any input value is null. By doing this, we avoid having to check for null inputs in the function code. Without this, we'd have to check for null values explicitly, by checking for a null pointer for each pass-by-reference argument. (For pass-by-value arguments, we don't even have a way to check!)

Although this calling convention is simple to use, it is not very portable; on some architectures there are problems with passing data types that are smaller than int this way. Also, there is no simple way to return a null result, nor to cope with null arguments in any way other than making the function strict. The version-1 convention, presented next, overcomes these objections.

ISBN 9781906966065The PostgreSQL 9.0 Reference Manual - Volume 2 - Programming GuideSee the print edition