User-defined functions can be written in C (or a language that
can be made compatible with C, such as C++). Such functions are
compiled into dynamically loadable objects (also called shared
libraries) and are loaded by the server on demand. The dynamic
loading feature is what distinguishes "C
language" functions from "internal" functions — the actual coding
conventions are essentially the same for both. (Hence, the
standard internal function library is a rich source of coding
examples for user-defined C functions.)

Two different calling conventions are currently used for C
functions. The newer "version 1"
calling convention is indicated by writing a PG_FUNCTION_INFO_V1() macro call for the function,
as illustrated below. Lack of such a macro indicates an old-style
("version 0") function. The language
name specified in CREATE FUNCTION is
C in either case. Old-style functions
are now deprecated because of portability problems and lack of
functionality, but they are still supported for compatibility
reasons.

The first time a user-defined function in a particular
loadable object file is called in a session, the dynamic loader
loads that object file into memory so that the function can be
called. The CREATE FUNCTION for a
user-defined C function must therefore specify two pieces of
information for the function: the name of the loadable object
file, and the C name (link symbol) of the specific function to
call within that object file. If the C name is not explicitly
specified then it is assumed to be the same as the SQL function
name.

The following algorithm is used to locate the shared object
file based on the name given in the CREATE
FUNCTION command:

If the name is an absolute path, the given file is
loaded.

If the name starts with the string $libdir, that part is replaced by the
PostgreSQL package library
directory name, which is determined at build time.

If the name does not contain a directory part, the file
is searched for in the path specified by the configuration
variable dynamic_library_path.

Otherwise (the file was not found in the path, or it
contains a non-absolute directory part), the dynamic loader
will try to take the name as given, which will most likely
fail. (It is unreliable to depend on the current working
directory.)

If this sequence does not work, the platform-specific shared
library file name extension (often .so) is appended to the given name and this
sequence is tried again. If that fails as well, the load will
fail.

It is recommended to locate shared libraries either relative
to $libdir or through the dynamic
library path. This simplifies version upgrades if the new
installation is at a different location. The actual directory
that $libdir stands for can be found
out with the command pg_config
--pkglibdir.

The user ID the PostgreSQL
server runs as must be able to traverse the path to the file
you intend to load. Making the file or a higher-level directory
not readable and/or not executable by the postgres user is a common mistake.

In any case, the file name that is given in the CREATE FUNCTION command is recorded literally in
the system catalogs, so if the file needs to be loaded again
the same procedure is applied.

Note:PostgreSQL
will not compile a C function automatically. The object
file must be compiled before it is referenced in a
CREATE FUNCTION command. See
Section 34.9.6 for
additional information.

To ensure that a dynamically loaded object file is not
loaded into an incompatible server, PostgreSQL checks that the file contains a
"magic block" with the appropriate
contents. This allows the server to detect obvious
incompatibilities, such as code compiled for a different major
version of PostgreSQL. A magic
block is required as of PostgreSQL 8.2. To include a magic block,
write this in one (and only one) of the module source files,
after having included the header fmgr.h:

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

The #ifdef test can be omitted if
the code doesn't need to compile against pre-8.2 PostgreSQL releases.

After it is used for the first time, a dynamically loaded
object file is retained in memory. Future calls in the same
session to the function(s) in that file will only incur the
small overhead of a symbol table lookup. If you need to force a
reload of an object file, for example after recompiling it,
begin a fresh session.

Optionally, a dynamically loaded file can contain
initialization and finalization functions. If the file includes
a function named _PG_init, that
function will be called immediately after loading the file. The
function receives no parameters and should return void. If the
file includes a function named _PG_fini, that function will be called
immediately before unloading the file. Likewise, the function
receives no parameters and should return void. Note that
_PG_fini will only be called
during an unload of the file, not during process termination.
(Presently, unloads are disabled and will never occur, but this
may change in the future.)

To know how to write C-language functions, you need to know
how PostgreSQL internally
represents base data types and how they can be passed to and
from functions. Internally, PostgreSQL regards a base type as a
"blob of memory". The user-defined
functions that you define over a type in turn define the way
that PostgreSQL can operate on
it. That is, PostgreSQL will
only store and retrieve the data from disk and use your
user-defined functions to input, process, and output the
data.

Base types can have one of three internal formats:

pass by value, fixed-length

pass by reference, fixed-length

pass by reference, variable-length

By-value types can only be 1, 2, or 4 bytes in length (also
8 bytes, if sizeof(Datum) is 8 on your
machine). You should be careful to define your types such that
they will be the same size (in bytes) on all architectures. For
example, the long type is dangerous
because it is 4 bytes on some machines and 8 bytes on others,
whereas int type is 4 bytes on most Unix
machines. A reasonable implementation of the int4 type on Unix machines might be:

/* 4-byte integer, passed by value */
typedef int int4;

On the other hand, fixed-length types of any size can be
passed by-reference. For example, here is a sample
implementation of a PostgreSQL
type:

Only pointers to such types can be used when passing them in
and out of PostgreSQL
functions. To return a value of such a type, allocate the right
amount of memory with palloc, fill in
the allocated memory, and return a pointer to it. (Also, if you
just want to return the same value as one of your input
arguments that's of the same data type, you can skip the extra
palloc and just return the pointer to
the input value.)

Finally, all variable-length types must also be passed by
reference. All variable-length types must begin with a length
field of exactly 4 bytes, and all data to be stored within that
type must be located in the memory immediately following that
length field. The length field contains the total length of the
structure, that is, it includes the size of the length field
itself.

Warning

Never
modify the contents of a pass-by-reference input value.
If you do so you are likely to corrupt on-disk data,
since the pointer you are given might point directly
into a disk buffer. The sole exception to this rule is
explained in Section
34.10.

As an example, we can define the type text as follows:

typedef struct {
int4 length;
char data[1];
} text;

Obviously, the data field declared here is not long enough
to hold all possible strings. Since it's impossible to declare
a variable-size structure in C, we rely on the knowledge that the
C compiler won't range-check
array subscripts. We just allocate the necessary amount of
space and then access the array as if it were declared the
right length. (This is a common trick, which you can read about
in many textbooks about C.)

When manipulating variable-length types, we must be careful
to allocate the correct amount of memory and set the length
field correctly. For example, if we wanted to store 40 bytes in
a text structure, we might use a
code fragment like this:

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.

Table 34-1
specifies which C type corresponds to which SQL type when
writing a C-language function that uses a built-in type of
PostgreSQL. The "Defined In" column gives the header file that
needs to be included to get the type definition. (The actual
definition might be in a different file that is included by the
listed file. It is recommended that users stick to the defined
interface.) Note that you should always include postgres.h first in any source file, because it
declares a number of things that you will need anyway.

Table 34-1. Equivalent C Types for Built-In SQL
Types

SQL Type

C Type

Defined In

abstime

AbsoluteTime

utils/nabstime.h

boolean

bool

postgres.h (maybe
compiler built-in)

box

BOX*

utils/geo_decls.h

bytea

bytea*

postgres.h

"char"

char

(compiler built-in)

character

BpChar*

postgres.h

cid

CommandId

postgres.h

date

DateADT

utils/date.h

smallint (int2)

int2 or int16

postgres.h

int2vector

int2vector*

postgres.h

integer (int4)

int4 or int32

postgres.h

real (float4)

float4*

postgres.h

double precision (float8)

float8*

postgres.h

interval

Interval*

utils/timestamp.h

lseg

LSEG*

utils/geo_decls.h

name

Name

postgres.h

oid

Oid

postgres.h

oidvector

oidvector*

postgres.h

path

PATH*

utils/geo_decls.h

point

POINT*

utils/geo_decls.h

regproc

regproc

postgres.h

reltime

RelativeTime

utils/nabstime.h

text

text*

postgres.h

tid

ItemPointer

storage/itemptr.h

time

TimeADT

utils/date.h

time with time zone

TimeTzADT

utils/date.h

timestamp

Timestamp*

utils/timestamp.h

tinterval

TimeInterval

utils/nabstime.h

varchar

VarChar*

postgres.h

xid

TransactionId

postgres.h

Now that we've gone over all of the possible structures for
base types, we can show some examples of real functions.

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, 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.

The version-1 calling convention relies on macros to
suppress most of the complexity of passing arguments and
results. The C declaration of a version-1 function is
always:

Datum funcname(PG_FUNCTION_ARGS)

In addition, the macro call:

PG_FUNCTION_INFO_V1(funcname);

must appear in the same source file. (Conventionally, it's
written just before the function itself.) This macro call is
not needed for internal-language
functions, since PostgreSQL
assumes that all internal functions use the version-1
convention. It is, however, required for dynamically-loaded
functions.

In a version-1 function, each actual argument is fetched
using a PG_GETARG_xxx() macro that corresponds to
the argument's data type, and the result is returned using a
PG_RETURN_xxx() macro for the return type.
PG_GETARG_xxx() takes as its argument the
number of the function argument to fetch, where the count
starts at 0. PG_RETURN_xxx() takes as its argument the
actual value to return.

The CREATE FUNCTION commands are
the same as for the version-0 equivalents.

At first glance, the version-1 coding conventions might
appear to be just pointless obscurantism. They do, however,
offer a number of improvements, because the macros can hide
unnecessary detail. An example is that in coding add_one_float8, we no longer need to be aware
that float8 is a pass-by-reference type.
Another example is that the GETARG
macros for variable-length types allow for more efficient
fetching of "toasted" (compressed or
out-of-line) values.

One big improvement in version-1 functions is better
handling of null inputs and results. The macro PG_ARGISNULL(n) allows a function to test
whether each input is null. (Of course, doing this is only
necessary in functions not declared "strict".) As with the PG_GETARG_xxx() macros, the input arguments
are counted beginning at zero. Note that one should refrain
from executing PG_GETARG_xxx() until one has verified that
the argument isn't null. To return a null result, execute
PG_RETURN_NULL(); this works in
both strict and nonstrict functions.

Other options provided in the new-style interface are two
variants of the PG_GETARG_xxx() macros. The first of these,
PG_GETARG_xxx_COPY(), guarantees to return a
copy of the specified argument that is safe for writing into.
(The normal macros will sometimes return a pointer to a value
that is physically stored in a table, which must not be written
to. Using the PG_GETARG_xxx_COPY() macros guarantees a
writable result.) The second variant consists of the
PG_GETARG_xxx_SLICE() macros which take
three arguments. The first is the number of the function
argument (as above). The second and third are the offset and
length of the segment to be returned. Offsets are counted from
zero, and a negative length requests that the remainder of the
value be returned. These macros provide more efficient access
to parts of large values in the case where they have storage
type "external". (The storage type
of a column can be specified using ALTER
TABLE tablename ALTER COLUMN
colname SET STORAGE storagetype. storagetype is one of plain, external,
extended, or main.)

Finally, the version-1 function call conventions make it
possible to return set results (Section 34.9.10) and
implement trigger functions (Chapter
35) and procedural-language call handlers (Chapter 48). Version-1 code is also more
portable than version-0, because it does not break restrictions
on function call protocol in the C standard. For more details
see src/backend/utils/fmgr/README in
the source distribution.

Before we turn to the more advanced topics, we should
discuss some coding rules for PostgreSQL C-language functions. While it
might be possible to load functions written in languages other
than C into PostgreSQL, this
is usually difficult (when it is possible at all) because other
languages, such as C++, FORTRAN, or Pascal often do not follow
the same calling convention as C. That is, other languages do
not pass argument and return values between functions in the
same way. For this reason, we will assume that your C-language
functions are actually written in C.

The basic rules for writing and building C functions are as
follows:

Use pg_config
--includedir-server to find out where the PostgreSQL server header files are
installed on your system (or the system that your users
will be running on).

Compiling and linking your code so that it can be
dynamically loaded into PostgreSQL always requires special
flags. See Section 34.9.6
for a detailed explanation of how to do it for your
particular operating system.

Remember to define a "magic
block" for your shared library, as described in
Section
34.9.1.

When allocating memory, use the PostgreSQL functions palloc and pfree instead of the corresponding C library
functions malloc and
free. The memory allocated by
palloc will be freed
automatically at the end of each transaction, preventing
memory leaks.

Always zero the bytes of your structures using
memset. Without this, it's
difficult to support hash indexes or hash joins, as you
must pick out only the significant bits of your data
structure to compute a hash. Even if you initialize all
fields of your structure, there might be alignment padding
(holes in the structure) that contain garbage values.

Most of the internal PostgreSQL types are declared in
postgres.h, while the function
manager interfaces (PG_FUNCTION_ARGS, etc.) are in fmgr.h, so you will need to include at
least these two files. For portability reasons it's best to
include postgres.hfirst, before any other
system or user header files. Including postgres.h will also include elog.h and palloc.h for you.

Symbol names defined within object files must not
conflict with each other or with symbols defined in the
PostgreSQL server
executable. You will have to rename your functions or
variables if you get error messages to this effect.

Before you are able to use your PostgreSQL extension functions written in
C, they must be compiled and linked in a special way to produce
a file that can be dynamically loaded by the server. To be
precise, a shared library needs to be
created.

For information beyond what is contained in this section you
should read the documentation of your operating system, in
particular the manual pages for the C compiler, cc, and the link editor, ld. In addition, the PostgreSQL source code contains several
working examples in the contrib
directory. If you rely on these examples you will make your
modules dependent on the availability of the PostgreSQL source code, however.

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 usually
not compiled that way.) The command to link a shared library
contains special flags to distinguish it from linking an
executable (at least in theory — on some systems the practice
is much uglier).

In the following examples we assume that your source code is
in a file foo.c and we will create a
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 only use one
here.

BSD/OS

The compiler flag to create PIC is -fpic.
The linker flag to create shared libraries is -shared.

gcc -fpic -c foo.c
ld -shared -o foo.so foo.o

This is applicable as of version 4.0 of BSD/OS.

FreeBSD

The compiler flag to create PIC is -fpic.
To create shared libraries the compiler flag is
-shared.

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

This is applicable as of version 3.0 of FreeBSD.

HP-UX

The compiler flag of the system compiler to create
PIC is +z. When using GCC it's -fpic. The linker flag for shared libraries
is -b. So:

cc +z -c foo.c

or:

gcc -fpic -c foo.c

and then:

ld -b -o foo.sl foo.o

HP-UX uses the
extension .sl for shared
libraries, unlike most other systems.

IRIX

PIC is the default,
no special compiler options are necessary. The linker
option to produce shared libraries is -shared.

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

Linux

The compiler flag to create PIC is -fpic.
On some platforms in some situations -fPIC must be used if -fpic does not work. Refer to the GCC
manual for more information. The compiler flag to create
a shared library is -shared. A
complete example looks like this:

The compiler flag to create PIC is -fpic.
For ELF systems, the
compiler with the flag -shared is
used to link shared libraries. On the older non-ELF
systems, ld -Bshareable is
used.

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

OpenBSD

The compiler flag to create PIC is -fpic.
ld -Bshareable is used to link
shared libraries.

gcc -fpic -c foo.c
ld -Bshareable -o foo.so foo.o

Solaris

The compiler flag to create PIC is -KPIC
with the Sun compiler and -fpic
with GCC. To link shared
libraries, the compiler option is -G with either compiler or alternatively
-shared with GCC.

cc -KPIC -c foo.c
cc -G -o foo.so foo.o

or

gcc -fpic -c foo.c
gcc -G -o foo.so foo.o

Tru64 UNIX

PIC is the default,
so the compilation command is the usual one. ld with special options is used to do the
linking.

cc -c foo.c
ld -shared -expect_unresolved '*' -o foo.so foo.o

The same procedure is used with GCC instead of the
system compiler; no special options are required.

UnixWare

The compiler flag to create PIC is -K PIC
with the SCO compiler and -fpic
with GCC. To link shared
libraries, the compiler option is -G with the SCO compiler and -shared with GCC.

cc -K PIC -c foo.c
cc -G -o foo.so foo.o

or

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

Tip: If this is too complicated for you, you
should consider using GNU Libtool,
which hides the platform differences behind a uniform
interface.

The resulting shared library file can then be loaded into
PostgreSQL. When specifying
the file name to the CREATE FUNCTION
command, one must give it the name of the shared library file,
not the intermediate object file. Note that the system's
standard shared-library extension (usually .so or .sl) can be
omitted from the CREATE FUNCTION
command, and normally should be omitted for best
portability.

Refer back to Section
34.9.1 about where the server expects to find the shared
library files.

If you are thinking about distributing your PostgreSQL extension modules, setting up a
portable build system for them can be fairly difficult.
Therefore the PostgreSQL
installation provides a build infrastructure for extensions,
called PGXS, so that simple
extension modules can be built simply against an already
installed server. Note that this infrastructure is not intended
to be a universal build system framework that can be used to
build all software interfacing to PostgreSQL; it simply automates common
build rules for simple server extension modules. For more
complicated packages, you need to write your own build
system.

To use the infrastructure for your extension, you must write
a simple makefile. In that makefile, you need to set some
variables and finally include the global PGXS makefile. Here is an example that
builds an extension module named isbn_issn consisting of a shared library, an SQL
script, and a documentation text file:

The last three lines should always be the same. Earlier in
the file, you assign variables or add custom make rules.

The following variables can be set:

MODULES

list of shared objects to be built from source file
with same stem (do not include suffix in this list)

DATA

random files to install into prefix/share/contrib

DATA_built

random files to install into prefix/share/contrib, which
need to be built first

DOCS

random files to install under prefix/doc/contrib

SCRIPTS

script files (not binaries) to install into prefix/bin

SCRIPTS_built

script files (not binaries) to install into prefix/bin, which need to be
built first

REGRESS

list of regression test cases (without suffix), see
below

or at most one of these two:

PROGRAM

a binary program to build (list objects files in
OBJS)

MODULE_big

a shared object to build (list object files in
OBJS)

The following can also be set:

EXTRA_CLEAN

extra files to remove in make
clean

PG_CPPFLAGS

will be added to CPPFLAGS

PG_LIBS

will be added to PROGRAM link
line

SHLIB_LINK

will be added to MODULE_big
link line

PG_CONFIG

path to pg_config
program for the PostgreSQL installation to build
against (typically just pg_config to use the first one in your
PATH)

Put this makefile as Makefile in
the directory which holds your extension. Then you can do
make to compile, and later make install to install your module. By default,
the extension is compiled and installed for the PostgreSQL installation that corresponds
to the first pg_config program found
in your path. You can use a different installation by setting
PG_CONFIG to point to its pg_config program, either within the makefile or
on the make command line.

Caution

Changing PG_CONFIG only
works when building against PostgreSQL 8.3 or later. With
older releases it does not work to set it to anything
except pg_config; you must
alter your PATH to select the
installation to build against.

The scripts listed in the REGRESS
variable are used for regression testing of your module, just
like make installcheck is used for the
main PostgreSQL server. For
this to work you need to have a subdirectory named sql/ in your extension's directory, within which
you put one file for each group of tests you want to run. The
files should have extension .sql,
which should not be included in the REGRESS list in the makefile. For each test
there should be a file containing the expected result in a
subdirectory named expected/, with
extension .out. The tests are run by
executing make installcheck, and the
resulting output will be compared to the expected files. The
differences will be written to the file regression.diffs in diff
-c format. Note that trying to run a test which is missing
the expected file will be reported as "trouble", so make sure you have all expected
files.

Tip: The easiest way of creating the expected
files is creating empty files, then carefully inspecting
the result files after a test run (to be found in the
results/ directory), and copying
them to expected/ if they match
what you want from the test.

Composite types do not have a fixed layout like C
structures. Instances of a composite type can contain null
fields. In addition, composite types that are part of an
inheritance hierarchy can have different fields than other
members of the same inheritance hierarchy. Therefore,
PostgreSQL provides a function
interface for accessing fields of composite types from C.

Suppose we want to write a function to answer the query:

SELECT name, c_overpaid(emp, 1500) AS overpaid
FROM emp
WHERE name = 'Bill' OR name = 'Sam';

GetAttributeByName is the
PostgreSQL system function
that returns attributes out of the specified row. It has three
arguments: the argument of type HeapTupleHeader passed into the function, the name
of the desired attribute, and a return parameter that tells
whether the attribute is null. GetAttributeByName returns a Datum value that you can convert to the proper data
type by using the appropriate DatumGetXXX()
macro. Note that the return value is meaningless if the null
flag is set; always check the null flag before trying to do
anything with the result.

There is also GetAttributeByNum, which selects the target
attribute by column number instead of name.

To return a row or composite-type value from a C-language
function, you can use a special API that provides macros and
functions to hide most of the complexity of building composite
data types. To use this API, the source file must include:

#include "funcapi.h"

There are two ways you can build a composite data value
(henceforth a "tuple"): you can
build it from an array of Datum values, or from an array of C
strings that can be passed to the input conversion functions of
the tuple's column data types. In either case, you first need
to obtain or construct a TupleDesc
descriptor for the tuple structure. When working with Datums,
you pass the TupleDesc to
BlessTupleDesc, and then call
heap_form_tuple for each row.
When working with C strings, you pass the TupleDesc to TupleDescGetAttInMetadata, and then call
BuildTupleFromCStrings for each
row. In the case of a function returning a set of tuples, the
setup steps can all be done once during the first call of the
function.

Several helper functions are available for setting up the
needed TupleDesc. The recommended
way to do this in most functions returning composite values is
to call:

passing the same fcinfo struct
passed to the calling function itself. (This of course requires
that you use the version-1 calling conventions.) resultTypeId can be specified as NULL or as the address of a local variable to
receive the function's result type OID. resultTupleDesc should be the address of a local
TupleDesc variable. Check that the
result is TYPEFUNC_COMPOSITE; if so,
resultTupleDesc has been filled with
the needed TupleDesc. (If it is
not, you can report an error along the lines of "function returning record called in context that
cannot accept type record".)

Tip:get_call_result_type can resolve the
actual type of a polymorphic function result; so it is
useful in functions that return scalar polymorphic results,
not only functions that return composites. The resultTypeId output is primarily useful for
functions returning polymorphic scalars.

Note:get_call_result_type has a sibling
get_expr_result_type, which
can be used to resolve the expected output type for a
function call represented by an expression tree. This can
be used when trying to determine the result type from
outside the function itself. There is also get_func_result_type, which can be used
when only the function's OID is available. However these
functions are not able to deal with functions declared to
return record, and get_func_result_type cannot resolve
polymorphic types, so you should preferentially use
get_call_result_type.

Older, now-deprecated functions for obtaining TupleDescs are:

TupleDesc RelationNameGetTupleDesc(const char *relname)

to get a TupleDesc for the row
type of a named relation, and:

TupleDesc TypeGetTupleDesc(Oid typeoid, List *colaliases)

to get a TupleDesc based on a
type OID. This can be used to get a TupleDesc for a base or composite type. It
will not work for a function that returns record, however, and it cannot resolve
polymorphic types.

Once you have a TupleDesc,
call:

TupleDesc BlessTupleDesc(TupleDesc tupdesc)

if you plan to work with Datums, or:

AttInMetadata *TupleDescGetAttInMetadata(TupleDesc tupdesc)

if you plan to work with C strings. If you are writing a
function returning set, you can save the results of these
functions in the FuncCallContext
structure — use the tuple_desc or
attinmeta field respectively.

to build a HeapTuple given user
data in C string form. values is an
array of C strings, one for each attribute of the return row.
Each C string should be in the form expected by the input
function of the attribute data type. In order to return a null
value for one of the attributes, the corresponding pointer in
the values array should be set to
NULL. This function will need to be
called again for each row you return.

Once you have built a tuple to return from your function, it
must be converted into a Datum. Use:

HeapTupleGetDatum(HeapTuple tuple)

to convert a HeapTuple into a
valid Datum. This Datum can be returned
directly if you intend to return just a single row, or it can
be used as the current return value in a set-returning
function.

There is also a special API that provides support for
returning sets (multiple rows) from a C-language function. A
set-returning function must follow the version-1 calling
conventions. Also, source files must include funcapi.h, as above.

A set-returning function (SRF) is called once for each item it
returns. The SRF must
therefore save enough state to remember what it was doing and
return the next item on each call. The structure FuncCallContext is provided to help control
this process. Within a function, fcinfo->flinfo->fn_extra is used to hold a
pointer to FuncCallContext across
calls.

typedef struct
{
/*
* Number of times we've been called before
*
* call_cntr is initialized to 0 for you by SRF_FIRSTCALL_INIT(), and
* incremented for you every time SRF_RETURN_NEXT() is called.
*/
uint32 call_cntr;
/*
* OPTIONAL maximum number of calls
*
* max_calls is here for convenience only and setting it is optional.
* If not set, you must provide alternative means to know when the
* function is done.
*/
uint32 max_calls;
/*
* OPTIONAL pointer to result slot
*
* This is obsolete and only present for backwards compatibility, viz,
* user-defined SRFs that use the deprecated TupleDescGetSlot().
*/
TupleTableSlot *slot;
/*
* OPTIONAL pointer to miscellaneous user-provided context information
*
* user_fctx is for use as a pointer to your own data to retain
* arbitrary context information between calls of your function.
*/
void *user_fctx;
/*
* OPTIONAL pointer to struct containing attribute type input metadata
*
* attinmeta is for use when returning tuples (i.e., composite data types)
* and is not used when returning base data types. It is only needed
* if you intend to use BuildTupleFromCStrings() to create the return
* tuple.
*/
AttInMetadata *attinmeta;
/*
* memory context used for structures that must live for multiple calls
*
* multi_call_memory_ctx is set by SRF_FIRSTCALL_INIT() for you, and used
* by SRF_RETURN_DONE() for cleanup. It is the most appropriate memory
* context for any memory that is to be reused across multiple calls
* of the SRF.
*/
MemoryContext multi_call_memory_ctx;
/*
* OPTIONAL pointer to struct containing tuple description
*
* tuple_desc is for use when returning tuples (i.e., composite data types)
* and is only needed if you are going to build the tuples with
* heap_form_tuple() rather than with BuildTupleFromCStrings(). Note that
* the TupleDesc pointer stored here should usually have been run through
* BlessTupleDesc() first.
*/
TupleDesc tuple_desc;
} FuncCallContext;

An SRF uses several
functions and macros that automatically manipulate the
FuncCallContext structure (and
expect to find it via fn_extra).
Use:

SRF_IS_FIRSTCALL()

to determine if your function is being called for the first
or a subsequent time. On the first call (only) use:

SRF_FIRSTCALL_INIT()

to initialize the FuncCallContext. On every function call,
including the first, use:

SRF_PERCALL_SETUP()

to properly set up for using the FuncCallContext and clearing any previously
returned data left over from the previous pass.

If your function has data to return, use:

SRF_RETURN_NEXT(funcctx, result)

to return it to the caller. (result
must be of type Datum, either a single
value or a tuple prepared as described above.) Finally, when
your function is finished returning data, use:

SRF_RETURN_DONE(funcctx)

to clean up and end the SRF.

The memory context that is current when the SRF is called is a transient context that
will be cleared between calls. This means that you do not need
to call pfree on everything you
allocated using palloc; it will
go away anyway. However, if you want to allocate any data
structures to live across calls, you need to put them somewhere
else. The memory context referenced by multi_call_memory_ctx is a suitable location
for any data that needs to survive until the SRF is finished running. In most cases,
this means that you should switch into multi_call_memory_ctx while doing the
first-call setup.

C-language functions can be declared to accept and return
the polymorphic types anyelement,
anyarray, anynonarray, and anyenum. See
Section
34.2.5 for a more detailed explanation of polymorphic
functions. When function arguments or return types are defined
as polymorphic types, the function author cannot know in
advance what data type it will be called with, or need to
return. There are two routines provided in fmgr.h to allow a version-1 C function to
discover the actual data types of its arguments and the type it
is expected to return. The routines are called get_fn_expr_rettype(FmgrInfo *flinfo) and
get_fn_expr_argtype(FmgrInfo *flinfo, int
argnum). They return the result or argument type OID, or
InvalidOid if the information is not
available. The structure flinfo is
normally accessed as fcinfo->flinfo. The parameter argnum is zero based. get_call_result_type can also be used as an
alternative to get_fn_expr_rettype.

For example, suppose we want to write a function to accept a
single element of any type, and return a one-dimensional array
of that type:

There is a variant of polymorphism that is only available to
C-language functions: they can be declared to take parameters
of type "any". (Note that this type
name must be double-quoted, since it's also a SQL reserved
word.) This works like anyelement except
that it does not constrain different "any" arguments to be the same type, nor do they
help determine the function's result type. A C-language
function can also declare its final parameter to be VARIADIC "any". This will match one or more
actual arguments of any type (not necessarily the same type).
These arguments will not be gathered into an array as
happens with normal variadic functions; they will just be
passed to the function separately. The PG_NARGS() macro and the methods described
above must be used to determine the number of actual arguments
and their types when using this feature.

Add-ins can reserve LWLocks and an allocation of shared
memory on server startup. The add-in's shared library must be
preloaded by specifying it in shared_preload_libraries. Shared memory is reserved by calling:

void RequestAddinShmemSpace(int size)

from your _PG_init
function.

LWLocks are reserved by calling:

void RequestAddinLWLocks(int n)

from _PG_init.

To avoid possible race-conditions, each backend should use
the LWLock AddinShmemInitLock
when connecting to and initializing its allocation of shared
memory, as shown here: