Extending the PostgreSQL Server with Custom Functions
| An extension function is loaded into a running PostgreSQL server process as needed. If you don't actually use an extension, it will not be loaded. Extension functions must be created in the form of a dynamically loadable object module. In the Windows world, an extension is contained within a DLL. In the Linux/Unix environment, an extension is contained within a shared object module. There are two phases to the process of adding an extension function to the PostgreSQL server. First, you create the extension function in the language of your choice, compiling it into a dynamic object module ( .dll or .so ). Next, tell the PostgreSQL server about the function. The CREATE FUNCTION command adds a new function to a database. I'll show you two examples that should help clarify this process.
The first example adds a simple function, named filesize , to the PostgreSQL server. Given the name of a file, it returns the size of the file (in bytes). If the file does not exist, cannot be examined, or is not a regular [1] file, this function returns NULL . You might find this function (and the filelist () function shown later) useful for performing system administration tasks from within a PostgreSQL application. After you have created the filesize function, you can call it like this:
movies=# SELECT filesize( '/bin/bash' ); filesize ---------- 512668 We'll develop the filesize function in C. The filesize function takes a single argument ”a pathname in the form of a TEXT value. This function returns the size of the named file as an INTEGER value. 1 /* 2 ** Filename: filesize.c 3 */ 4 5 #include "postgres.h" 6 #include "fmgr.h" 7 #include <sys/stat.h> 8 9 PG_FUNCTION_INFO_V1(filesize); 10 11 Datum filesize(PG_FUNCTION_ARGS) 12 { 13 text * fileNameText = PG_GETARG_TEXT_P(0); 14 size_t fileNameLen = VARSIZE( fileNameText ) - VARHDRSZ; 15 char * fileName = (char *)palloc( fileNameLen + 1 ); 16 struct stat statBuf; 17 18 memcpy( fileName, VARDATA( fileNameText ), fileNameLen ); 19 fileName[fileNameLen] = ' Lines 5 and 6 #include two header files supplied by PostgreSQL. These files ( postgres.h and fmgr.h ) provide data type definitions, function prototypes , and macros that you can use when writing extensions. The <sys/stat.h> file included at line 7 defines the layout of the struct stat object used by the stat() function (described later). Line 9 uses the PG_FUNCTION_INFO_V1() to tell PostgreSQL that the function ( filesize() ) uses the version-1 calling convention. At line 11, you see the signature used for all version-1 functions. The filesize() function returns a Datum and expects a single argument. PG_FUNCTION_ARGS is a preprocessor symbol that expands to declare a consistently named parameter. So, your function definition expands from this: Datum filesize(PG_FUNCTION_ARGS) to this: Datum filesize( FunctionCallInfo fcinfo ) This might seem a little strange at first, but the version-1 argument accessor macros are written so that the single function argument must be named fcinfo . At line 13, you create a variable of type text . text is one of the data types defined in the postgres.h header file (or in a file included by postgres.h ). Whenever you write an extension function, you will be working with two sets of data types. Each function parameter (and the return value) will have a SQL data type and a C data type. For example, when you call the filesize function from within PostgreSQL, you pass a TEXT parameter: TEXT is the SQL data type. When you implement the filesize function in C, you receive a text value: text is the C data type. The name for the C data type is usually similar to the name of the corresponding SQL data type. For clarity, I'll refer to the PostgreSQL data types using uppercase letters and the C data types using lowercase letters . Notice that a macro is vused to retrieve the address of the TEXT value. I mentioned earlier that an extension function must use macros to access parameters, and this is an example of such a macro. The PG_GETARG_TEXT_P( n ) macro returns the n th parameter, which must be of type TEXT . The return value of PG_GETARG_TEXT_P( n ) is of type text . There are many argument-accessor functions, each corresponding to a specific parameter type: PG_GETARG_INT32( n ) , PG_GETARG_BOOL( n ) , PG_GETARG_OID( n ) , and so on. See the fmgr.h PostgreSQL header file for a complete list. We'll be using the stat() function (from the C Runtime library) to find the size of a given file. stat() expects to find the pathname in the form of a null- terminated string. PostgreSQL has given you a text value, and text values are not null-terminated. You will need to convert fileNameText into a null-terminated string. If fileNameText is not null-terminated, how do you know the length of the pathname? Let's take a peek at the definition of the text data type (from the c.h PostgreSQL header file): struct varlena { int32 vl_len; char vl_data[1]; }; typedef struct varlena text; You can see that a text value is defined by the struct varlena structure. The vl_len member tells you how many bytes are required to hold the entire structure. The characters that make up the text value start at the address of the v1_data[0] member. PostgreSQL supplies two macros that make it easy to work with variable-length data structures. The VARHDRSZ symbol contains the size of the fixed portion of a struct varlena . The VARSIZE() macro returns the size of the entire data structure. The VARDATA() macro returns a pointer to first byte of the TEXT value. The length of the TEXT value is VARSIZE() - VARHDRSZ . You store that length in the fileNameLen variable. At line 15, you allocate enough space to hold a copy of the null-terminated string. The palloc() function is similar to malloc() : It allocates the requested number of bytes and returns a pointer to the new space. You should use palloc() and pfree() when you write extension functions rather than malloc() and free() . The palloc() and pfree() functions ensure that you can't create a memory leak in an extension function, which is something you can do if you use malloc() instead. Lines 18 and 19 create a null-terminated copy of the TEXT value, and line 21 passes the null-terminated string to the stat() function. If the stat() function succeeds, it fills in the statBuf structure and returns 0. If you succeeded in retrieving the file status information and the file is a regular file, free the null-terminated string (using pfree() ) and return the file size. Notice that you must use a macro to translate the return value (an int32 ) into a Datum . If the stat() function failed (or the file is not a regular file), you free the null- terminated string and return NULL . Again, you use a macro to produce the return value in the form of a Datum . Now that you have crafted the filesize function, you need to compile it into a shared object module. You usually compile a C source file into a standalone executable program, but PostgreSQL expects to find the filesize function in a shared object module. The procedure for producing a shared object module is different for each compiler; section 12.5 of the PostgreSQL Programmer's Guide describes the process for a number of compilers. Here is the makefile that I've used to compile the filesize function using Red Hat Linux 7.2: # File name: makefile SERVER_INCLUDES += -I $(shell pg_config --includedir) SERVER_INCLUDES += -I $(shell pg_config --includedir-server) CFLAGS += -g $(SERVER_INCLUDES) .SUFFIXES: .so .c.so: $(CC) $(CFLAGS) -fpic -c $< $(CC) $(CFLAGS) -shared -o $@ $(basename $<).o To compile filesize using this makefile , you would issue the following command: $ make -f makefile filesize.so After the compile step is completed, you are left with a file named filesize.so in your current directory. The preferred location for a PostgreSQL extension can be found using the pg_config command: $ pg_config --pkglibdir /usr/local/pg721/lib/postgresql You can copy the filesize.so file to this directory, but I prefer to create a symbolic link pointing back to my development directory instead. After an extension is completely debugged , I delete the symbolic link and copy the final version into the preferred location. To create a symbolic link, use the following command: $ ln -s `pwd`/filesize.so `pg_config --pkglibdir` At this point, you have a shared object module, but you still have to tell PostgreSQL about the function that you want to import into the server. The CREATE FUNCTION command tells PostgreSQL everything it needs to know to call your function: movies=# CREATE OR REPLACE FUNCTION movies-# filesize( TEXT ) RETURNS INTEGER AS movies-# 'filesize.so', 'filesize' LANGUAGE 'C' movies-# WITH ( ISSTRICT ); CREATE This command defines a function named filesize( TEXT ) . This function returns an INTEGER value. The function is written in C and can be found in the file filesize.so in the preferred extension directory. You can specify a complete pathname to the shared object module if you want to, but in most cases it's easier to just put it where PostgreSQL expects to find it, as I've done here. You can also omit the filename extension (the .so part), as long as you follow the shared object module-naming rules imposed by your host operating system. I've defined filesize() as a strict function. The ISSTRICT attribute tells PostgreSQL that this function will always return NULL if any argument is NULL . If PostgreSQL knows that a function ISSTRICT , it can avoid calling the function with a NULL argument (again, a performance optimization). ISSTRICT makes it easier for you to implement your extension functions; you don't have to check for NULL arguments if you declare your functions to be ISSTRICT .
Now you can call the function from within a PostgreSQL session: movies=# SELECT filesize( '/bin/bash' ); filesize ---------- 512668 (1 row) movies=# SELECT filesize( 'non-existent file' ); filesize ---------- (1 row) |
EAN: 2147483647
Pages: 220