Returning Multiple Values from an Extension Function

The second extension that you will add works well with the filesize function. Given the name of a directory, the filelist function returns a list of all files (and subdirectories) contained in that directory. The filesize function (from the previous example) returns a single value; filelist will return multiple rows. An extension function that can return multiple results is called a set-returning function, or SRF.

PostgreSQL s SRF Interface

Before you read too much further, I should tell you that there's an easy way to write set-returning functions and another method that's almost as easy. I'll describe both methods, starting with the slightly more difficult approach. Starting with version 7.3, the PostgreSQL developers introduced a set of wrapper functions (and macros) that put a friendlier face on top of the original method. Under the hood, your SRF is doing the same thing whether you use the new approach or the old approach, but it's a little easier to understand the new SRF interface if you can peek under the covers.

When you are finished creating the filelist function, you can use it like this:

movies=# SELECT filelist( '/usr' );
 filelist
------------
 .
 ..
 bin
 dict
 etc
 games
 html
 include
 kerberos
 lib
 libexec
 local
 sbin
 share
 src
 tmp
 X11R6
(17 rows)

In this example, the user has invoked the filelist function only once, but 17 rows were returned. A SRF is actually called multiple times. In this case, the filelist() function is called 18 times. The first time through, filelist() does any preparatory work required and then returns the first result. For each subsequent call, filelist() returns another row until the result set is exhausted. On the 18th call, filelist() returns a status that tells the server that there are no more results available.

Like the filesize function, filelist takes a single argument; a directory name in the form of a TEXT value. This function returns a SETOF TEXT values. Listing 6.3 shows the first part of the filelist.c source file:

Listing 6.3. filelist.c (Part 1)

 1 /*
 2 ** Filename: filelist.c
 3 */
 4
 5 #include "postgres.h"
 6 #include "fmgr.h"
 7 #include "nodes/execnodes.h"
 8
 9 #include 
10
11 typedef struct
12 {
13 int dir_ctx_count;
14 struct dirent ** dir_ctx_entries;
15 int dir_ctx_current;
16 } dir_ctx;
17
18 PG_FUNCTION_INFO_V1(filelist);
19

filelist.c #includes four header files, the first three of which are supplied by PostgreSQL. postgres.h and fmgr.h provide data type definitions, function prototypes, and macros that you will need to create extensions. The nodes/execnodes.h header file defines a structure (ReturnSetInfo) that you need because filelist returns a set of values. You will use the scandir() function to retrieve the directory contents from the operating system. The fourth header file defines a few data types that are used by scandir().

Line 11 defines a structure that keeps track of your progress. In the first invocation, you will set up a context structure (dir_ctx) that we can use for each subsequent call. The dir_ctx_count member indicates the number of files and subdirectories in the given directory. The dir_ctx_entries member is a pointer to an array of struct dirent structures. Each member of this array contains a description of a file or subdirectory. dir_ctx_current keeps track of the current position as you traverse the dir_ctx_entries array.

Line 18 tells PostgreSQL that filelist() uses the version-1 calling convention.

Listing 6.4 shows the filelist() function:

Listing 6.4. filelist.c (Part 2)

 20 Datum filelist(PG_FUNCTION_ARGS)
 21 {
 22 FmgrInfo * fmgr_info = fcinfo->flinfo;
 23 ReturnSetInfo * resultInfo = (ReturnSetInfo *)fcinfo->resultinfo;
 24 text * startText = PG_GETARG_TEXT_P(0);
 25 int len = VARSIZE( startText ) - VARHDRSZ;
 26 char * start = (char *)palloc( len+1 );
 27 dir_ctx * ctx;
 28
 29 memcpy( start, startText->vl_dat, len );
 30 start[len] = '';
 31
 32 if( fcinfo->resultinfo == NULL )
 33 elog(ERROR, "filelist: context does not accept a set result");
 34
 35 if( !IsA( fcinfo->resultinfo, ReturnSetInfo ))
 36 elog(ERROR, "filelist: context does not accept a set result");
 37
 38 if( fmgr_info->fn_extra == NULL )
 39 {
 40 dir_ctx * new_ctx;
 41
 42 fmgr_info->fn_extra = MemoryContextAlloc( fmgr_info->fn_mcxt,
 43 sizeof( dir_ctx ));
 44
 45 new_ctx = (dir_ctx *)fmgr_info->fn_extra;
 46
 47 new_ctx->dir_ctx_count = scandir( start,
 48 &new_ctx->dir_ctx_entries,
 49 NULL,
 50 alphasort );
 51 new_ctx->dir_ctx_current = 0;
 52 }
 53
 54 ctx = (dir_ctx *)fmgr_info->fn_extra;
 55
 56 if( ctx->dir_ctx_count == -1 )
 57 {
 58 pfree( fmgr_info->fn_extra );
 59
 60 fmgr_info->fn_extra = NULL;
 61
 62 resultInfo->isDone = ExprEndResult;
 63
 64 PG_RETURN_NULL();
 65 }
 66
 67 if( ctx->dir_ctx_current < ctx->dir_ctx_count )
 68 {
 69 struct dirent * entry;
 70 size_t nameLen;
 71 size_t resultLen;
 72 text * result;
 73
 74 entry = ctx->dir_ctx_entries[ctx->dir_ctx_current];
 75 nameLen = strlen( entry->d_name );
 76 resultLen = nameLen + VARHDRSZ;
 77
 78 result = (text *)palloc( resultLen );
 79
 80 VARATT_SIZEP( result ) = resultLen;
 81
 82 memcpy( VARDATA( result ), entry->d_name, nameLen );
 83
 84 resultInfo->isDone = ExprMultipleResult;
 85
 86 /*
 87 ** Advance to the next entry in our array of
 88 ** filenames/subdirectories
 89 */
 90 ctx->dir_ctx_current++;
 91
 92 PG_RETURN_TEXT_P( result );
 93 }
 94 else
 95 {
 96 free( ctx->dir_ctx_entries );
 97
 98 pfree( fmgr_info->fn_extra );
 99
100 fmgr_info->fn_extra = NULL;
101
102 resultInfo->isDone = ExprEndResult;
103
104 PG_RETURN_NULL();
105 }
106 }

Line 20 declares filelist() using the standard version-1 calling convention (remember, a version-1 function always returns a Datum and uses the PG_FUNCTION_ARGS preprocessor symbol as an argument list).

The C preprocessor translated line 20 into

Datum filesize( FunctionCallInfo fcinfo )

As you can see, you can access the single argument to filesize() through the variable fcinfo. All version-1 extension functions expect a FunctionCallInfo structure. Here is the definition of the FunctionCallInfo data type:

typedef struct FunctionCallInfoData
{
 FmgrInfo *flinfo; /* ptr to lookup info used for this call */
 struct Node *context; /* pass info about context of call */
 struct Node *resultinfo; /* pass or return extra info about result */
 bool isnull; /* true if result is NULL */
 short nargs; /* # arguments actually passed */
 Datum arg[FUNC_MAX_ARGS]; /* Function arguments */
 bool argnull[FUNC_MAX_ARGS]; /* T if arg[i] is NULL */
} FunctionCallInfoData;

There is quite a bit of information in this structure. For now, you need to know about only two of the structure members; the rest of the members are manipulated using macros, so you should pretend that you don't see them. The two members that you are interested in are flinfo and resultInfo. The flinfo member points to a structure of type FmgrInfo. The FmgrInfo structure looks like this:

typedef struct FmgrInfo
{
 PGFunction fn_addr; /* function or handler to be called */
 Oid fn_oid; /* OID of function (NOT of handler, if any) */
 short fn_nargs; /* 0..FUNC_MAX_ARGS, or -1 if variable arg */
 bool fn_strict; /* func. is "strict" (NULL in = NULL out) */
 bool fn_retset; /* func. returns a set (multiple calls) */
 void *fn_extra; /* extra space for use by handler */
 MemoryContext fn_mcxt; /* memory context to store fn_extra in */
} FmgrInfo;

Look closely at the FmgrInfo and FunctionCallInfo structures. Why would you need two structures to represent a function call? The FmgrInfo function contains information about the definition of a function; in other words, the stuff you tell PostgreSQL in the CREATE FUNCTION command can be found in the FmgrInfo structure. The FunctionCallInfo structure represents a single invocation of a function. If you call the same function 20 times, you'll have 20 different FunctionCallInfo structures, each pointing to a single FmgrInfo structure. You can see the difference by comparing FmgrInfo.fn_nargs with FunctionCallInfo.nargs. FmgrInfo.fn_nargs tells you how many arguments were listed in the CREATE FUNCTION command; FmgrInfo.fn_nargs tells you how many arguments were passed to this particular invocation.

Line 23 declares a variable called fmgr_info; you'll use this to get to the FmgrInfo structure for this function. Line 24 declares a variable that you will use to get to the ReturnSetInfo structure. I'll describe the ReturnSetInfo structure in a moment.

Lines 24 through 30 turn the text argument into a null-terminated string. This is basically the same procedure you used in the filesize() function.

Lines 32 through 36 perform some sanity checks. It's possible to call the filelist() function in an inappropriate context. We know that filelist() returns multiple rows, so it makes sense to call that function as a target of a SELECT command. You could also call filelist() in the WHERE clause of a SELECT command, but that would be an inappropriate context (because of that multiple-row problem). When you write a function that returns a set of values, you should ensure that your function is being called in an appropriate context the way we do here.

Line 38 is where the interesting stuff starts. fmgr_info->fn_extra is a pointer that you can use for your own purposes; PostgreSQL doesn't do anything with this structure member except to provide for your use. The first time filelist() is called, the fmgr_info->fn_extra member is NULL. In each subsequent call, fmgr_info->fn_extra is equal to whatever you set it to in the previous call. Sounds like a great place to keep context information. Remember the dir_ctx structure you looked at earlier? That structure holds the information that you use to keep track of your progress as you walk through the array of file entries in a given directory.

At line 42, you know that fmgr_info->fn_extra is NULL: That implies that you have not yet started traversing a directory list. So, you allocate a dir_ctx structure and point fmgr_info->fn_extra to the new structure. The next time you are called, fmgr_info->fn_extra will point to the same dir_ctx structure (remember, there is only one FmgrInfo structure, regardless of how many times this function is called).

You may be thinking that I should have used palloc() to allocate the dir_ctx structure. In most extension functions, that is precisely what you should do. But in the case of an SRF, you want to allocate information related to the FmgrInfo structure in a different memory context[2], the context pointed to in the fmgr_info structure.

[2] You can think of a memory context as a pool of memory. Unlike malloc(), the MemoryContextAlloc() function allocates memory from a specific pool (malloc() allocates all memory from the same pool). A memory context has lifetime (or scope). When the scope completes, all memory allocated within that scope is automatically released. The palloc() function is just a wrapper around MemoryContextAlloc(). The memory context used by palloc() is destroyed at the end of a transaction (or possibly sooner).

Lines 47 through 50 do the real grunt work. You use the scandir() function to create an array of struct dirent structures. Each element in this array (new_ctx->dir_ctx_entries) describes a file or subdirectory. The scandir() function expects four parameters. The first parameter is the name of the directory that you are interested in; you pass the null-terminated string (start) that you crafted earlier in this function. The second parameter is a bit complexit's a pointer to a pointer to an array of struct dirent structures. You know that your dir_ctx.dir_ctx_entries member is a pointer to an array of structures, so you pass the address of dir_ctx_entries and scandir() points dir_ctx_entries to the new array. The third parameter is a pointer to a structure. If you want to choose which files and subdirectories to include in the result set, you can write your own selection function and pass its address to scandir(). You want all files and subdirectories so you just pass in a NULL to tell scandir() not to filter the result set. The final scandir() parameter is a pointer to a comparison function. If you don't provide a comparison function, scandir() won't sort the result set. Use the alphasort function from the C Runtime Libraryit's already written, and you aren't too concerned about performance here. For more information on scandir() and alphasort(), see the scandir() man page.

Finish initializing the dir_ctx structure by setting dir_ctx_current to zero. dir_ctx_current is incremented as you walk through the dir_ctx_entries.

Now that the initialization is complete, you can return your first result. But first, a quick review. You know that PostgreSQL calls this function many times and it continues to call filelist() until you set resultInfo->isDone to ExprEndResult. You can detect the initial call to filelist() by the fact that fmgr_info->fn_extra is NULL. In the initial call, you allocate a context structure and point fmgr_info->fn_extra to the new structure; the next time that filelist() is called, fmgr_info->fn_extra will not be NULL, so you know that you can skip the initialization step. Next, populate the context structure by calling the scandir() function: scandir() allocates an array of struct dirent structures and gives you a pointer to that array.

Line 54 retrieves the address of your context structure from fmgr_info->fn_extra.

Lines 56 through 65 take care of the case where the scandir() function fails to return any directory entries. The scandir() function returns the number of directory entries retrievedit returns -1 on failure.

The details in this section of code are important. First, you must free the context structure that you allocated in the initial call (using pfree()). You also set fmgr__info->fn_extra to NULL; if you forget this step, the next call to filelist() will find a stale context structure and won't reinitialize. Remember, there is one FunctionCallInfo structure for each invocation, but there is never more than one FmgrInfo structure; you'll get the same FmgrInfo structure each time filelist() is invoked. Line 62 tells PostgreSQL that you have reached the end of the result set and line 64 returns a NULL Datum.

Lines 67 through 93 take care of returning a single result to the caller.

Lines 74 through 82 create a text value from a null-terminated directory entry (actually, ignore most of the struct dirent structure and just return the name portion). You first allocate a new text structure using palloc(); then set the structure size and copy the directory entry name into place. Notice that you don't copy the null-terminator: A text value should not be null-terminated. At line 84, you tell PostgreSQL that you are returning a result and there may be more results, so keep calling. Next, you increment the array index so that the next call to filelist() will return the next directory entry. Finally, you return the directory entry to the caller in the form of a text value.

Notice that the context structure in this section of code has not been freed. You need to preserve the dir_ctx structure until you have processed the last directory entry.

You reach Lines 96 through 104 once you have returned all directory entries. This section is nearly identical to the code that deals with a scandir() failure (lines 58-64). In fact, the only difference is that you have one more thing to clean up. When you called the scandir() function, it allocated an array of struct dirent structures using malloc(). You have to free() that array before you finish up.

That completes the C part of this function; now you have to compile it into a shared object module and tell PostgreSQL where to find it. You can use the same makefile that you used to compile the filesize function:

$ make -f makefile filelist.so

As before, you'll create a symbolic link between filelist.so and PostgreSQL's preferred package directory:

$ ln -s `pwd`/filelist.so `pg_config --pkglibdir`

Now the only thing remaining is to tell PostgreSQL about the new function:

movies=# CREATE FUNCTION filelist( TEXT )
movies-# RETURNS SETOF TEXT
movies-# AS 'filelist.so' LANGUAGE 'C';
CREATE

Now, let's call filelist() to see how it works:

movies=# SELECT filelist( '/usr' );
 filelist
------------
 .
 ..
 bin
 dict
 etc
 games
 html
 include
 kerberos
 lib
 libexec
 local
 sbin
 share
 src
 tmp
 X11R6
(17 rows)

Notice that the results appear in sorted order. The ordering comes because you used the alphasort() function when you called scandir(). If you don't care about the ordering, you can specify a NULL comparison function instead. Of course, we can ask PostgreSQL to order the data itself:

movies=# SELECT filelist( '/usr' ) ORDER BY filelist DESC;
 filelist
------------
 X11R6
 tmp
 src
 share
 sbin
 local
 libexec
 lib
 kerberos
 include
 html
 games
 etc
 dict
 bin
 ..
 .
 (17 rows)

Now that you know how to create an SRF the hard way, I'll describe the new SRF interface that was introduced with PostgreSQL version 7.3.