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

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

5.9.8 Composite-Type Arguments

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 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';

Using call conventions version 0, we can define c_overpaid as:

#include "postgres.h"
#include "executor/executor.h"  /* for GetAttributeByName()
                                 */

bool
c_overpaid(HeapTupleHeader t, /* the current row of emp */
           int32 limit)
{
  bool isnull;
  int32 salary;

  salary =
      DatumGetInt32(GetAttributeByName
                    (t, "salary", &isnull));
  if (isnull)
    return false;
  return salary > limit;
}

In version-1 coding, the above would look like this:

#include "postgres.h"
#include "executor/executor.h"  /* for GetAttributeByName()
                                 */

PG_FUNCTION_INFO_V1(c_overpaid);

Datum
c_overpaid(PG_FUNCTION_ARGS)
{
  HeapTupleHeader t = PG_GETARG_HEAPTUPLEHEADER(0);
  int32 limit = PG_GETARG_INT32(1);
  bool isnull;
  Datum salary;

  salary = GetAttributeByName(t, "salary", &isnull);
  if (isnull)
    PG_RETURN_BOOL(false);
  /* Alternatively, we might prefer to do PG_RETURN_NULL()
     for null salary. */

  PG_RETURN_BOOL(DatumGetInt32(salary) > limit);
}

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.

The following command declares the function c_overpaid in SQL:

CREATE FUNCTION c_overpaid(emp, integer) RETURNS boolean
    AS 'DIRECTORY/funcs', 'c_overpaid'
    LANGUAGE C STRICT;

Notice we have used STRICT so that we did not have to check whether the input arguments were NULL.

ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition