The obligatory Hello example

Every employee of example.com is required to write a function that says Hello, world! . There is no escape from this requirement. This page is dedicated to all those example.com employees faced with that terrible task.

The goal

Clearly, a function that only returns Hello, world! is useless for illustrating how to pass parameters. So, the goal will be a function that works like this:

# \df
                           List of functions
 Schema | Name  | Result data type  |   Argument data types    |  Type  
--------+-------+-------------------+--------------------------+--------
 public | hello | character varying | towhom character varying | normal
(1 row)
# select hello('world');
     hello     
---------------
 Hello, world!
(1 row)
A start: the Java program

Employees of example.com never forget to put their Java code in a package
that begins with com.example, so very quickly this program takes shape:
package com.example.proj;
public class Hello {
  public static String hello(String toWhom) {
    return "Hello, " + toWhom + "!";
How to build it: a Maven project

Such a small program might not need a build system like Maven, but like any
project, the requirements could grow over time, so it might as well be set up
right from the beginning.
In preparation, PL/Java must have been built, using the command
mvn clean install. Recall from the installation page that
the install Maven goal has nothing to do with installing PL/Java into
PostgreSQL, but does register PL/Java in your Maven
repository. With that done, any project of your own needs only declare
pljava-api as a dependency, and Maven can compile and package it for you.

The POM file

The project begins as an empty directory (named proj in this example),
and the first thing to go in that directory is a pom.xml file. The
Project Object Model is what tells Maven all it needs to know about
this project. It is XML and verbose, and more lines than the Hello program
itself! But it is mostly unchanging boilerplate and, as you can see, only
has a few places to change for information specific to the project.
An important Maven feature is POM inheritance. In an organization with many
similar projects, there might be one pom.xml like this one, and many
individual projects with shorter pom.xml files naming this as the parent.
<project
 xmlns="http://maven.apache.org/POM/4.0.0"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation=
 "http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"
  <modelVersion>4.0.0</modelVersion>
  <!-- Above is all boilerplate. Next: your project's "Maven coordinates" -->
  <groupId>com.example</groupId>
  <artifactId>proj</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <!-- Coordinates are nice, but so are names and descriptions for humans. -->
  <name>Hello in PL/Java</name>
  <description>Project that provides a Hello function</description>
    Many Maven plugins care what character set encoding your files are in.
    For this example I've chosen the most restrictive (US-ASCII). Change if
    your files use a different encoding, but be sure not to lie. You should
    be sure the encoding named here IS the way your source files are coded.
  <properties>
    <project.build.sourceEncoding>US-ASCII</project.build.sourceEncoding>
  </properties>
  <!-- Here's where you say your project depends on a pljava-api version. -->
  <dependencies>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>pljava-api</artifactId>
      <version>1.6.8</version>
    </dependency>
  </dependencies>
  <!-- The rest here is pretty much boilerplate. -->
  <build>
    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <version>3.10.1</version>
        <configuration>
          <release>9</release>
        </configuration>
      </plugin>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-jar-plugin</artifactId>
        <version>3.3.0</version>
        <configuration>
          <archive>
            <manifest>
              <!-- This identifies and version-stamps the jar.
                 Not essential, but easy and useful. -->
              <addDefaultImplementationEntries>
              </addDefaultImplementationEntries>
            </manifest>
            <manifestSections>
              <!-- This identifies a file in the jar named
                 pljava.ddr as an SQLJDeploymentDescriptor. -->
              <manifestSection>
                <name>pljava.ddr</name>
                <manifestEntries>
                  <SQLJDeploymentDescriptor>
                  </SQLJDeploymentDescriptor>
                </manifestEntries>
              </manifestSection>
            </manifestSections>
          </archive>
        </configuration>
      </plugin>
    </plugins>
  </build>
</project>
So far, so good. There is a new proj directory with only this pom.xml file
in it:
   | pom.xml
What happens if mvn clean package is run in this directory, even before
any Java code has been written?
[WARNING] JAR will be empty - no content was marked for inclusion!
[INFO] Building jar: /var/tmp/proj/target/proj-0.0.1-SNAPSHOT.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
Now there is a target subdirectory with something in it:
   | pom.xml
   | target
          | proj-0.0.1-SNAPSHOT.jar
Although Maven warned the jar would be empty, in fact it is not completely
empty:
$ jar tf target/proj-0.0.1-SNAPSHOT.jar 
META-INF/
META-INF/MANIFEST.MF
META-INF/maven/
META-INF/maven/com.example/
META-INF/maven/com.example/proj/
META-INF/maven/com.example/proj/pom.xml
META-INF/maven/com.example/proj/pom.properties
It has a manifest file, and a couple of files Maven adds to save information
about the build. What is in the manifest?
$ unzip -c target/proj-0.0.1-SNAPSHOT.jar META-INF/MANIFEST.MF
Manifest-Version: 1.0
Implementation-Title: Hello in PL/Java
Implementation-Version: 0.0.1-SNAPSHOT
Implementation-Vendor-Id: com.example
Name: pljava.ddr
SQLJDeploymentDescriptor: true
Clearly, Maven did what the POM told it to do. It created a manifest with
naming and version information for the project, and declaring that the file
pljava.ddr (if there were such a file in the jar) is special because it is
an SQLJ deployment descriptor.
This proves that Maven can successfully build an empty project with no code!

Adding the Java code

Maven has a convention over configuration philosophy: it has strict
expectations of how the project directories will be laid out, and if those
expectations are followed, it knows what to do, without need to add more
information in the POM. Sources go in src, and they are split between main
and test for sources that are only built for tests. Sources written in Java
go in a java subdirectory. So, the Java class that was shown earlier could be
saved as Hello.java, here:
   | pom.xml
   | src
   |   | main
   |        | java
   |             | Hello.java
   | target
          | proj-0.0.1-SNAPSHOT.jar
After running mvn clean package again, what is now in the jar?
$ jar tf target/proj-0.0.1-SNAPSHOT.jar 
META-INF/
META-INF/MANIFEST.MF
com/example/
com/example/proj/
com/example/proj/Hello.class
META-INF/maven/
META-INF/maven/com.example/
META-INF/maven/com.example/proj/
META-INF/maven/com.example/proj/pom.xml
META-INF/maven/com.example/proj/pom.properties
This is good progress. The class file is placed in the jar at its correct,
package-based path, even though the Java file was saved directly in
src/main/java. That is convenient for such a small project. A larger project
with many classes would probably organize the source files into package-based
subdirectories of src/main/java also.
But something is still missing from this jar. It does not contain any
pljava.ddr file to tell PL/Java what to do when loading it.

Annotating the Java code

That can be fixed by adding two lines to the Java code:
package com.example.proj;
import org.postgresql.pljava.annotation.Function;
public class Hello {
  @Function
  public static String hello(String toWhom) {
    return "Hello, " + toWhom + "!";
The @Function annotation declares that the hello function should
be available from SQL, so a pljava.ddr file will be added to the jar,
containing the SQL commands to make that happen.
One more try with mvn clean package and there it is:
$ jar tf target/proj-0.0.1-SNAPSHOT.jar 
META-INF/
META-INF/MANIFEST.MF
com/example/
com/example/proj/
com/example/proj/Hello.class
pljava.ddr
META-INF/maven/
META-INF/maven/com.example/
META-INF/maven/com.example/proj/
META-INF/maven/com.example/proj/pom.xml
META-INF/maven/com.example/proj/pom.properties
Curious about what is in the pljava.ddr file?
$ unzip -c target/proj-0.0.1-SNAPSHOT.jar pljava.ddr
SQLActions[]={
"BEGIN INSTALL
BEGIN PostgreSQL
CREATE OR REPLACE FUNCTION hello(
	toWhom pg_catalog.varchar)
	RETURNS pg_catalog.varchar
	LANGUAGE java VOLATILE
	AS 'java.lang.String=com.example.proj.Hello.hello(java.lang.String)'
END PostgreSQL;
END INSTALL",
"BEGIN REMOVE
BEGIN PostgreSQL
DROP FUNCTION hello(
	toWhom pg_catalog.varchar)
END PostgreSQL;
END REMOVE"
There you have it. A jar file containing the new class, and the instructions
PL/Java needs when installing or removing it.


    
     
      
       
        
         
          
           
            
             
              
               Using the jar in PostgreSQL
              
             
             
              
               The time has come to load this jar file into PostgreSQL and try it out!
Within PL/Java, each jar has a short name; this one can be
               
                myjar
               
               . The
final
               
                true
               
               parameter to
               
                install_jar
               
               means that the deployment descriptor
commands should be used.
              
             
             # select sqlj.install_jar(
  'file:/home/me/proj/target/proj-0.0.1-SNAPSHOT.jar', 'myjar', true);
 install_jar 
-------------
(1 row)
The result returned by install_jar isn't very interesting, but it does not
show an error, so is the function ready to use?
# \df
                           List of functions
 Schema | Name  | Result data type  |   Argument data types    |  Type  
--------+-------+-------------------+--------------------------+--------
 public | hello | character varying | towhom character varying | normal
(1 row)
# select hello('world');
ERROR:  java.lang.ClassNotFoundException: com.example.proj.Hello
Not so fast! In PL/Java, there is a classpath for every schema. (This is
not quite what the SQL-JRT standard intended, but it is manageable for
some related functions grouped into a schema.) The hello function was put
into the public schema. Why could the class not be found?
# select sqlj.get_classpath('public');
 get_classpath 
---------------
(1 row)
An empty classpath would have that effect. The short name myjar should be
added.
# select sqlj.set_classpath('public', 'myjar');
 set_classpath 
---------------
(1 row)
# select hello('world');
     hello     
---------------
 Hello, world!
(1 row)
Success!
One or two refinements

The function says hello, but it also does this:
# select hello(null);
    hello     
--------------
 Hello, null!
(1 row)
The function has not been written to notice when the parameter is null.
In this case, Java substitutes the word null and nothing worse happens,
but perhaps the function should do something different. If the function
should return null whenever a parameter is null, there is no need to
even add any code to the function. It can be annotated to declare that
behavior, and then PostgreSQL will consider it to return null without
even calling the function any time a parameter is null.
  @Function(onNullInput=Function.OnNullInput.RETURNS_NULL)
  public static String hello(String toWhom) {
Another optimization suggests itself because the hello function has
no side effects, and its result depends on nothing except the parameter passed
to it. By default, functions are assumed to possibly have side effects, depend
on database contents or outside influences, and otherwise be harder to reason
about. The PostgreSQL optimizer will be helped if this function is declared
IMMUTABLE. That leads to a program like this:
package com.example.proj;
import org.postgresql.pljava.annotation.Function;
import static org.postgresql.pljava.annotation.Function.Effects.IMMUTABLE;
import static
  org.postgresql.pljava.annotation.Function.OnNullInput.RETURNS_NULL;
public class Hello {
  @Function(onNullInput=RETURNS_NULL, effects=IMMUTABLE)
  public static String hello(String toWhom) {
    return "Hello, " + toWhom + "!";
Further reading

From here, consider:
The documentation for @Function and the rest of the
PL/Java API
The user guide pages on the wiki
The many pre-built examples