The PostgreSQL SRF Interface

First off, you should know that the new SRF interface is simply a wrapper around the old method. Set-returning functions are still invoked multiple times. The first time through, an SRF initializes its own context structure and stores that structure away so that each subsequent invocation can find it. In the old approach, an SRF examined fmgr_info->fn_extra to determine whether it was being invoked for the first time (if fmgr_info->fn_extra is NULL, this is the first call). In the new approach, you call the SRF_IS_FIRSTCALL() macro instead. You can probably guess what this macro does: It returns trUE if fmgr_info->fn_extra is NULL (implying that this is the first call). In fact, here's the definition of the SRF_IS_FIRSTCALL() macro:

#define SRF_IS_FIRSTCALL() ( fcinfo->flinfo->fn_extra == NULL )

No great surprises there.

Once you know that you're looking at the first invocation of an SRF, you typically allocate a context structure of some sort and store the address of the structure in fmgr_info->fn_extra. In the new approach, you call the SRF_FIRSTCALL_INIT() macro. This macro allocates its own context structure (a structure of type FuncCallContext), records the address of the structure in fmgr_info->fn_extra, and returns the address back to your SRF. A FuncCallContext structure looks like this:

typedef struct FuncCallContext
 uint32 call_cntr;
 uint32 max_calls;
 TupleTableSlot * slot;
 void * user_fctx;
 AttInMetadata * attinmeta;
 MemoryContext multi_call_memory_ctx;
 TupleDesc tuple_desc;
} FuncCallContext;

If the SRF_FIRSTCALL_INIT() macro stores its own pointer in fmgr_info->fn_extra, where are you supposed to store the address of your context structure? In the user_fctx fieldthat pointer is reserved for your own personal use, just like fmgr_info->fn_extra was reserved for your use in the old SRF mechanism. I'll explain the other members of the FuncCallContext structure in a moment.

Now that you have a pointer to a spanking new FuncCallContext (remember, SRF_FIRSTCALL_INIT() returns the address of the structure), you can allocate your own context structure and store its address in user_fctx:

FuncCallContext * srf = SRF_FIRSTCALL_INIT();
dir_ctx * ctx;

ctx = (dir_ctx *) MemoryContextAlloc( srf->multi_call_memory_ctx,
 sizeof( dir_ctx ));

srf->usr_fctx = ctx;

Notice that the FuncCallContext structure holds a MemoryContext named multi_call_memory_ctx. Any data that you need to save from one invocation to the next must be allocated from the multi_call_memory_ctx or PostgreSQL will discard that data as soon as the first invocation completes (multi_call_memory_ctx is equivalent to fmgr_info->fn_mctx in the old SRF mechanism).

Each time your SRF is invoked (even the first time), you should call the SRF_PERCALL_SETUP() macro. Like SRF_FIRSTCALL_INIT(), SRF_PERCALL_SETUP() returns a pointer to the FuncCallContext structure. The context pointer that you saved in user_fctx is still there. You can use that pointer to get to the context structure that you allocated (and initialized) the first time through.

The new SRF mechanism provides two more macros: SRF_RETURN_NEXT() and SRF_RETURN_DONE(). As you might expect, these macros return information to the caller. The SRF_RETURN_NEXT() macros returns a value (a Datum) to the caller and tells the server to call you again to retrieve the next value in the result set (remember, you're writing a set-returning function; the server will call your function until you indicate that you have no more results to add to the set). The SRF_RETURN_DONE() macros returns a NULL value to the caller and tells the server that you have no more results to add to the result set. SRF_RETURN_DONE() also deallocates the FullCallContext structure so you should perform any cleanup work before you call SRF_RETURN_DONE()you won't get another chance.

To show you how all of these macros fit together, Listing 6.5 shows the filelist() function again, this time created with the new SRF mechanism:

Listing 6.5. filelistSRF.c

 1 /*
 2 ** Filename: filelistSRF.c
 3 */
 5 #include "postgres.h"
 6 #include "funcapi.h"
 8 #include 
 9 #include 
11 typedef struct
12 {
13 struct dirent ** dir_ctx_entries;
14 } dir_ctx;
16 PG_FUNCTION_INFO_V1(filelist);
18 Datum filelist(PG_FUNCTION_ARGS)
19 {
20 text * startText = PG_GETARG_TEXT_P(0);
21 int len = VARSIZE( startText ) - VARHDRSZ;
22 char * start = (char *)palloc( len+1 );
23 dir_ctx * ctx;
24 FuncCallContext * srf;
26 memcpy( start, startText->vl_dat, len );
27 start[len] = '';
30 {
33 srf->user_fctx = MemoryContextAlloc( srf->multi_call_memory_ctx,
34 sizeof( dir_ctx ));
36 ctx = (dir_ctx *)srf->user_fctx;
38 srf->max_calls = scandir(start,&ctx->dir_ctx_entries,NULL,alphasort);
39 srf->call_cntr = 0;
40 }
43 ctx = (dir_ctx *)srf->user_fctx;
45 if( srf->max_calls == -1 )
46 SRF_RETURN_DONE( srf );
48 if( srf->call_cntr < srf->max_calls )
49 {
50 struct dirent * entry;
51 size_t nameLen;
52 size_t resultLen;
53 text * result;
55 entry = ctx->dir_ctx_entries[srf->call_cntr];
56 nameLen = strlen( entry->d_name );
57 resultLen = nameLen + VARHDRSZ;
59 result = (text *)palloc( resultLen );
61 VARATT_SIZEP( result ) = resultLen;
63 memcpy( VARDATA( result ), entry->d_name, nameLen );
65 SRF_RETURN_NEXT( srf, (Datum) result );
66 }
67 else
68 {
69 SRF_RETURN_DONE( srf );
70 }
71 }

I'll point out a few of the differences. First, notice that you don't need quite as many #include files when you use the new mechanism (the new funcapi.h header takes care of including any required headers). Next, take a look at the dir_ctx structure at line 11. If you compare that to the original version, you'll notice that the new version is much shorter. The FuncCallContext structure already contains placeholders for some of the data that used to be in dir_ctx. I'll explain more in a moment.

The next significant change appears at line 29. The new version of filelist() calls the SRF_IS_FIRSTCALL() macro to decide whether to initialize itself or return the next value in the result set. At line 31 you see a call to the SRF_FIRSTCALL_INIT() macro. That macro returns a pointer to the FuncCallContext structure that you're supposed to use for this invocation and for future invocations. The call to MemoryContextAlloc() (line 33) allocates space for a dir_ctx from the srf->multi_call_memory_ctx context. srf->multi_call_memory_ctx is a memory pool that survives from invocation to invocation.

Now take a look at lines 38 and 39. The call to scandir() returns the number of files that it finds in the given directory. In the previous version, you stored the file count in dir_ctx->dir_ctx_count. In the new version, you don't need an extra field (dir_ctx_count) to hold the file count; the FuncCallContext structure already has a field that serves the same purpose: max_calls. The SRF mechanism doesn't actually do anything with max_calls, it just gives you a place to store a number. At line 38, filelist() stores the file count in srf->max_calls. The FuncCallContext structure also has a replacement for the dir_ctx_current field that you saw in the original version of this function. Each time PostgreSQL calls your function, it increments the call_cntr field in the FuncCallContext structure. call_cntr starts at 0 and is incremented each time you call SRF_RETURN_NEXT(). To summarize, the new version of filelist() stores the file count in srf->max_calls and uses srf->call_cntr to index into the array of filenames.

Every time the server calls this function, filelist() calls SRF_PERCALL_SETUP() to retrieve a pointer to the FuncCallContext structure (see line 42). If filelist() decides that it has no more filenames to add to the result set, it calls SRF_RETURN_DONE() to tell PostgreSQL that it has finished its work (lines 46 and 69). If filelist() does have another result, it creates a text structure and calls SET_RETURN_NEXT() (see the previous version of this function for a more complete explanation). SET_RETURN_NEXT() increments srf->call_cntr, returns the Datum (result) to the server, and tells the server that it should call this function again to retrieve the next result.

You can see that the new version of this function is very similar to the old version. The SRF macros simply hide a few of the quirks imposed by the PostgreSQL calling convention. Which approach should you use? The down-and-dirty approach or the new SRF-macrobased approach? That depends on your goals. If you need to write an extension function that will work in an older version of PostgreSQL (older than version 7.3), use the original approach (the SRF macros were added in version 7.3). If not, consider the new approach. It's possible that the SRF calling convention may change in the future and it seems safer to assume that the PostgreSQL developers will hide as many changes as possible behind the SRF macros. If you choose to use the old approach, you may find that you have to change your source code when you upgrade to a future release.

Part I: General PostgreSQL Use

Introduction to PostgreSQL and SQL

Working with Data in PostgreSQL

PostgreSQL SQL Syntax and Use


Part II: Programming with PostgreSQL

Introduction to PostgreSQL Programming

Extending PostgreSQL


The PostgreSQL C APIlibpq

A Simpler C APIlibpgeasy

The New PostgreSQL C++ APIlibpqxx

Embedding SQL Commands in C Programsecpg

Using PostgreSQL from an ODBC Client Application

Using PostgreSQL from a Java Client Application

Using PostgreSQL with Perl

Using PostgreSQL with PHP

Using PostgreSQL with Tcl and Tcl/Tk

Using PostgreSQL with Python

Npgsql: The .NET Data Provider

Other Useful Programming Tools

Part III: PostgreSQL Administration

Introduction to PostgreSQL Administration

PostgreSQL Administration

Internationalization and Localization


Replicating PostgreSQL Data with Slony

Contributed Modules


PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
PostgreSQL(c) The comprehensive guide to building, programming, and administering PostgreSQL databases
ISBN: 735712573
Year: 2004
Pages: 261 © 2008-2020.
If you may any questions please contact us: