intro

PL/Java 1.2.0 released Bringing the power of Java™ to PostgreSQL™ Functions and Triggers. Java™ is a registered trademark of Sun Microsystems, Inc. in the United States and other countries. PostgreSQL™ is a trademark of PostgreSQL Inc and Regents of the University of California. PL/Java is an add on module to the PostgreSQL backend. It falls into the same category as PL/SQL, PL/TCL, PL/Perl, PL/Python, and PL/R. When installed, functions and triggers can be written in Java using your favorite Java IDE and installed into the database.

The PL/Java 1.2.0 release of PL/Java provides the following features.

1. Ability to write both functions and triggers using Java 1.4 or higher.
2. Standardized utilities (modeled after the SQL 2003 proposal) to install and maintain Java code in the database.
3. Standardized mappings of parameters and result. Complex types as well as sets are supported.
4. An embedded, high performance, JDBC driver utilizing the internal PostgreSQL SPI routines.
5. Metadata support for the JDBC driver. Both DatabaseMetaData and ResultSetMetaData is included.
6. The ability to return a ResultSet that origins from a query as an alternative to build a ResultSet row by row
7. Full support for PostgreSQL 8.0 savepoints and exception handling.
8. Ability to use IN, INOUT, and OUT parameters when used with PostgreSQL 8.1
9. Two language handlers, one TRUSTED (the default) and one that is not TRUSTED (language tag is javaU to conform with the defacto standard)
10. Transaction and Savepoint listeners enabling code execution when a transaction or savepoint is commited or rolled back.
11. Integration with GNU GCJ on selected platforms.

PL/Java in brief

A function or trigger in SQL will appoint a static method in a Java class. In order for the function to execute, the appointed class must be installed in the database. PL/Java adds a set of functions that helps installing and maintaining the java classes. Classes are stored in normal Java archives (AKA jars). A Jar may optionally contain a deployment descriptor that in turn contains SQL commands to be executed when the jar is deployed/undeployed. The functions are modeled after the standards proposed for SQL 2003.

PL/Java implements a standardized way of passing parameters and return values. Complex types and sets are passed using the standard JDBC ResultSet class. Great care has been taken not to introduce any proprietary interfaces unless absolutely necessary so that Java code written using PL/Java becomes as database agnostic as possible.

A JDBC driver is included in PL/Java. This driver is written directly on top of the PostgreSQL internal SPI routines. This driver is essential since it’s very common for functions and triggers to reuse the database. When they do, they must use the same transactional boundaries that where used by the caller.

PL/Java is optimized for performance. The Java virtual machine executes within the same process as the backend itself. This vouches for a very low call overhead. PL/Java is designed with the objective to enable the power of Java to the database itself so that database intensive business logic can execute as close to the actual data as possible.

The standard Java Native Interface (JNI) is used when bridging calls from the backend into the Java VM and vice versa. Please read the rationale behind the choice of technology and a more in-depth discussion about some implementation details. PL/Java is primarily targeted to the new 8.1 version but will run with PostgreSQL 8.0 versions too albeit with some limitations.

For info on how to get started, please read the release notes. A user guide contains more information on how to create and manage our Java functions and triggers.

Source and selected binaries are available for download. See the download page for more details.