Why Write PostgreSQL Extension Functions?

by Joe Conway
06/28/2004

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. Register now!

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 joe. As shown above, it then stores the name of the currently logged in user in a session variable named luser. Although the user_data 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_setvar(), myfunc_getvar(), and myfunc_rmvar(), as well as more complex C functions that manipulate arrays and return full rows, at my O'Reilly OSCON 2004 tutorial, "Power PostgreSQL."

I hope to see you there!

Joe Conway has been involved with PostgreSQL as a contributor since 2001.

---

Return to ONLamp.com