Why Write PostgreSQL Extension Functions?by Joe Conway
Have you ever wanted (or needed) to process your data in a way that your database cannot handle natively? You're not alone. One of my favorite capabilities of PostgreSQL is its extensibility. You can extend PostgreSQL's native functionality using one of the five procedural languages shipped with PostgreSQL or one of several independently available procedural language handlers. However, the most powerful way to extend PostgreSQL is, arguably, by writing your functions in C.
Joe Conway will present Power PostgreSQL: Extending the Database with C. Discover how powerful PostgreSQL can be by developing your own user defined C functions, and deliver superior results to your customers. This tutorial will give you an understanding from start to finish with respect to writing user defined PostgreSQL C functions.
For example, suppose you want to implement session variables, that is, variables that survive for the life of a database connection. Furthermore, you do not want to use persistent storage. A typical scenario where this might be the case is an application that manages security at the application layer and uses a common and generic login to the database itself, regardless of user. The session variable you would track in this instance is the currently logged in user. You don't need to maintain this information beyond one session, and you don't want the overhead of storing the simple variable in a table.
This is easily doable with a few relatively simple C functions that use the PostgreSQL back end's "dynahash" functionality. You need one function to place a named variable into memory that will last until the end of the connection. You need another to retrieve that named variable. Finally, you might want to remove a previously stored variable. In practice, the result looks like this:
CREATE TABLE user_data ( id int primary key, luser text unique, name text, city text, country text ); INSERT INTO user_data VALUES(1, 'joe', 'Joe', 'San Diego', 'USA'); INSERT INTO user_data VALUES(2, 'bob', 'Bob', 'Portland', 'USA'); SELECT myfunc_setvar('luser','joe'); myfunc_setvar --------------- OK (1 row) SELECT * FROM user_data WHERE luser = myfunc_getvar('luser'); id | luser | name | city | country ----+-------+------+-----------+--------- 1 | joe | Joe | San Diego | USA (1 row) SELECT myfunc_rmvar('luser'); myfunc_rmvar -------------- OK (1 row) SELECT * FROM user_data WHERE luser = myfunc_getvar('luser'); id | luser | name | city | country ----+-------+------+------+--------- (0 rows)
The application login first needs to authenticate the user
As shown above, it then stores the name of the currently logged in user in a
session variable named
luser. Although the
table holds personal information for all registered users of the application,
by filtering the
SELECT with the value of our session variable,
joe can see only his own personal information. We log
joe out by removing the
luser session variable.
You can now simplify the job of your application programmer by wrapping the
SELECT in a view:
CREATE OR REPLACE VIEW USER_DATA_VW AS SELECT * FROM user_data WHERE luser = myfunc_getvar('luser'); SELECT myfunc_setvar('luser','joe'); myfunc_setvar --------------- OK (1 row) SELECT * FROM user_data_vw; id | luser | name | city | country ----+-------+------+-----------+--------- 1 | joe | Joe | San Diego | USA (1 row)
This approach allows the application programmer to set the
luser variable at the beginning of a session and then access all
further information via secured views, comfortable in the knowledge that the
view takes care of all of the appropriate filtering.
You can learn how to write functions such as
myfunc_rmvar(), as well as more
complex C functions that manipulate arrays and return full rows, at my O'Reilly
OSCON 2004 tutorial, "Power
I hope to see you there!
Joe Conway has been involved with PostgreSQL as a contributor since 2001.
Return to ONLamp.com