Tuesday, April 17, 2007

C Stored Function handling of an array of VARCHAR/TEXT/CHAR as input for PostgreSQL 8.2

Here's a brief C example that takes an array of VARCHAR as input, iterates through the items and prints them out using elog(). This example specifically uses VARCHAR but it should apply to TEXT or CHAR data types. Otherwise this particular function isn't really all that useful.

#include <sys.h>
#include <unistd.h>
#include <postgres.h>
#include <fmgr.h>
#include <executor/spi.h>
#include <executor/executor.h>
#include <funcapi.h>

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/* Prototypes to prevent potential gcc warnings. */
Datum pgarrayex(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(pgarrayex);

Datum pgarrayex(PG_FUNCTION_ARGS)
{
int i;
int ndim;
int nitems;
int *dim;
char *p;

ArrayType *array_p = PG_GETARG_ARRAYTYPE_P(0);

int16 typlen;
bool typbyval;
char typalign;

/* Get the dimentions of the array. */
ndim = ARR_NDIM(array_p);
dim = ARR_DIMS(array_p);
nitems = ArrayGetNItems(ndim, dim);

/* Get the pointer to the array data. */
p = ARR_DATA_PTR(array_p);

get_typlenbyvalalign(ARR_ELEMTYPE(array_p), &typlen, &amp;amp;amp;typbyval,
  &typalign);

/*
 * Iterate through all the elements in the array and use elog to display
 * them.
 */
for (i = 0; i < nitems; i++) {
 elog(NOTICE, "[%d] %s", i,
   DatumGetCString(DirectFunctionCall1(textout,
   PointerGetDatum(p))));

 /* Increment the position of the pointer to the data in the array. */
 p = att_addlength(p, typlen, PointerGetDatum(p));                 
 p = (char *) att_align(p, typalign);
}

/* Just return a 1. */
PG_RETURN_INT32(1);
}


Here's the SQL to create the stored function, note that the path to the pgarray.so probably needs to be changed on your system:

CREATE OR REPLACE FUNCTION pgarrayex (IN broker_list VARCHAR[])
RETURNS INTEGER AS '/home/markwkm/src/pgarray/pgarray', 'pgarrayex'
LANGUAGE C IMMUTABLE STRICT;


And if you're wondering how to build it, here's an example Makefile:

MODULES = pgarray
DATA_built = pgarray.sql

PGXS := $(shell pg_config --pgxs)
include $(PGXS)


Here's how to try it out:

$ psql -d test -f pgarray.sql
CREATE FUNCTION

$ psql -d test -c "SELECT * FROM pgarrayex('{"a", "b", "z"}');"
NOTICE:  [0] a
NOTICE:  [1] b
NOTICE:  [2] z
 pgarrayex 
-----------
         1
(1 row)

Labels: ,

0 Comments:

Post a Comment

<< Home