If you've read through the first few sections in this chapter, you know how to write an extension function that returns a single scalar value (that's what the filesize() function does). You also know how to return a set of scalar values (that's what the filelist() function does). In this section, I'll show you how to return a set of rows (or, as the PostgreSQL developers prefer to call them, tuples).
To illustrate the sequence that you must follow to return multiple tuples from an extension function, I'll create a new function, fileinfo(), that combines filesize() and filelist(). You call fileinfo() with the name of a directory and it returns a SETOF tuples. Each tuple contains three columns: a filename, the size of the file (or NULL if the size is not known), and the file type (or NULL if the type is not known). When you've finished, you can call the fileinfo() function like this:
movies=# SELECT * FROM fileinfo( '/dev' ); filename | filesize | filetype ---------------+------------+---------- . | 9380 | d .. | 4096 | d adsp | 0 | c agpgart | 0 | c arpd | 0 | c audio | 0 | c cdrom | 0 | b console | 0 | c core | 1073156096 | - cpu | 360 | d ...
To start, you must define a data type that describes each row returned by the fileinfo() function:
movies=# CREATE TYPE _fileinfo AS ( filename TEXT, filesize INTEGER, filetype CHAR(1)); CREATE TYPE
I'll create a few helper functions that will simplify the fileinfo() function. Listing 6.6 shows the getFileInfo(), getFileType(), and text2cstring() functions:
Listing 6.6. fileinfo.c (Part 1)
1 /* 2 ** Filename: fileinfo.c 3 */ 4 #include "postgres.h" 5 #include "funcapi.h" 6 7 #include 8 #include 9 10 typedef struct 11 { 12 struct dirent ** dir_ctx_entries; 13 char * dir_ctx_name; 14 } dir_ctx; 15 16 static bool getFileInfo(struct stat * buf, char * dirName, char * fileName) 17 { 18 char * pathName = (char *) palloc(strlen(dirName)+1+strlen(fileName)+1); 19 20 strcpy( pathName, dirName ); 21 strcat( pathName, "/" ); 22 strcat( pathName, fileName ); 23 24 if( stat( pathName, buf ) == 0 ) 25 return( true ); 26 else 27 return( false ); 28 } 29 30 static char getFileType( mode_t mode ) 31 { 32 if( S_ISREG(mode)) 33 return( '-' ); 34 if( S_ISDIR(mode)) 35 return( 'd' ); 36 if( S_ISCHR(mode)) 37 return( 'c' ); 38 if( S_ISBLK(mode)) 39 return( 'b' ); 40 if( S_ISFIFO(mode)) 41 return( 'p' ); 42 if( S_ISLNK(mode)) 43 return( 'l' ); 44 if( S_ISSOCK(mode)) 45 return( 's' ); 46 47 return( '?' ); 48 49 } 50 51 static char * text2cstring( text * src ) 52 { 53 int len = VARSIZE( src ) - VARHDRSZ; 54 char * dst = (char *)palloc( len+1 ); 55 56 memcpy( dst, src->vl_dat, len ); 57 dst[len] = '