Composite Types in C-Language Functions

Composite types do not have a fixed layout like C structures. Instances of a composite type may contain NULL fields. In addition, composite types that are part of an inheritance hierarchy may have different fields than other members of the same inheritance hierarchy. Therefore, PostgreSQL provides a procedural interface for accessing fields of composite types from C. As PostgreSQL processes a set of rows, each row will be passed into your function as an opaque structure of type TupleTableSlot. 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';

In the query above, we can define c_overpaid as:

#include "postgres.h" #include "executor/executor.h" /* for GetAttributeByName() */ PG_FUNCTION_INFO_V1(c_overpaid); Datum c_overpaid(PG_FUNCTION_ARGS) { TupleTableSlot *t = (TupleTableSlot *) PG_GETARG_POINTER(0); int32 limit = PG_GETARG_INT32(1); bool isnull; int32 salary; salary = DatumGetInt32(GetAttributeByName(t, "salary", &isnull)); if (isnull) PG_RETURN_BOOL(false); /* Alternatively, do PG_RETURN_NULL() for NULL salary */ PG_RETURN_BOOL(salary > limit); }

GetAttributeByName is the PostgreSQL system function that returns attributes out of the current row. It has three arguments: the argument of type TupleTableSlot* 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 datatype by using the appropriate DatumGetXXX() macro.

The following SQL command lets PostgreSQL know about the c_overpaid function:

CREATE FUNCTION c_overpaid(emp, int4) RETURNS bool AS 'PGROOT/tutorial/obj/funcs_new.so' LANGUAGE 'c';

While there are ways to construct new rows or modify existing rows from within a C function, these are far too complex to discuss in this manual.