The pljava module allows stored procedures, triggers, and functions to be written in the Java language and executed in the Postgres Pro backend.

pljava provides the following main features:

Backend functions and triggers are written in Java using a directly-connected efficient version of the standard Java JDBC API that pljava transparently provides, with enhanced capabilities found in the pljava API.

A function or trigger in SQL resolves to a static method in a Java class. In order for the function to execute, the appointed class must be installed in the database. pljava adds a set of functions that help installing and maintaining Java classes.

The Java compiler also writes an SQLJ deployment descriptor containing the SQL statements that must be executed when installing and uninstalling the compiled Java code in the Postgres Pro backend.

The compiled Java code and the deployment descriptor file are stored together in a Java archive (JAR file). The sqlj.install_jar function both loads the code into Postgres Pro backend and executes the necessary SQL commands in the deployment descriptor, making new types, functions, and triggers available for use.

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

A JDBC driver is included in pljava. This driver is written directly on top of the internal Postgres Pro SPI routines. This driver is essential since it is 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.

pljava 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. pljava 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 virtual machine and vice versa.

pljava is provided with Postgres Pro Enterprise as a separate pre-built package pljava-ent-17 (for the detailed installation instructions, see Chapter 17).

The sqlj.install_jar, sqlj.replace_jar, and sqlj.remove_jar functions can act on a deployment descriptor allowing SQL commands to be executed after the JAR file was installed or prior to removal.

The descriptor is added as a normal text file to your JAR file. In the manifest of the JAR file, there must be an entry that appoints the file as the SQLJ deployment descriptor.

Name: deployment/examples.ddr
SQLJDeploymentDescriptor: TRUE

Such a file can be written by hand according to the format below but the usual method is to add specific Java annotations in the source code, as described in the Generating SQL Automatically section. The Java compiler then generates the deployment descriptor file at the same time it compiles the Java sources, and the compiled classes and .ddr file can all be placed in the JAR file together.

The format of the deployment descriptor is stipulated by ISO/IEC 9075-13:2003.

<descriptor_file> ::=
  SQLActions <left_bracket> <right_bracket> <equal_sign>
  { [ <double_quote> <action_group> <double_quote>
    [ <comma> <double_quote> <action_group> <double_quote> ] ] }

<action_group> ::=
    <install_actions>
  | <remove_actions>

<install_actions> ::=
  BEGIN INSTALL [ <command> <semicolon> ]... END INSTALL

<remove_actions> ::=
  BEGIN REMOVE [ <command> <semicolon> ]... END REMOVE

<command> ::=
    <SQL_statement>
  | <implementor_block>

<SQL_statement> ::= <SQL_token>...

<implementor_block> ::=
  BEGIN <implementor_name> <SQL_token>... END <implementor_name>

<implementor_name> ::= <identifier>

<SQL_token> ::= ! an SQL lexical unit specified by the term "<token>"
                in Sub clause 5.2, "<token> and <separator>", in ISO/IEC 9075-2.

If implementor blocks are used, pljava considers only those with the PostgreSQL implementor name (case insensitive) by default. Here is a sample deployment descriptor:

SQLActions[] = {
  "BEGIN INSTALL
    CREATE FUNCTION javatest.java_getTimestamp()
      RETURNS timestamp
      AS 'org.postgresql.pljava.example.Parameters.getTimestamp'
      LANGUAGE java;
  END INSTALL",
  "BEGIN REMOVE
    DROP FUNCTION javatest.java_getTimestamp();
  END REMOVE"
}

Although, by default, only the PostgreSQL implementor name is recognized, the implementor name(s) to be recognized can be set as a list in the pljava.implementors configuration parameter. It is consulted after every command while executing a deployment descriptor, which gives code in the descriptor a rudimentary form of conditional execution control, by changing which implementor blocks will be executed based on discovered conditions.

The method signature of a trigger is predefined. A trigger method must always return void and have the org.postgresql.pljava.TriggerData parameter. The TriggerData interface provides access to two java.sql.ResultSet instances: one representing the old row and one representing the new row. The old row is read-only, while the new row can be updated.

ResultSets are only available for triggers that are fired on each row. Delete triggers have no new row, and insert triggers have no old row. Only update triggers have both.

In addition to the sets, several boolean methods exist to gain more information about the trigger.

CREATE TABLE mdt (
    id int4,
    idesc text,
    moddate timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE FUNCTION moddatetime()
    RETURNS trigger
    AS 'org.postgresql.pljava.example.Triggers.moddatetime'
    LANGUAGE java;

CREATE TRIGGER mdt_moddatetime
    BEFORE UPDATE ON mdt
    FOR EACH ROW
    EXECUTE PROCEDURE moddatetime (moddate);

The corresponding Java code looks as follows:

/**
* Update a modification time when the row is updated
*/
static void moddatetime(TriggerData td)
throws SQLException
{
    if(td.isFiredForStatement())
    throw new TriggerException(td, "can't process STATEMENT events");

    if(td.isFiredAfter())
    throw new TriggerException(td, "must be fired before event");

    if(!td.isFiredByUpdate())
    throw new TriggerException(td, "can only process UPDATE events");

    ResultSet _new = td.getNew();
    String[] args = td.getArguments();
    if(args.length != 1)
    throw new TriggerException(td, "one argument was expected");

    _new.updateTimestamp(args[0], new Timestamp(System.currentTimeMillis()));
}

Using pljava, you can install a mapping between an arbitrary type and a Java class. There are the following prerequisites for doing this:

package org.postgresql.pljava.example;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class Point implements SQLData {
    private double m_x;
    private double m_y;
    private String m_typeName;

    public String getSQLTypeName() {
    return m_typeName;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException {
    m_x = stream.readDouble();
    m_y = stream.readDouble();
    m_typeName = typeName;
    }

    public void writeSQL(SQLOutput stream) throws SQLException {
    stream.writeDouble(m_x);
    stream.writeDouble(m_y);
    }

    /* Meaningful code that actually does something with this type was
    * intentionally left out
    */
}

SELECT sqlj.add_type_mapping('point', 'org.postgresql.pljava.example.Point');

CREATE TYPE javatest.complextuple AS (x float8, y float8);

SELECT sqlj.add_type_mapping('javatest.complextuple',
        'org.postgresql.pljava.example.ComplexTuple');

package org.postgresql.pljava.example;

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;

public class ComplexTuple implements SQLData {
    private double m_x;
    private double m_y;
    private String m_typeName;

    public String getSQLTypeName()
    {
    return m_typeName;
    }

    public void readSQL(SQLInput stream, String typeName) throws SQLException
    {
    m_typeName = typeName;
    m_x = stream.readDouble();
    m_y = stream.readDouble();
    }

    public void writeSQL(SQLOutput stream) throws SQLException
    {
    stream.writeDouble(m_x);
    stream.writeDouble(m_y);
    }

    /* Meaningful code that actually does something with this type was
    * intentionally left out
    */
}

@MappedUDT(schema="javatest", name="complextuple",
structure={"x float8", "y float8"})
public class ComplexTuple implements SQLData {
...

This text assumes that you have some familiarity with how scalar types are created and added to the Postgres Pro type system. For more information, refer to User-Defined Types.

Creating a new scalar type using Java functions is very similar to how they are created using C functions from an SQL perspective but different when looking at the actual implementation. Java stipulates that a mapping between a Java class and a corresponding SQL type should be done using the java.sql.SQLData, java.sql.SQLInput, and java.sql.SQLOutput interfaces, which are used by pljava. In addition, the Postgres Pro type system stipulates that each type must have a textual representation.

The example below shows how to create a type called javatest.complex. The name of the corresponding Java class will be org.postgresql.pljava.example.ComplexScalar.

The Java class for a scalar UDT must implement the java.sql.SQLData interface. In addition, it must also implement the parse() method that creates and returns an instance of the class and the toString() method that returns something that the parse() method can parse.

package org.postgresql.pljava.example;

import java.io.IOException;
import java.io.StreamTokenizer;
import java.io.StringReader;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.logging.Logger;

import org.postgresql.pljava.annotation.Function;
import org.postgresql.pljava.annotation.SQLType;
import org.postgresql.pljava.annotation.BaseUDT;

import static org.postgresql.pljava.annotation.Function.Effects.IMMUTABLE;
import static
        org.postgresql.pljava.annotation.Function.OnNullInput.RETURNS_NULL;

@BaseUDT(schema="javatest", name="complex",
        internalLength=16, alignment=BaseUDT.Alignment.DOUBLE)
public class ComplexScalar implements SQLData
{
    private double m_x;
    private double m_y;
    private String m_typeName;

    @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
    public static ComplexScalar parse(String input, String typeName)
    throws SQLException
    {
    try
    {
        StreamTokenizer tz = new StreamTokenizer(new StringReader(input));
        if(tz.nextToken() == '('
        && tz.nextToken() == StreamTokenizer.TT_NUMBER)
        {
            double x = tz.nval;
            if(tz.nextToken() == ','
            && tz.nextToken() == StreamTokenizer.TT_NUMBER)
            {
                double y = tz.nval;
                if(tz.nextToken() == ')')
                {
                return new ComplexScalar(x, y, typeName);
                }
            }
        }
        throw new SQLException("Unable to parse complex from string \""
        + input + '"');
    }
    catch(IOException e)
    {
        throw new SQLException(e.getMessage());
    }
    }

    public ComplexScalar()
    {
    }

    public ComplexScalar(double x, double y, String typeName)
    {
    m_x = x;
    m_y = y;
    m_typeName = typeName;
    }

    @Override
    public String getSQLTypeName()
    {
    return m_typeName;
    }

    @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException
    {
    m_x = stream.readDouble();
    m_y = stream.readDouble();
    m_typeName = typeName;
    }

    @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
    @Override
    public void writeSQL(SQLOutput stream) throws SQLException
    {
    stream.writeDouble(m_x);
    stream.writeDouble(m_y);
    }

    @Function(effects=IMMUTABLE, onNullInput=RETURNS_NULL)
    @Override
    public String toString()
    {
    s_logger.info(m_typeName + " toString");
    StringBuffer sb = new StringBuffer();
    sb.append('(');
    sb.append(m_x);
    sb.append(',');
    sb.append(m_y);
    sb.append(')');
    return sb.toString();
    }

    /* Meaningful code that actually does something with this type was
    * intentionally left out
    */
}

The class itself is annotated with @BaseUDT giving its SQL schema, name, as well as the length and alignment needed for its internal stored form.

Because the compiler knows that the class is a BaseUDT, it expects the parse(), toString(), readSQL(), and writeSQL() methods to be present and will generate correct SQL to declare them as functions to Postgres Pro. The @Function annotations are only there to declare immutability and on-null-input behavior for those methods, because those values are not the defaults when declaring a function.

pljava handles a complex return value as the IN or OUT parameter. If you declare a function that returns a complex type, you will need to use a Java method with the boolean return type and with the last parameter of the java.sql.ResultSet type added after all of visible method parameters. The output parameter will be initialized to an updatable ResultSet that contains exactly one row.

CREATE FUNCTION createComplexTest(int, int)
  RETURNS complexTest
  AS 'foo.fee.Fum.createComplexTest'
  IMMUTABLE LANGUAGE java;

The pljava method resolver will now find the following method in the foo.fee.Fum class:

public static boolean complexReturn(int base, int increment, ResultSet receiver)
throws SQLException
{
  receiver.updateInt(1, base);
  receiver.updateInt(2, base + increment);
  receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis()));
  return true;
}

The return value denotes if the receiver parameter should be considered as a valid tuple (true) or NULL (false).

Returning sets is tricky. You do not need to first build a set and then return it, since large sets require excessive resources. It is better to produce one row at a time. Incidentally, that is exactly what the Postgres Pro backend expects from a function that returns SETOF <type>. The <type> can be a scalar type, such as int, float, or varchar, can be a complex type, or the RECORD type.

CREATE FUNCTION javatest.getNames()
  RETURNS SETOF varchar
  AS 'foo.fee.Bar.getNames'
  IMMUTABLE LANGUAGE java;

package foo.fee;
import java.util.Iterator;

import org.postgresql.pljava.annotation.Function;
import static org.postgresql.pljava.annotation.Function.Effects.IMMUTABLE;

public class Bar
{
    @Function(schema="javatest", effects=IMMUTABLE)
    public static Iterator<String> getNames()
    {
        ArrayList<String> names = new ArrayList<>();
        names.add("Lisa");
        names.add("Bob");
        names.add("Bill");
        names.add("Sally");
        return names.iterator();
    }
}

CREATE FUNCTION javatest.listComplexTests(int, int)
  RETURNS SETOF complexTest
  AS 'foo.fee.Fum.listComplexTest'
  IMMUTABLE LANGUAGE java;

public class Fum implements ResultSetProvider
{
  private final int m_base;
  private final int m_increment;
  public Fum(int base, int increment)
  {
    m_base = base;
    m_increment = increment;
  }
  public boolean assignRowValues(ResultSet receiver, int currentRow)
  throws SQLException
  {
    // Stop when reaching 12 rows
    //
    if(currentRow >= 12)
      return false;
    receiver.updateInt(1, m_base);
    receiver.updateInt(2, m_base + m_increment * currentRow);
    receiver.updateTimestamp(3, new Timestamp(System.currentTimeMillis()));
    return true;
  }
  public void close()
  {
    // Nothing needed in this example
  }
  @Function(effects=IMMUTABLE, schema="javatest", type="complexTest")
  public static ResultSetProvider listComplexTests(int base, int increment)
  throws SQLException
  {
    return new Fum(base, increment);
  }
}

CREATE FUNCTION javatest.listSupers()
  RETURNS SETOF pg_user
  AS 'org.postgresql.pljava.example.Users.listSupers'
  LANGUAGE java;

CREATE FUNCTION javatest.listNonSupers()
  RETURNS SETOF pg_user
  AS 'org.postgresql.pljava.example.Users.listNonSupers'
  LANGUAGE java;

public class Users implements ResultSetHandle
{
  private final String m_filter;
  private Statement m_statement;

  public Users(String filter)
  {
    m_filter = filter;
  }

  public ResultSet getResultSet()
  throws SQLException
  {
    m_statement = DriverManager.getConnection("jdbc:default:connection")
      .createStatement();
    return m_statement.executeQuery("SELECT * FROM pg_user WHERE " + m_filter);
  }

  public void close()
  throws SQLException
  {
    m_statement.close();
  }

  @Function(schema="javatest", type="pg_user")
  public static ResultSetHandle listSupers()
  {
    return new Users("usesuper = true");
  }

  @Function(schema="javatest", type="pg_user")
  public static ResultSetHandle listNonSupers()
  {
    return new Users("usesuper = false");
  }
}

pljava contains a JDBC driver that maps to the Postgres Pro SPI functions. A connection that maps to the current transaction can be obtained using the following statement:

Connection conn = DriverManager.getConnection("jdbc:default:connection");

Now you can prepare and execute statements just like with any other JDBC connection. There are a couple of limitations:

You can catch and handle an exception in the Postgres Pro backend just like any other exception. The backend ErrorData structure is exposed as a property in the ServerException class derived from java.sql.SQLException, and the Java try/catch mechanism is synchronized with the backend mechanism.

pljava will always catch exceptions that you do not. They will cause a Postgres Pro error and the message is logged using the Postgres Pro logging utilities. The stack trace of the exception will also be printed if the log_min_messages configuration parameter is set to DEBUG1 or lower.

Postgres Pro savepoints are exposed using the standard setSavepoint() and releaseSavepoint() methods of the java.sql.Connection interface. The following restrictions apply:

“Function” here refers to the pljava function that is called from SQL. The restrictions do not prevent the Java code from being organized into several methods but the savepoint cannot survive after the eventual return from Java to the SQL caller.

pljava uses the standard java.util.logging.Logger class. Hence, you can write things like:

Logger.getAnonymousLogger().info(
    "Time is " + new Date(System.currentTimeMillis()));

At present, Logger is hardwired to a handler that maps the level in the log_min_messages configuration parameter to a valid Logger level and that outputs all messages using the ereport() backend function.

Importantly, Logger methods can quickly discard any message logged at a finer level than the one that was mapped from the Postgres Pro parameter at the time pljava was first used in the current session. Such messages never even get as far as ereport() even if the Postgres Pro parameter is changed later.

So, if expected messages from Java code are not showing up, be sure that the Postgres Pro parameters are fine enough at the time of the first use of pljava in the session, so that Java will not throw the messages away. Once pljava started, the parameters can be changed as desired and will control in the usual way what ereport() does with the messages pljava delivers to it.

The cutoff level in Java is set based on the finer of log_min_messages and client_min_messages.

The following mapping applies between the Logger levels and the Postgres Pro backend levels:

Several configuration parameters can affect pljava operation, including some common Postgres Pro parameters, as well as own parameters of pljava.