PL/JavaWriting Stored Procedures in Java

PL JavaWriting Stored Procedures in Java

PL/Java is a procedural language. When you write a PL/Java function, you write normal Java code, compile it to bytecode form (that is, compile the source code into a .class file), store the compiled form in a Java archive file (a .jar file), and then load that archive into the database. When you execute a PL/Java function, the PL/Java call handler creates a Java Virtual Machine (JVM) within the PostgreSQL server process and then asks the JVM execute the bytecode form of your function. A PL/Java function corresponds to a method of some Java class. Since a PL/Java function is a Java method, you can use Java control structures, Java classes, Java data types, Java functions, and Java interfaces. In fact, a PL/Java function (which runs within the PostgreSQL server) can interact with the PostgreSQL database through the standard JDBC interface. (PL/Java includes a "loopback" JDBC driver.)

PL/Java is based on the proposed SQL 2003 standard. That means that you can write stored procedures and functions in PL/Java and use the same code (source code or compiled) with any database system that supports the standard (Oracle, Sybase, and possibly others). The PL/Java package includes a set of deployment aids that makes it easy to install and manage server-side functions written in Java. PL/Java does not include a Java compiler or a Java Virtual Machineyou must obtain those components from a separate source.

Installing PL/Java

Installing PL/Java is surprisingly easy, but you have to have a few pieces in place first. The PL/Java developers have written a Java-based installer that will move the required class files into place, define PL/Java as a trusted server-side language, and create a repository for your class files. You have to install PL/Java into each database that you create, but you can install PL/Java into the template1 database to automatically include PL/Java in all databases that you create in the future[1].

[1] Every new database is created by making a clone of template1 (or a database of your choice).

Before you install PL/Java, you'll need a properly installed and configured Java virtual machine, the PostgreSQL JDBC driver (see Chapter 13, "Using PostgreSQL from a Java Client Application"), and the PL/Java distribution.

Installing PL/Java is currently a three-step process. First, you install the PL/Java call handler into a cluster. Then you configure the postmaster so that it can find the PL/Java components. Finally, you install the PL/Java language, functions, types, and repository into a database. The installation procedure is likely to change in the near future so be sure to check the release notes before you go any further. I'll outline the steps required to install PL/Java version 1.1 on a Linux host (running PostgreSQL version 8.0).

To install the call handler into a cluster, first unpack the PL/Java tarball (you'll find it at gborg.postgresql.org or pgfoundry.org) into a temporary directory. Next, move the PL/Java call handler into the cluster with the following commands:

$ mv libpljava.so $(pg_config pkglibdir)
$ mv pljava.jar $(pg_config pkglibdir)

To configure the postmaster, edit the $PGDATA/postgresql.conf configuration file and add the following entries:

custom_variable_classes = 'pljava'
pljava.classpath = '$libdir/pljava.jar'

Now you can restart the postmaster (pg_ctl restart).

Finally, install the PL/Java into the database like this:

$ psql -f install.sql movies
CREATE SCHEMA
GRANT
CREATE FUNCTION
...

When the install.sql script completes, the PL/Java language is ready to use.

To summarize the PL/Java 1.1 installation process:

• Unpack the PL/Java tarball
• Move libpljava.so into the PostgreSQL cluster
• Move pljava.jar into the PostgreSQL cluster
• Modify $PGDATA/postgresql.conf so the Java VM can find the pljava.jar file
• Restart the postmaster
• Install the PL/Java components into each database

Writing a Simple PL/Java Function

With all of the prerequisites in place, you're ready to write some code. Listing 19.1 shows a simple Java class that computes the factorial of a given number.

Listing 19.1. MyMath.java

 1 // FileName: MyMath.java
 2
 3 public class MyMath
 4 {
 5 public static int myFactorial( Integer value )
 6 throws IllegalArgumentException
 7 {
 8 if( value == null )
 9 throw new IllegalArgumentException( "argument must not be NULL" );
10
11 int intValue = value.intValue();
12
13 if( intValue < 0 )
14 throw new IllegalArgumentException( "argument must be >= 0" );
15
16 if( intValue == 1 )
17 return( 1 );
18 else
19 {
20 int result;
21
22 result = myFactorial( new Integer( intValue - 1 ));
23 result = result * intValue;
24
25 return( result );
26 }
27 }
28 }

The MyMath class exports a single static method: myFactorial( Integer ). Once you've compiled and installed this code, you can call the myFactorial() method (from a PostgreSQL client application) like this:

movies=# SELECT myFactorial( 5 );
 myfactorial
------------
 120
(1 row)

Take a close look at line 5. Notice that myFactorial() returns an int, but expects an Integer argument. Why two different data types? There's no way to store a NULL value in a non-reference type (such as int). If you want an argument (or a return value) that can hold a NULL value, you must use a reference type such as Integer. By looking at the signature for this method, you can tell that it will never return NULL (because the return type, int, is a primitive non-reference type) and that it should be able to handle a NULL argument (because the argument type, Integer, is a reference type).

You should also note that you can tHRow exceptions from a PL/Java function. The PL/Java call handler catches any unhandled exceptions and translates them into PostgreSQL errors. For example, if you call the myFactorial() function with a NULL argument, the tHRow at line 9 will be handled like this:

movies=# SELECT myFactorial( NULL );
ERROR: java.lang.IllegalArgumentException: argument must not be NULL

To install the MyMath class (and the MyMath.myFactorial() function), I'll first compile the MyMath.java source code:

$ javac MyMath.java

The Java compiler translates MyMath.java into bytecode form and stores the result in a file named MyMath.class. It's possible to install a .class file in PostgreSQL, but I'll show you in a moment that PL/Java prefers to work with Jar (Java Archive) files instead. To create a jar file named MyMath.jar:

$ jar -c -f /tmp/MyMath.jar MyMath.class

The syntax for the jar command is very similar to the syntax for tar. In this case, the -c flags tells jar to create an archive file. The -f MyMath.jar option tells jar to store the archive in a file named MyMath.jar (in the current directory). The last argument specifies the name of the .class file that I want to store in the archive. (You can list as many .class files as you want.)

At this point, you should have a Java source code file (MyMath.java), a compiled bytecode file (MyMath.class), and a Java archive (MyMath.jar) that contains a copy of MyMath.class. Now it's time to tell PostgreSQL about the MyMath class.

When you add PL/Java to a database, the installer creates a bytecode repository inside of the database. Each entry in the repository contains a copy of a jar file, a record of where the jar file came from, and a symbolic name for the jar file. When you invoke a PL/Java function, the PostgreSQL server loads the Java interpreter and creates a Java VM (the VM runs within the PostgreSQL server process). To find the bytecode for the function that you've invoked, the VM searches the bytecode repository in your database. To install a jar file into the bytecode repository, invoke the sqlj.install_jar() function (that is, the install_jar() function in the sqlj schema):

$ psql movies
movies=# SELECT sqlj.install_jar( 'file:///tmp/MyMath.jar', 'Math', true );
install_jar
-----------

(1 row)

The sql.install_jar() function copies /tmp/MyMath.jar into the repository (specifically, into a table named sql.jar_entry) and assigns the symbolic name Math to that entry (the symbolic name is stored in sqlj.jar_repository). The sqlj.install_jar() function expects three arguments: a URL that resolves to a jar file (typically, you call install_jar() with a URL that begins with file:///), a symbolic name for the jar file, and a boolean value that tells PL/Java whether to process the deployment descriptor contained in the jar file. (I'll explain deployment descriptors a little later in this chapter.) You can delete the MyMath.jar and MyMath.class files at this point if you wish.

PL/Java associates a classpath with each schema in a database. A classpath is a colon-separated list of jar filenames. (You can include .class files as well.) When the Java VM needs to find the bytecode for a class, it searches through each jar file (and .class file) listed in the classpath, in order, until it finds a match. To assign a classpath to a schema, call the sqlj.set_classpath() function:

movies=# SELECT sqlj.set_classpath( 'public', 'Math' );
set_classpath
-------------

(1 row)

Now when you invoke a PL/Java function that's defined in the public schema, the Java VM will search the Math jar file for the bytecode form of the function. If you want the Java VM to search through multiple jar files, just list each one in the call to sqlj.set_classpath(). For example, if you want the VM to search through a jar file named Math, and then a jar file named Movies:

movies=# SELECT sqlj.set_classpath( 'public', 'Math:Movies' );
set_classpath
-------------

(1 row)

Just remember that the set_classpath() function expects a list of symbolic names, not the actual jar filenames. You should also remember that the VM searches through each element of the classpath in order until it finds the class that you're invoking. That means that if Math and Movies both define a class named VersionInfo, the VM will stop as soon as it finds the first one.

The last step in the process is easy: Tell the PostgreSQL server which class (and method) to invoke when you call the myFactorial() function. To do that, execute a CREATE FUNCTION command, like this:

movies=# CREATE FUNCTION myFactorial( INT ) RETURNS INT AS
movies-# 'MyMath.myFactorial( java.lang.Integer )'
movies-# LANGUAGE java;

Take careful notice of the data types mentioned in the CREATE FUNCTION command. The two data types that you see on the first line of the command (INT and INT) tell the PostgreSQL server what to expect. When you invoke myFactorial(), you call it with a single argument of type INT and it returns a value of type INT. The data type that you see on the second line (java.lang.Integer) tells PL/Java what kind of argument is expected by the Java bytecode. The Java data type serves two purposes. First, it tells the PL/Java call handler how to convert the PostgreSQL INT value into the form required by the method that you're defining. (That is, you're telling PL/Java to convert the INT argument into a java.lang.Integer value.) Second, the Java VM combines the method name (myFactorial()) and the argument data types (java.lang.Integer) to come up with a signature for the method. The signature gives the VM a way to distinguish between two methods that have the same name (but differing argument lists). Because Java searches for a method based on its signature (not just its name), you could create a myFactorial( java.lang.Integer ) method and a myFactorial( java.lang.Long ) method in the same class.

Once you've told PostgreSQL about the function by executing a CREATE FUNCTION command, you can call it like this:

movies=# SELECT myFactorial( 5 )
myfactorial
----------
 120
(1 row)

movies=# SELECT myFactorial( NULL )
ERROR: java.lang.IllegalArgumentException: argument must not be NULL

To summarize the process:

• Write the source code for your Java method
• Compile the source code into a .class file
• Store the .class file in a Java archive (that is, a jar file)
• Copy the jar file into the PL/Java bytecode repository by calling the sqlj.install_jar() function
• Ensure that the new jar file (the one you just added to the bytecode repository) is mentioned in your schema's search path (call the sqlj.set_classpath() function)
• Execute a CREATE FUNCTION command for each method that you want to call from PostgreSQL

Accessing the Database from a PL/Java Function

To interact with the PostgreSQL database, a PL/Java function uses the JDBC package. I won't describe JDBC in detail in this chapter, see Chapter 13, "Using PostgreSQL from a Java Client Application," for an introduction to JDBC. Instead, I'll show you how to write some simple JDBC-enabled PL/Java functions that demonstrate a few of the quirks required by the PL/Java JDBC driver.

Listing 19.2 shows a Java source file that defines a single class (Movies) and two methods (getBalance() and getTotal()). The getBalance() method returns the current balance for a given customer. The getTotal() method returns the total (current) balance for all customers.

Listing 19.2. Movies.java

 1 // File: Movies.java
 2
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.Statement;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9
10 public class Movies
11 {
12 private static String m_url = "jdbc:default:connection";
13
14 public static double getBalance( int id ) throws SQLException
15 {
16 Connection conn = DriverManager.getConnection( m_url );
17 String query = "SELECT * FROM customers WHERE customer_id = ?";
18
19 PreparedStatement stmt = conn.prepareStatement( query );
20
21 stmt.setInt( 1, id );
22
23 ResultSet rs = stmt.executeQuery();
24
25 rs.next();
26
27 double balance = rs.getFloat( "balance" );
28
29 stmt.close();
30 conn.close();
31
32 return( balance );
33 }
34
35 public static double getTotal( ) throws SQLException
36 {
37 Connection conn = DriverManager.getConnection( m_url );
38 Statement stmt = conn.createStatement();
39
40 ResultSet rs = stmt.executeQuery( "SELECT * FROM customers" );
41
42 double total = 0;
43
44 while( rs.next())
45 {
46 total += rs.getFloat( 5 );
47 }
48
49 stmt.close();
50 conn.close();
51
52 return( total );
53 }
54 }

A JDBC client application connects to a database using a JDBC driver. The JDBC package locates the proper driver by examining a URL provided by the client. A typical URL specifies a driver name, database name, username, password, and so on. For example, to connect to a PostgreSQL database from an external JDBC client, you would code an URL that looks like this:

jdbc:org.postgresql?user=korry&password=cows

The leading part of the URL (the stuff before the question mark) tells JDBC to load the driver provided by org.postgresql. The trailing part of the URL (the stuff that follows the question mark) specifies a set of connection properties that are understood by the org.postgresql driver. (If you connect to a different database system, say Oracle, the connection properties would look very different.)

The PL/Java procedural language includes a "loopback driver." Rather than connecting to an external database (the way a normal JDBC driver would), the loopback driver connects back to the PostgreSQL server. To connect to the loopback driver, you always use the same URL: jdbc:default:connection (see Listing 19.2, line 12).

The first method exposed by the Movies class (getBalance()), connects to the PostgreSQL server and creates a PreparedStatement object. A PreparedStatement object prepares an SQL command for later execution. You typically uses a PreparedStatement (as opposed to a Statement) when you want to execute a command repeatedly, or when the command contains one or more parameters whose values you fill in at a later time. The SELECT command (see line 17) managed by the PreparedStatement includes a single parameter indicated by the question mark at the end of the WHERE clause. The call to conn.prepareStatement() (at line 19) sends the query to the server for parsing and planning and returns a handle to the PreparedStatement. Before you can execute the query, you must provide a value for the parameter (the question mark in the WHERE clause) and that's what the call to stmt.setInt() takes care of (see line 21). As I mentioned in the Chapter 13, an object of type PreparedStatement supports a variety of parameter-substitution methods. In this case, we're providing a value for the customer_id column (which happens to be an INTEGER value) so I'll call the setInt() method. See the section titled PreparedStatement in Chapter 13 for a list of related methods.

At line 23, the call to stmt.executeQuery() asks the PostgreSQL server to execute the query that I've prepared. executeQuery() returns a reference to a ResultSet object. A ResultSet contains a copy of all the data and a copy of all the metadata returned by a command. Each ResultSet maintains a pointer to the "current row" within the ResultSet. Initially, the pointer is positioned just before the first row in the ResultSet. To get to the values stored in the first row, I'll call the rs.next() method (see line 25).

Result Set Navigation in PL Java

When you create a ResultSet object in a typical JDBC application, you can scroll forward and backward through the rows in the result set. You can also move to the first row or the last row, or to a position just before the first row or just after the last row. At the time we are writing this, PL/Java version 1.1 supports a single navigation method: ResultSet.next(). If you try to call any of the other navigation methods (first(), last(), previous(), and so forth), the ResultSet will throw an UnsupportedFeatureException. You can still call the navigation predicate methods (isFirst(), isLast(), isBeforeFirst(), isAfterLast(), and getrow()); you just can't move any direction other than forward.

The call to rs.getFloat() (see line 27) extracts the balance column from the current row (the first row) and returns a float value. The remainder of the method closes the PreparedStatement, closes the connection, and returns the balance to the caller.

The second method exposed by the Movies class (getTotal()) is similar to the first. In fact, there are only two significant changes. First, getTotal() creates a Statement object instead of a PreparedStatement. A PreparedStatement offers no advantages in this case because I only need to execute the query once and the query doesn't contain any parameters. I could use a PreparedStatement in this method, but a Statement object is easier to work with. Second, getTotal() loops through every row in the ResultSet, accumulating the total value as it goes. As I mentioned earlier, you call the next() method to iterate through the rows in a ResultSet: next() returns FALSE when you've read past the last row.

As you can see, it's easy to interact with the database from within a PL/Java function. It's important to note that the PL/Java loopback driver creates a connection to the caller's PostgreSQL session. When you connect to the loopback driver, the SQL commands that you invoke execute in the same transaction as the function. Your function sees the same user ID, the same search_path, and the same temporary ResultSet object tables as the caller.

Returning Multiple Results from a PL/Java Function

Writing a PL/Java function that returns multiple rows is a bit more complicated. PL/Java provides three different mechanisms for writing a function that returns a SETOF some data type. In the simplest case, you can return a SETOF scalar (simple) values by returning an Iterator. If you want to return a SETOF complex (non-scalar) values, you can return a ResultSet created by a JDBC method or you can create your own ResultSet. In this section, I'll show you all three methods.

Listing 19.3 shows a simple PL/Java function that returns a SETOF string values:

Listing 19.3. Tapes.java

 1 // File: Tapes.java
 2
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.Statement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import java.util.Iterator;
 9 import java.util.ArrayList;
10
11 public class Tapes
12 {
13 private static String m_url = "jdbc:default:connection";
14
15 public static Iterator getTapeIDs( )
16 throws SQLException
17 {
18 Connection conn = DriverManager.getConnection( m_url );
19 Statement stmt = conn.createStatement();
20 ResultSet rs = stmt.executeQuery( "SELECT tape_id FROM tapes" );
21 ArrayList result = new ArrayList();
22
23 while( rs.next())
24 {
25 result.add( rs.getString( "tape_id" ));
26 }
27
28 return( result.iterator());
29 }
30 }

When you call a function defined as RETURNS SETOF scalarType, PL/Java expects to find a method that returns a java.util.Iterator. In this case, the getTapeIDs() creates an ArrayList that will hold each tape_id returned by the query that you see at line 20. The while loop (starting at line 23) copies the each row from the result set into the ArrayList. Line 28 creates an Iterator that can read through the ArrayList and returns a reference to that Iterator. When the method completes, PL/Java reads through the Iterator and copies each row into the result set that the PostgreSQL server is assembling.

The getTapeIDs() method shown in Listing 19.3 materializes the entire result set before returning to the caller. (That is, it copies all of the result rows into a container and then returns an Iterator that reads from that container.) Materializing a large result set can be somewhat inefficient because you have to copy the entire set into a container and then let PL/Java read through the container. A better approach is to convert a ResultSet into an Iterator on demand. Listing 19.4 shows an alternative version of getTapeIDs() that returns a hand-crafted Iterator. I should warn you that this code will not work with PL/Java version 1.1 because of a bug that has been fixed (but the fix has not yet been released at the time of this writing).

Listing 19.4. Tapes2.java

 1 // File: Tapes2.java
 2
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.Statement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import java.util.NoSuchElementException;
 9 import java.util.Iterator;
10
11 public class Tapes2 implements Iterator
12 {
13 private static String m_url = "jdbc:default:connection";
14 private ResultSet m_rs;
15
16 public Tapes2() throws SQLException
17 {
18 Connection conn = DriverManager.getConnection( m_url );
19 Statement stmt = conn.createStatement();
20
21 m_rs = stmt.executeQuery( "SELECT tape_id FROM tapes" );
22 }
23
24 public static Iterator getTapeIDs( ) throws SQLException
25 {
26 return new Tapes2();
27 }
28
29 public boolean hasNext()
30 {
31 try
32 {
33 return !( m_rs.isLast() );
34 }
35 catch( Exception e )
36 {
37 return( false );
38 }
39 }
40
41 public Object next()
42 {
43 try
44 {
45 m_rs.next();
46
47 return( m_rs.getString( "tape_id" ));
48 }
49 catch( Exception e )
50 {
51 throw new NoSuchElementException( "No more tapes" );
52 }
53 }
54
55 public void remove()
56 {
57 throw new UnsupportedOperationException("No way");
58 }
59 }

In this version, the getTapeIDs()method returns a reference to a new Tapes2 object (see line 26). Since Tapes2 implements the Iterator interface (see line 11), returning a reference to a Tapes2 object is the same thing as returning a reference to an Iterator. The Tapes2 constructor (line 16) connects to the database (using the PL/Java loopback driver), executes a simple query, and stores the ResultSet in a member variable (m_rs). Now it's time to implement the Iterator interface. Iterator defines three methods: boolean hasNext(), Object next(), and void remove(). The caller (the PL/Java call handler) invokes hasNext() (line 29) repeatedly to find out whether there are any more values accessible through the Iterator. hasNext() simply calls the m_rs.isLast() method to detect when the ResultSet has been exhausted. If hasNext() returns true, the PL/Java call handler invokes next() (line 41) to retrieve the next value. next() navigates to the next row in the m_rs ResultSet, extracts the tape_id column, and returns it to the caller. The last Iterator method, remove(), is very simple. We can't remove a row from the result set (and PL/Java will never ask us to), so remove() throws an UnsupportedOperationException. As I mentioned earlier, the code shown in Listing 19.4 will not work properly with the currently released version of PL/Java (1.1)if you try to invoke this method, you'll see the first row in the result set, and then the second call to m_rs.isLast() will throw an exception (Stale Handle to native structure). This bug may be fixed by the time you're reading this book, so go ahead and give it a try.

PL/Java provides two different mechanisms for returning a SETOF complex values, but in each case, your code returns a ResultSet. Listing 19.5 shows a Java class (Rentals) that defines a single callable function (getrentals()). The getrentals() method returns a SETOF tuples. When you call getrentals(), you provide a customer_id and it returns information about each tape rented by that customer. For example:

movies=# SELECT * FROM getRentals( 3 );
 tape_id | title | rental_date
+-+-
 AB-67472 | The Godfather | 2001-11-25
 MC-68873 | Casablanca | 2001-11-20
(2 rows)

I'll show you two different implementations for this function. The first method (Listing 19.5) demonstrates the ResultSetHandle interface. The second method (Listing 19.6) shows you how to create a ResultSet on-the-fly.

Listing 19.5. Rentals.java

 1 // File: Rentals.java
 2
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import org.postgresql.pljava.ResultSetHandle;
 9
10 public class Rentals implements ResultSetHandle
11 {
12
13 private PreparedStatement m_stmt;
14 private int m_id;
15 private static String m_url = "jdbc:default:connection";
16 private static String m_query =
17 "SELECT tapes.tape_id, title, rental_date" +
18 " FROM rentals, tapes" +
19 " WHERE rentals.customer_id = ?" +
20 " AND tapes.tape_id = rentals.tape_id";
21
22 public Rentals( int id )
23 {
24 m_id = id;
25 }
26
27 public static ResultSetHandle getRentals( int id ) throws SQLException
28 {
29 return new Rentals( id );
30 }
31
32 public ResultSet getResultSet() throws SQLException
33 {
34 Connection conn = DriverManager.getConnection( m_url );
35
36 m_stmt = conn.prepareStatement( m_query );
37 m_stmt.setInt( 1, m_id );
38
39 return( m_stmt.executeQuery( ));
40 }
41
42 public void close() throws SQLException
43 {
44 m_stmt.close();
45 }
46 }

One way to return a SETOF complex values is to implement the ResultSetHandle interface. (Note that ResultSetHandle is defined in the org.postgresql.pljava package.) The Rentals class (line 10) defines four methods: a constructor (line 22), the function that you expose to PostgreSQL (line 27), and the two methods required by the ResultSetHandle interface (lines 32 and 42).

When you use this class from within PostgreSQL, you call the geTRentals() static method. getrentals() simply creates a new Rentals object and returns a reference to that object. Since Rentals implements the ResultSetHandle interface, returning a reference to a Rentals object is the same thing as returning a reference to a ResultSetHandle. The Rentals constructor (line 22) is equally simpleit just stores a copy of the id argument in the m_id class variable.

geTResultSet() is the first of two methods required by the ResultSetHandle interface. When PL/Java invokes the geTResultSet() method, it's expecting a ResultSet that it can map into a SETOF of the appropriate type. Where do you get a ResultSet? Just execute a command. In this case, getresultSet() connects to the PostgreSQL server, creates a PreparedStatement, fills in the query parameter (see lines 16 and 37), executes the query, and returns the ResultSet provided by m_stmt.executeQuery(). The second method required by the ResultSetHandle interface is close() (see line 42). The PL/Java call handler calls close() when it has finished mapping the ResultSet into a SETOF valuesas you can see, my implementation simply invokes the m_stmt.close() method.

The ResultSetHandle interface works well when you want to return a SETOF values that you can read directly from the PostgreSQL database (or even from some other database). But what if you want to build the ResultSet yourself? In that case, you should create a class the implements the ResultSetProvider interface. Listing 19.6 shows an alternate version of the geTRentals() function. It returns the same set of rows returned by the original version, but this version creates a ResultSet on the fly.

Listing 19.6. Rentals2.java

 1 // File: Rentals2.java
 2
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import org.postgresql.pljava.ResultSetProvider;
 9
10 public class Rentals2 implements ResultSetProvider
11 {
12
13 private PreparedStatement m_stmt;
14 private ResultSet m_rs;
15 private static String m_url = "jdbc:default:connection";
16 private static String m_query =
17 "SELECT tapes.tape_id, title, rental_date" +
18 " FROM rentals, tapes" +
19 " WHERE rentals.customer_id = ?" +
20 " AND tapes.tape_id = rentals.tape_id";
21
22 public Rentals2( int id )
23 throws SQLException
24 {
25 Connection conn = DriverManager.getConnection( m_url );
26
27 m_stmt = conn.prepareStatement( m_query );
28 m_stmt.setInt( 1, id );
29
30 m_rs = m_stmt.executeQuery();
31 }
32
33 public static ResultSetProvider getRentals( int id )
34 throws SQLException
35 {
36 return new Rentals2( id );
37 }
38
39 public boolean assignRowValues( ResultSet dst, int rowNum )
40 throws SQLException
41 {
42 if( m_rs.next())
43 {
44 dst.updateString( 1, m_rs.getString( 1 ));
45 dst.updateString( 2, m_rs.getString( 2 ));
46 dst.updateDate( 3, m_rs.getDate( 3 ));
47
48 return( true );
49 }
50 else
51 {
52 return( false );
53 }
54 }
55
56 public void close()
57 throws SQLException
58 {
59 m_stmt.close();
60 }
61 }

The Rentals2 class implements the org.postgresql.pljava.ResultSetProvider interface by providing two methods: assignRowValues() and close().

When you use this class from within PostgreSQL, you call the getrentals() static method. Like the previous version, this version of geTRentals() simply creates a new Rentals2 object and returns a reference that object. Since Rentals implements the ResultSetProvider interface, returning a reference to a Rentals2 object is the same thing as returning a reference to a ResultSetProvider.

The Rentals2 constructor (line 22) connects to the PostgreSQL server, creates a PreparedStatement using the query defined at line 16, fills in the query parameter, and executes the query. Unlike the previous version, this constructor stores the ResultSet returned by m_stmt.executeQuery() in a member variable (m_rs).

After getrentals() returns a ResultSetProvider, the PL/Java call handler re peatedly invokes the assignRowValues() method until it returns false. The assignRowValues() method expects two arguments: a ResultSet and a row number. The assignRowValues() fills in the given ResultSet (dst) one row at a time. When it runs out of results, assignRowValues() returns false to tell the PL/Java call handler that the ResultSet is complete. In this example, assignRowValues() simply reads through one result set (m_rs) and copies the column values into the ResultSet provided by the caller. In a more realistic scenario, you may compute some (or all) of the values that you store in the caller's ResultSet.

When assignRowValues() returns false, the PL/Java call handler invokes the close() method (the second method required by the ResultSetProvider interface).

The ResultSetHandle and ResultSetProvider mechanisms seem very similar at first glance. Each mechanism returns a ResultSet that the PL/Java call handler converts into a SETOF rows. Each interface requires two methods. How do you choose the right one? Use the ResultSetHandle interface when you already have a ResultSet that contains the values that you want to return. You obtain a ResultSet by executing a command against a database, typically a PostgreSQL database, but you can certainly retrieve a ResultSet from any JDBC driver. Use the ResultSetProvider interface when you want to construct a ResultSet from computed values. The ResultSetProvider interface is more flexible than ResultSetHandle. (As you've already seen, a ResultSetProvider can do anything that a ResultSetHandle can, but the reverse is not true.)

Writing PL/Java Trigger Functions

A trigger is a function that executes in response to a specific event in a given table. The PostgreSQL server automatically invokes a trigger (if defined) when you execute an INSERT command, an UPDATE command, or a DELETE command. Creating a trigger function in PL/Java is simply a matter of writing a method that expects a single argument: a reference to an object of type triggerData. The PL/Java call handler fills in the triggerData object before calling your code.

A triggerData object contains information about the event that caused the trigger to execute. For example, to determine the type of statement that fired the trigger, you can call the triggerData's isFiredByInsert(), isFiredByUpdate(), and isFiredByDelete() methods. When you add a trigger to a table (by executing a CREATE TRIGGER command), you can tell PostgreSQL whether to execute the trigger once for each modified row or to execute the trigger when the INSERT, UPDATE, or DELETE statement completes. A triggerData object exposes two methods that you can use to determine how often the trigger executes: isFiredForEachRow() and isFiredForStatement(). When isFiredForEachRow() returns true, the triggerData object holds a copy of the new row (in the case of a trigger fired by an INSERT command), a copy of the old row (in the case of a trigger fired by a DELETE command), or both (when the trigger is fired by an UPDATE command). To access the new row, call the getNew() method. To access the old row, call the getOld() method. Both methods return a one-row ResultSet. Table 19.1 summarizes the information that you can extract from a triggerData object:

Table 19.1. triggerData Methods

Method Name	Description
boolean isFiredByInsert()	Returns true if the trigger was fired by an INSERT command.
boolean isFiredByUpdate()	Returns TRue if the trigger was fired by an UPDATE command.
boolean isFiredByDelete	Returns TRue if the trigger was fired by a DELETE command.
boolean isFiredForEachRow()	Returns TRue if the trigger is fired for each row modified by an INSERT, UPDATE, or DELETE command (returns the opposite of isFiredForStatement()).
boolean isFiredForStatement()	Returns true if the trigger is fired when an INSERT, UPDATE, or DELETE command completes (returns the opposite of isFiredForEachRow()).
boolean isFiredBefore()	Returns true if the trigger executes before the command begins (in the case of a statement-level trigger) or before each row is modified (in the case of a row-level trigger).
boolean isFiredAfter()	Returns TRue if the trigger executes after the command completes (in the case of a statement-level trigger) or after each row is modified (in the case of a row-level trigger).
ResultSet getNew()	Returns the new row for an INSERT or UPDATE command. (You should only call this method when isFiredByInsert() or isFiredByUdate() returns true and isFiredForEachRow() returns TRue.)
ResultSet getOld()	Returns the new old for a DELETE or UPDATE command. (You should only call this method when isFiredByDelete() or isFiredByUdate() returns true and isFiredForEachRow() returns true.)
String getName()	Returns the name of the trigger.
String getTableName()	Returns the name of the modified table.
String[] getArguments()	Returns the (optional) trigger arguments defined by the CREATE TRIGGER command.

Listing 19.7 shows a class that will create an audit log of all changes made to the customers table. After you compile and install this PL/Java function (and turn it into a trigger), PostgreSQL will invoke the archiveCustomer() method when you modify the customers table. Each time archiveCustomer() is invoked, it adds a new row to the customer_archive table. If you DELETE a customer, archiveCustomer() copies the obsolete data into customer_archive. If you INSERT a customer, archiveCustomer() copies the new data into customer_archive. If you UPDATE a customer, archiveCustomer() adds two rows to customer_archivethe first row contains the original values and the second row contains the new values. Each row in the customer_archive table contains a complete copy of a customers row plus a few extra audit fields. Here's the complete definition of the customer_archive table:

CREATE TABLE customer_archive
(
 customer_id INTEGER,
 customer_name VARCHAR(50) NOT NULL,
 phone CHAR(8),
 birth_date DATE,
 balance DECIMAL(7,2),
 user_changed VARCHAR,
 date_changed DATE,
 operation VARCHAR
);

The user_changed column contains the name of the user that fired the trigger. The date_changed column contains the modification date. operation will contain INSERT or DELETE to indicate the type of modification. (An UPDATE is recorded as a DELETE followed by an INSERT.)

Listing 19.7. Archive.java

 1 // File: Archive.java
 2
 3 import java.sql.Connection;
 4 import java.sql.DriverManager;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8
 9 import org.postgresql.pljava.TriggerData;
10
11 public class Archive
12 {
13 private static String m_url = "jdbc:default:connection";
14
15 public static void archiveCustomer( TriggerData td )
16 throws SQLException
17 {
18 if( td.isFiredForStatement())
19 return;
20
21 if( td.isFiredByInsert())
22 addArchive( td.getNew(), "INSERT" );
23 else if( td.isFiredByDelete())
24 addArchive( td.getOld(), "DELETE" );
25 else
26 {
27 addArchive( td.getOld(), "DELETE" );
28 addArchive( td.getNew(), "INSERT" );
29 }
30 }
31
32 private static void addArchive( ResultSet values, String operation )
33 throws SQLException
34 {
35
36 String command =
37 "INSERT INTO customer_archive " +
38 "( " +
39 " customer_id, " +
40 " customer_name, " +
41 " phone, " +
42 " birth_date, " +
43 " balance, " +
44 " user_changed, " +
45 " date_changed, " +
46 " operation " +
47 ") " +
48 "VALUES " +
49 "( ?, ?, ?, ?, ?, CURRENT_USER, now(), ? )";
50
51 Connection conn = DriverManager.getConnection( m_url );
52 PreparedStatement stmt = conn.prepareStatement( command );
53
54 stmt.setInt( 1, values.getInt( "customer_id" ));
55 stmt.setString( 2, values.getString( "customer_name" ));
56 stmt.setString( 3, values.getString( "phone" ));
57 stmt.setDate( 4, values.getDate( "birth_date" ));
58 stmt.setBigDecimal( 5, values.getBigDecimal( "balance" ));
59 stmt.setString( 6, operation );
60
61 stmt.executeQuery();
62
63 stmt.close();
64 conn.close();
65 }
66 }

The archiveCustomer() method begins at line 15. If archiveCustomer() is invoked by a statement-level trigger, it won't have access to the old and new row values so it simply returns without doing any workif you want the old and/or new values in a trigger function, you must create a row-level trigger. The if/else statement starting at line 21 decides whether to add an INSERT record, a DELETE record, or (in the case of an UPDATE command), both. Note that each call to the addArchive() method provides a ResultSet and an operation. The ResultSet contains a copy of the new row values or a copy of the old row values, depending on whether you call td.getNew() or td.getOld().

The addArchive() method (line 32) adds a new row to the customer_archive table. After connecting to the PostgreSQL server, addArchive() creates a PreparedStatement and fills in the six parameter values. The customers values (customer_id, customer_name, and so on) are copied straight out of the ResultSet provided by the caller. The PostgreSQL server computes the values for the audit fields (user_changed, date_changed).

After compiling and installing this class, you can turn it into a trigger with the following commands:

movies=# CREATE FUNCTION archiveCustomer() RETURNS TRIGGER AS
movies-# 'Archive.addArchive';
CREATE FUNCTION

movies=# CREATE TRIGGER archiveCustomer
movies-# AFTER INSERT OR DELETE OR UPDATE
movies-# ON customers
movies-# FOR EACH ROW
movies-# EXECUTE PROCEDURE archiveCustomer();
CREATE TRIGGER

As you may have noticed by now, it takes quite a bit of tedious work to install a PL/Java function once the Java compiler turned your source code into bytecode form. The PL/Java developers have included a feature that makes it a little bit easier to manage jar files in a PostgreSQL database.

Adding Install/Uninstall Commands to a Jar File

When you create a jar file, you can include an optional deployment descriptor that specifies a sequence of SQL commands to execute when the jar file is installed and a sequence of SQL commands to execute when the jar file is uninstalled. A typical deployment descriptor contains one CREATE FUNCTION command (and a corresponding DROP FUNCTION command) for each function defined by the jar file. A deployment descriptor might also contain CREATE TYPE and CREATE TRIGGER commands. When you install the jar file (using the sqlj.install_jar() function), PL/Java executes the install-commands defined by the deployment descriptor. When you uninstall the jar file (using the sqlj.remove_jar() function), PL/Java executes the remove-commands defined by the deployment descriptor. When you add a deployment descriptor to a jar file, you're creating a self-installing archive that will clean up after itself should you remove the jar file from the database.

A deployment descriptor is a text file that contains a list of install-commands and a list of uninstall-commands. Listing 19.8 shows a typical deployment descriptor.

Listing 19.8. Movies.ddr

SQLActions[ ] = {
 "BEGIN INSTALL

 BEGIN PostgreSQL SET search_path TO public END PostgreSQL;

 CREATE FUNCTION getBalance(int) RETURNS float8
 AS 'Movies.getTotal(int)' LANGUAGE java;

 END INSTALL",

 "BEGIN REMOVE
 DROP FUNCTION getBalance(int);
 END REMOVE"
}

A deployment descriptor begins with the text SQLActions[ ] = { ands ends with a closing brace }. Between the braces you can write a "BEGIN INSTALL ... END INSTALL" section and a "BEGIN REMOVE ... END REMOVE " section. As you've probably guessed, the "BEGIN INSTALL ... END INSTALL" section contains a list of SQL commands that execute when you install the jar that contains the deployment descriptor. Likewise, the "BEGIN REMOVE ... END REMOVE " section contains a list of SQL commands that execute when you remove the jar from the database.

The deployment descriptor shown in Listing 19.8 contains two install-commands. The first command (SET search_path TO public) is wrapped inside of an implementor block. To understand implementor blocks, remember that Java's primary goal is portability. When you write a PL/Java class, you can load that class into other database systems (Oracle, Sybase, and so on). If you want to execute install-commands specific to a given database system, wrap the commands in an implementor block. When PL/Java sees an install-command (or uninstall-command) wrapped in a BEGIN implementor...END implementor pair, it only executes those commands where implementor is PostgreSQL.

You can create a deployment descriptor for a jar file with any text editor. Just decide which commands you want to execute when the jar file is installed and which commands you want to execute when the jar file is uninstalled. The "BEGIN INSTALL ... END INSTALL" section should (typically) include a CREATE FUNCTION command for each function that you're adding to the database. You may want to include GRANT EXECUTE...ON FUNCTION commands as well. The "BEGIN REMOVE ... END REMOVE " section should (typically) include a DROP FUNCTION command for each function. (Or, if you've installed the jar into a specific schema, you can simply DROP SCHEMA schemaName CASCADE instead.)

Once you've completed the deployment descriptor, add it to the jar file and add a reference to the descriptor to the jar's manifest. The manifest for a Java archive is stored in a member named META-INF/MANIFEST.MF and it contains meta-data about the other members of the archive. If you don't create a manifest file yourself, the jar command will create an empty one for you. When you store a deployment descriptor in a Java archive, you must build the manifest file by hand. A manifest (like a deployment descriptor) is a text file that you can create with any text editor. Listing 19.9 shows a typical manifest:

Listing 19.9. MANIFEST.MF

Manifest-Version: 1.0
Created-By: 0.92-gcc

Name: Movies.ddr
SQLJDeploymentDescriptor: TRUE

Each line in the manifest is a keyword : value pair. The first two pairs specify the manifest version (as specified by Sun Microsystems) and a note indicating who created the manifest. The last two pairs specify the name of an archive member and a note that tells PL/Java that the given file (in this case Movies.ddr) is an SQLJDeploymentDescriptor.

If you've saved the manifest in a file named manifest.txt, saved the deployment descriptor in a file named Movies.ddr, and saved the Java class file in Movies.class, you can create a new jar file with the following command:

$ jar -cv -f Movies.jar -m manifest.txt Movies.class Movies.ddr
adding: META-INF/ (in=0) (out=0) (stored 0%)
adding: META-INF/MANIFEST.MF (in=104) (out=99) (deflated 4%)
adding: Movies.class (in=1353) (out=783) (deflated 42%)
adding: Movies.ddr (in=283) (out=204) (deflated 27%)
Total:

(in = 1740) (out = 1536) (deflated 11%)

Notice that the jar command has converted the manifest.txt file into META-INF/MANIFEST.MF (as required by the Java specification). To view the contents of the new jar file (Movies.jar):

$ jar -tv -f Movies.jar
 0 Thu Apr 21 01:31:02 UTC 2005 META-INF/
 104 Thu Apr 21 12:52:28 UTC 2005 META-INF/MANIFEST.MF
 1353 Thu Apr 21 10:20:36 UTC 2005 Movies.class
 283 Thu Apr 21 12:16:46 UTC 2005 Movies.ddr

Now when you install Movies.jar, sqlj.install_jar() will execute the install-commands you specified in the deployment descriptor. Likewise, if you uninstall Movies.jar, sqlj.remove_jar() will execute the uninstall-commands found in the deployment descriptor.

If you're a Java programmer, PL/Java is an easy way to add server-side functionality to a PostgreSQL database. Because PL/Java closely follows industry standards such as JDBC and the SQLJ initiative, you can easily move PL/Java functions between database systems.

SQLThe Other Procedural Language

In this book, I've described the PL/pgSQL and PL/Java procedural languages and mentioned a few others (PL/Perl, PL/tcl, and even PL/bash). You can also write functions and stored procedures in SQL itself (that is, in LANGUAGE SQL). An SQL function is simply a named sequence of SQL commands. PostgreSQL executes each command in the function, in order, and returns any results produced by the last command. Unlike the other procedural languages, you can't "jump around" inside of an SQL function. SQL doesn't support control-of-flow commands like if, while, or switch. You can use the CASE, COALESCE, and NULLIF expressions to handle limited decision-making, but for anything more complex you'll need a more complete language. For more information about SQL functions, see the PostgreSQL reference documentation (specifically section 31.4, "Query Language (SQL) Functions" in PostgreSQL version 8.0).