The PostgreSQL RDBMS offers a great variety for writing server-side functions and with PostgreSQL 11 now also stored procedures. PostgreSQL comes with a language named PL/pqSQL which enhances plain SQL with control flow predicates (similar to Oracle's PL/SQL). Besides this SQL-like language, PostgreSQL also offers built-in support for using Perl 5, Python and TCL as possible languages to implement functions and stored procedures. As a Java developer, I was always curious if there is a way to use Java within PostgreSQL. While searching for a solution, I found PL/Java which is an open-source extension for PostgreSQL that allows stored procedures, triggers and functions to be written in Java.
In this blog, I'll show you how to install the third-party extension and how to write and deploy Java code as a PostgreSQL function. I'll be using PL/Java with version 1.5.2, OpenJDK 8, Debian 9, PostgreSQL 9.6 and Maven 3.
Installing PL/Java via apt-get (for Debian &Ubtunu)
There are pre-built packages for Debian and Ubuntu once you enable the PostgreSQL Debian repository (follow the Quickstart section in the provided link). If you are running on Debian or Ubuntu and have the PostgreSQL repository enabled, you can simply install the PL/Java extension alongside the PostgreSQL server and skip the following example to build the extension manually:
| 1 | apt-get install postgresql-server-9.6 postgresql-9.6-pljava |
As the PL/Java extension is not available as a prebuilt package for every operating system, I'll demonstrate how to build it for your platform. If you are running on Debian or Ubuntu and installed the package via apt-get, you can jump to the Enabling the pljava extension.
Building PL/Java from source
The PL/Java source code is available on GitHub as well as the official documentation site. To build the extension, there are some prerequisites you have to meet on your database server. First of all, you need a JDK installed and Maven as the build tool. In addition, you need the C compiling and linking tools for your platform (in most cases gcc and g++). Furthermore, you have to provide some development packages for your PostgreSQL server version and for openssl. In the following you'll see all additional packages required for the Debian 9 slim docker image:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | RUN apt-get update && \ apt-get install -y \ postgresql-server-dev-9.6 \ gcc \ g++ \ openssl \ libssl-dev \ maven \ git \ libecpg-dev \ libkrb5-dev \ default-jdk && \ apt-get clean && \ rm -rf /var/lib/apt/lists/ |
After installing all the required software packages, you can clone the Git repository, check out the PL/Java version of your choice and build the Maven project:
| 1 2 3 4 | $ git clone https://github.com/tada/pljava.git $ cd pljava $ git checkout refs/tags/V1_5_2 $ mvn clean install -B |
This step may take some time and will fail if you are missing any additional package. After Maven is finished, you'll find the required .jar file for your platform in the pljava-packaging/target folder. For my setup, this file is called: pljava -packaging/target/pljava-pg9.6-amd64-Linux-gpp.jar. You have to run this .jar file once with sufficient privilege to write in the PostgreSQL installation directories:
| 1 | $ java -jar pljava-packaging/target/pljava-pg9.6-amd64-Linux-gpp.jar |
With these steps, you are now ready to create the pljava extension within PostgreSQL.
Enabling the pljava extension
Either connect to your database with PgAdmin or the psql CLI tool and run the following SQL with the postgres superuser:
| 1 2 3 4 | SET pljava.libjvm_location TO '/usr/lib/jvm/java-1.8.0-openjdk-amd64/jre/lib/amd64/server/libjvm.so'; CREATE EXTENSION pljava; GRANT USAGE ON LANGUAGE java TO your_user; ALTER DATABASE your_database SET pljava.libjvm_location FROM CURRENT; |
The first line is essential as this will tell PostgreSQL where to find the JVM on your machine. The path might differ for your operating system or JDK version/vendor. If you use the pljava extension from the Debian repository, this parameter already points to your default-java installation.
Furthermore, the GRANT USAGE statement is required for your database application user to have access to pljava.
Creating a custom Java based function
Now you are ready to write your functions with Java. Therefore create a simple Maven project with the following pom.xml:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 | <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> <groupId>de.rieckpil.blog</groupId> <artifactId>simple-java-function</artifactId> <version>0.0.1-SNAPSHOT</version> <name>simple-java-function</name> <description>Simple Java Function for PL/Java</description> <properties> <maven.compiler.source>1.8</maven.compiler.source> <maven.compiler.target>1.8</maven.compiler.target> <failOnMissingWebXml>false</failOnMissingWebXml> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> </properties> <dependencies> <!-- HINT: This package is not available at Maven Central! You'll get it while building PL/Java installed in your local .m2 folder. If you just need this .jar on your development machine and don't want to fully build PL/Java, just navigate in the pljava-api folder of the cloned repository and run mvn install --> <dependency> <groupId>org.postgresql</groupId> <artifactId>pljava-api</artifactId> <version>1.5.2</version> </dependency> </dependencies> <build> <finalName>simple-java-function</finalName> <plugins> <plugin> <groupId>org.apache.maven.plugins</groupId> <artifactId>maven-jar-plugin</artifactId> <version>2.6</version> <configuration> <archive> <manifest> <addDefaultImplementationEntries> true </addDefaultImplementationEntries> </manifest> <manifestSections> <manifestSection> <name>pljava.ddr</name> <manifestEntries> <SQLJDeploymentDescriptor> true </SQLJDeploymentDescriptor> </manifestEntries> </manifestSection> </manifestSections> </archive> </configuration> </plugin> </plugins> </build> </project> |
The Maven project just needs the pljava-apidependency which is part of building PL/Java in the previous step. Make sure you read the comment in the pom.xml above if you develop on another machine then the machine running your PostgreSQL server. Furthermore, I configured the maven-jar-plugin for a custom manifest configuration, which will be responsible for creating a pljava.ddr file during the Maven build step. I'll provide the use of this file later on.
With this setup, you can now create public Java classes which contain your PostgreSQL functions as static Java methods:
| 1 2 3 4 5 6 7 8 9 | import org.postgresql.pljava.annotation.Function; public class Functions { @Function public static String greet(String personName) { return "Hello World, " + personName + " !"; } } |
With the @Function annotation of the pljava-api dependency, we can configure the PostgreSQL function's metadata and mark the Java function to be deployed as a PostgreSQL function later on. For writing functions, you can use the standard Java data types as method arguments and return values (for more detailed information about passing data types between PostgreSQL and Java, read the official documentation for this topic).
For returning a list of values, you can use the Iterator<E> interface:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | @Function(name = "split_string_by_delimiter") public static Iterator<String> splitStringByDelimiter(String tagString, String delimiter) { if (delimiter == null || delimiter.isEmpty()) { delimiter = ">"; } List<String> tags = new ArrayList<String>(); for (String currentTag : tagString.split(delimiter)) { tags.add(currentTag.trim()); } return tags.iterator(); } |
Writing only functions like the two above would be boring if you can't access data within your database. Therefore you can create a JDBC connection within your function and connect to a default JDBC URL: jdbc:default:connection. This is not a conventional JDBC connection, but rather direct access into the current PostgreSQL backend.
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | @Function(name = "get_oldest_person") public static String getOldestPerson() throws SQLException { try(Statement statement = DriverManager // .getConnection("jdbc:default:connection") // .createStatement(); // ResultSet resultSet = statement.executeQuery("SELECT * FROM persons")) { List<Person> personList = new ArrayList<>(); while (resultSet.next()) { Person person = new Person(); person.setId(resultSet.getLong("id")); person.setFirstName(resultSet.getString("first_name")); person.setLastName(resultSet.getString("last_name")); person.setDayOfBirth(resultSet.getDate("day_of_birth").toLocalDate()); personList.add(person); } Collections.sort(personList); Person oldestPerson = personList.get(0); return String.format("The oldest person is %s, %s with %s years!", oldestPerson.getFirstName(), oldestPerson.getLastName(), Period.between(oldestPerson.getDayOfBirth(), LocalDate.now()).getYears()); } } |
When you finished writing your Java methods, you can now package your Maven project with:
| 1 | $ mvn clean package |
The result will contain the following files:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ jar tf simple-java-function.jar META-INF/ META-INF/MANIFEST.MF de/ de/rieckpil/ de/rieckpil/blog/ pljava.ddr de/rieckpil/blog/Functions.class de/rieckpil/blog/Person.class META-INF/maven/ META-INF/maven/de.rieckpil.blog/ META-INF/maven/de.rieckpil.blog/simple-java-function/ META-INF/maven/de.rieckpil.blog/simple-java-function/pom.xml META-INF/maven/de.rieckpil.blog/simple-java-function/pom.properties |
Applying the PL/Java function to the PostgreSQL database
The already mentioned pljava.ddr file will now contain SQL instructions to automatically create your PostgreSQL functions when your .jar is deployed to your database server:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $ cat pljava.ddr SQLActions[]={ "BEGIN INSTALL BEGIN PostgreSQL CREATE OR REPLACE FUNCTION greet( personName pg_catalog.varchar) RETURNS pg_catalog.varchar LANGUAGE java VOLATILE AS 'java.lang.String=de.rieckpil.blog.Functions.greet(java.lang.String)' END PostgreSQL; // ... END INSTALL", // ... } |
The pljava extension comes with few utility functions that are installed into the sqlj schema: install_jar(), remove_jar(), replace_jar(). Our Java functions can now be deployed with the install_jar() function:
| 1 | SELECT sqlj.install_jar( 'file:/tmp/simple-java-function/target/simple-java-function.jar','jfunctions', true ); |
The function takes the path of the .jar file, a mnemonic name to reference the .jar within the database and a boolean indicating whether or not the functions should be automatically deployed (based on the pljava.ddr file). The path can be either absolute or relative to the server's current datadir directory.
You can now list all available functions within your database using psql:
| 1 2 3 4 5 6 7 | postgres=# \df Liste der Funktionen Schema | Name | Ergebnisdatentyp | Argumentdatentypen --------+---------------------------+-------------------------+--------------------------------------------------------- public | get_oldest_person | character varying | public | greet | character varying | personname character varying public | split_string_by_delimiter | SETOF character varying | tagstring character varying, delimiter character varying |
and use the functions like any other PostgreSQL function:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | postgres=# SELECT greet('duke'); greet --------------------- Hello World, duke ! postgres=# SELECT split_string_by_delimiter('duke - 2018 - sun - mike - 1', '-' ); split_string_by_delimiter --------------------------- duke 2018 sun mike 1 postgres=# SELECT get_oldest_person(); get_oldest_person ------------------------------------------------ The oldest person is Java, Duke with 23 years! |
You are now ready and deploy new, replace or remove old Java functions with PL/Java.
Please note: This is just a quick example to show you what's possible with PL/Java. For use in production make sure you read the documentation for potential adjustments. You can follow the official Hello World example in addition and read through the recommendation section for setting the appropriate VM options.
For a more detailed deep dive into PostgreSQL server-side programming, have a look at this excellent book: PostgreSQL 11 Server Side Programming Quick Start Guide: Effective database programming and interaction
I've created a simple Dockerfile for you with everything already set up to try it on your machine. You can find in my GitHub repository with further instructions. Big thanks to jcflack for the feedback and adjustments for this blog post.
Have fun writing PL/Java functions,
Phil
