The examples built with PL/Java

The PL/Java source includes a number of examples that are built into a jar that can be installed in the same way as a jar of your own PL/Java functions and types.

You can extract examples.jar from the packaged archive, or use the direct path to where it was built: relative to the build root, pljava-examples/target/pljava-examples-${project.version}.jar, with ${project.version} expanded the obvious way, for example 1.6.8.

Loading the examples

Assuming you are connected to a database where PL/Java has been successfully loaded, use the sqlj.install_jar function, which takes three parameters. The first is a URL to the jar file to be loaded. The simplest case would be a file: URL giving a path on the PostgreSQL server host to the jar file, with permissions set so the backend can read it. Other forms of URL are also accepted, so install_jar could retrieve a jar from the web, for example.

The second parameter is a short name to identify the jar within PL/Java, and the third is a boolean controlling whether to execute the deployment descriptor included in the jar as part of installing it.

Therefore:

SELECT sqlj.install_jar(
  'file:'
  '/buildroot/pljava-examples/target/pljava-examples-1.6.8.jar',
  'examples', true);

will install the examples.jar file (assuming the build was done in /buildroot), giving it the identifier examples and executing the deployment descriptor.

A typical deployment descriptor will merely declare functions or types provided by the jar, create tables they rely on, etc. The examples.jar deployment descriptor goes farther, and calls several functions provided as test cases, so this use of install_jar may take a few extra seconds and produce some test-related output. (To see successful test-related output, be sure to set client_min_messages to a level at least as detailed as INFO before the session's first use of PL/Java.)

See the end of this page if your attempt to install the examples jar fails with “unable to find static method” or “no such class” messages.

Trying the examples

While the deployment descriptor itself runs some of the examples, once the jar is installed, they are all available for you to run, as functions in the javatest schema.

First, be sure the javatest schema is on your search_path:

SHOW search_path;

It will be, after install_jar returns (though that is arguably a bug and could change), but that will not persist into new sessions.

Make sure that the Java classpath for that schema includes the ID for this jar:

SELECT sqlj.get_classpath('javatest');

If it does not, set the classpath:

SELECT sqlj.set_classpath('javatest', 'examples');

Then use some example function:

 SELECT javatest.java_addone(3);
 java_addone 
-------------
           4

Optionally-built example code

Some example code is included in source form but not built by default, because it assumes a later Java version than PL/Java should require to build, or pulls in extra libraries, or both. Optional examples can be built by adding corresponding -P profile options to the mvn command line when building.

Using the Saxon XML-processing library for true XMLQUERY and XMLTABLE

In the source directory org/postgresql/pljava/example/saxon is example code for XML processing functions similar to XMLCAST, XMLEXISTS, XMLQUERY, and XMLTABLE but using the XQuery language as the SQL/XML standard actually specifies (in contrast to the similar functions built into PostgreSQL, which support only XPath, and XPath 1.0, at that).

This code is not built by default because it pulls in the sizeable Saxon-HE library from Saxonica, and because it requires Java 8.

To include these optional functions when building the examples, be sure to use a Java 8 or later build environment, and add -Psaxon-examples to the mvn command line.

The Saxon example documentation is here.

Exception resolving class or method (message when installing examples)

As described above, there are some optionally-built examples included in the source. For example, there are XML Query examples that are not built by default because they depend on the Saxon jar.

If your examples jar was built with the optional examples enabled, then PL/Java will normally validate that all of the functions it creates can be used. That validation can fail if a needed dependency, such as the Saxon jar, is not already installed and on the classpath. The error message will not directly say the Saxon jar is missing, but may say it failed to find a class (whose name will suggest it should be part of Saxon).

There are two ways to proceed:

  • Install the required dependency first. Use sqlj.install_jar to install the Saxon jar (as described here), and sqlj.set_classpath to make it accessible, and then use sqlj.install_jar to install the examples jar itself. The dependency will be satisfied and all of the example functions will work.

  • Use SET check_function_bodies TO off before installing the examples jar. That will simply relax the strict checking at CREATE FUNCTION time, so that all of the example functions will be created. The ones that require Saxon, of course, won't work; SET check_function_bodies TO off simply means you get the errors later, when trying to use the functions, instead of when creating them. If you install the dependency jar later and add it to the class path, those functions will then work.