OpenIndiana and PostgreSQL

Table of Contents

Classpath
Simple Types
Complex Types
Final Notes
References
Downloads

CLASSPATH

To compile the example using the org.postgresql.pljava.ResultSetProvider interface you will need pljava.jar on your classpath.

Assuming you have a typical PL/Java installation, you will find it in the server's $libdir. This can be queried with the pg_config --libdir command. On Debian you may have to use pg_config --pkglibdir.

Setting it with zsh.

$ typest -xT CLASSPATH classpath
$ classpath=( `pg_config --libdir`/pljava.jar $classpath )

With ksh and bash.

$ export CLASSPATH=`pg_config --libdir`/pljava.jar:$CLASSPATH

And with tcsh.

$ setenv CLASSPATH `pg_config --libdir`/pljava.jar:$CLASSPATH

The Simple Types

Returning sets of the simple types, Integer, Double, String, etc. is done with the java.util.Iterator interface. Note that iterators work with Objecs so primitive types like int (int4 in Postgres), long (int8), double (float8) and so on will need to be returned as Integer, Long, Double, ... from the iterator. PL/Java handles this automatically.

Side Note. Functions accepting such parameters, say int4, can be declared AS 'com.example.package.Class.function(java.lang.Integer)' in the CREATE FUNCTION statement.

A very simple function that returns the set of all system property keys follows.

    public static Iterator< String > systemPropertyKeys()
    {
	Properties systemProperties = System.getProperties();

	Set< String > keySet = systemProperties.stringPropertyNames();

	return keySet.iterator();
    }

Here we use the iterator directly from the Set of keys.

And the SQL to create it is

 CREATE OR REPLACE FUNCTION pljava.system_property_keys()
  RETURNS SETOF text
  LANGUAGE java
 AS 'com2ndQuadrant.pljava.SRF.systemPropertyKeys';

And then

SELECT keys FROM system_property_keys() AS keys;

returns:

             keys
-------------------------------
 java.runtime.name
 sun.boot.library.path
 java.vm.version
 java.vm.vendor

...

 java.vendor
 file.separator
 java.vendor.url.bug
 sun.cpu.endian
 sun.io.unicode.encoding
 sun.desktop
 sun.cpu.isalist
(49 rows)

We can also manipulate the sets by implementing our own Iterators. For example, to return the set of system properties as the string key + " = " + value we implement an iterator that manipulates the strings before they are returned to PL/Java.

The following inner class is an example of a manipulating iterator. It is not an example of the most elegant way to list all the properties with their values.

    private static class SystemPropertyIterator
	implements Iterator< String >
    {
	private static Properties systemProperties;
	private static Iterator< String > propertyKeys;

	public SystemPropertyIterator( Properties systemProperties )
	    {
		this.systemProperties = systemProperties;
		this.propertyKeys 
		    = systemProperties.stringPropertyNames().iterator();
	    }

	public boolean hasNext()
	{
	    return propertyKeys.hasNext();
	}

	public String next()
	{
	    String key = propertyKeys.next();

	    String value = systemProperties.getProperty( key );

	    return key + " = " + value;
	}

	public void remove()
	    throws UnsupportedOperationException
	{
	    throw new UnsupportedOperationException();
	}
    }

Here we are using the assumption that PL/Java will always call hasNext() before next(). Code that may run elsewhere should probably check for NoSuchElementException from propertyKeys.next().

The systemProperties() function makes use of this iterator to return all the properties with their values.

    public static Iterator< String > systemProperties()
    {
	Properties systemProperties = System.getProperties();
	
	return new SystemPropertyIterator( systemProperties );
    }

It is then created with

 CREATE OR REPLACE FUNCTION system_properties()
  RETURNS SETOF text
  LANGUAGE java
 AS 'com2ndQuadrant.pljava.SRF.systemProperties';

and run with

SELECT properties FROM system_properties() AS properties;

which returns

			     properties
--------------------------------------------------------------------
  java.runtime.name = Java(TM) SE Runtime Environment
  sun.boot.library.path = /usr/jdk/instances/jdk1.6.0/jre/lib/amd64
  java.vm.version = 20.1-b02
  java.vm.vendor = Sun Microsystems Inc.
  java.vendor.url = http://java.sun.com/
  path.separator = :
  java.vm.name = Java HotSpot(TM) 64-Bit Server VM
  file.encoding.pkg = sun.io
  sun.os.patch.level = unknown
  java.vm.specification.name = Java Virtual Machine Specification
  user.dir = /home/johann/db/v9.2beta1
  java.runtime.version = 1.6.0_26-b03
  java.awt.graphicsenv = sun.awt.X11GraphicsEnvironment
  java.endorsed.dirs = /usr/jdk/instances/jdk1.6.0/jre/lib/endorsed
  os.arch = amd64

...

  java.vendor = Sun Microsystems Inc.
  file.separator = /
  java.vendor.url.bug = http://java.sun.com/cgi-bin/bugreport.cgi
  sun.cpu.endian = little
  sun.io.unicode.encoding = UnicodeBig
  sun.desktop = gnome
  sun.cpu.isalist = 
(49 rows)

Complex Types

Sets of complex types are returned by implementing the org.postgresql.pljava.ResultSetProvider interface.

Side note. This tutorial does not include an example using the org.postgresql.pljava.ResultSetHandle interface. The referenced wiki page contains one.

Given this type

CREATE TYPE property AS ( key text, value text );

we can use

    private static class SystemPropertyProvider
	implements ResultSetProvider
    {

	private Iterator< String > systemPropertyIterator;
	private Properties systemProperties;

	public SystemPropertyProvider( Properties systemProperties )
	{
	    this.systemProperties = systemProperties;
	    this.systemPropertyIterator 
		= systemProperties.stringPropertyNames().iterator();
	}

	public void close()
	{
	    return;
	}

	public boolean assignRowValues( ResultSet receiver, int currentRow )
	    throws SQLException
	{
	    boolean hasNext = systemPropertyIterator.hasNext();

	    if ( hasNext )
		{
		    String key = systemPropertyIterator.next();

		    String value = systemProperties.getProperty( key );

		    receiver.updateString( 1, key );
		    receiver.updateString( 2, value );
		    return true;
		}
	    else
		{
		    return false;
		}
	}
    }

to return the set of properties as rows of keys and values. Here we use the iterator created previously to retrieve the keys and construct the return value row by row. Again, this is not an example of how to return the system properties in the most elegant ways but on how to use and implement the ResultSetProvider interface.

The close() function is called after the last row has been returned and can be used to clean up resources. This is signaled by returning false from assignRowValues().

Using this is then just a simple matter of returning an instance of that class.

    public static ResultSetProvider systemPropertyRecords()
    {
	return new SystemPropertyProvider( System.getProperties() );
    }

Then in Postgres we do

 CREATE OR REPLACE FUNCTION system_property_records()
  RETURNS SETOF property
  LANGUAGE java
 AS 'com2ndQuadrant.pljava.SRF.systemPropertyRecords';

and

SELECT key, value FROM system_property_records();

which returs

              key              |             value
-------------------------------+------------------------------------------
 java.runtime.name             | Java(TM) SE Runtime Environment
 sun.boot.library.path         | /usr/jdk/instances/jdk1.6.0/jre/lib/amd64
 java.vm.version               | 20.1-b02
 java.vm.vendor                | Sun Microsystems Inc.
 java.vendor.url               | http://java.sun.com/
 path.separator                | :
 java.vm.name                  | Java HotSpot(TM) 64-Bit Server VM
 file.encoding.pkg             | sun.io
 sun.os.patch.level            | unknown
 java.vm.specification.name    | Java Virtual Machine Specification
 user.dir                      | /home/johann/db/v9.2beta1
 java.runtime.version          | 1.6.0_26-b03
 java.awt.graphicsenv          | sun.awt.X11GraphicsEnvironment

...

 java.vendor.url.bug           | http://java.sun.com/cgi-bin/bugreport.cgi
 sun.cpu.endian                | little
 sun.io.unicode.encoding       | UnicodeBig
 sun.desktop                   | gnome
 sun.cpu.isalist               |
(49 rows)

Final Notes

This article expands on the material available in the PL/Java manual on the wiki. The code examples are meant to give ideas on how to use PL/Java to the experienced Java programmer, not as a tutorial for those new to the Java language or JDBC programming. As such, questions are likely to come up and for those we refer to the PL/Java mailing list, see below.

References

• PL/Java Project at PGFoundry
• PL/Java Wiki
• PL/Java Mailing Lists
• PostgreSQL Website
• PostgreSQL Documentation

Downloads

• 2ndquadrant-pljava-srf.tar.bz2 PostgreSQL licensced sourced code.