Using PL/Java in Greenplum

In this article we are going to show you how to write PL/Java functions in Greenplum.
I assume that you have a working Greenplum (or Greenplum Community Edition) at your disposal. In this example we will use version **4.0.4**, installed in /usr/local/greenplum-db-4.0.4.0 (which is the default location).

# Install PL/Java
In order to use PL/Java in a Greenplum Database, you must install **JDK 1.6** on every host (master and segments) in your Greenplum Database cluster. In case of Community Edition, you only have one host.
In this example we will use **OpenJDK 1.6** on a **CentOS 5** server running [Greenplum Community Edition](http://www.greenplum.com/community/), which can be used for free on a single host (refer to the Greenplum website for licensing information).

# yum install java-1.6.0-openjdk.x86_64

Make sure that the JAVA_HOME environment variable is set up for the gpadmin user on all hosts.
In our example, I have used:

$ export JAVA_HOME=/usr/lib/jvm/java-1.6.0-openjdk-1.6.0.0.x86_64

in gpadmin’s .bashrc file.
Then put the Java libraries in the library path.
On Linux you can create a file containing the right path under
/etc/ld.so.conf.d/ directory and run ldconfig.
So execute, as root:

# echo "$JAVA_HOME/jre/lib/amd64/server" > /etc/ld.so.conf.d/libjdk.conf
# ldconfig

# Enable PL/Java in Greenplum
Run the install.sql file shipped with Greenplum to enable PL/Java in the given database.
In a default installation, it is placed in /usr/local/greenplum-db-4.0.4.0/share/postgresql/pljava directory.
**Important**: you have to run this script as a database superuser!

$ psql -U gpadmin -f /usr/local/greenplum-db/share/postgresql/pljava/install.sql pljavatestdb
CREATE FUNCTION
CREATE FUNCTION
CREATE LANGUAGE
CREATE LANGUAGE

In order to uninstall PL/Java, you can use an SQL script file called uninstall.sql:

$ psql -U gpadmin -f /usr/local/greenplum-db/share/postgresql/pljava/uninstall.sql pljavatestdb
DROP LANGUAGE
DROP LANGUAGE

# The real thing: hello world!
We are ready to make a real – but simple – PL/Java function.
As far as this article is concerned, we will work in a temporary directory (for production environments we recommend that you use GIT or any other source control technology).
*Step 1*: Make a temporary directory and enter inside

$ cd `mktemp -d`

*Step 2*: Write your Java function in a file called HelloWorld.java, containing the following lines of code:

package mypkg;
public class HelloWorld
{
static java.lang.String say_hello()
{
return "Hello World";
}
}

As you can read, the function we have written simply returns “Hello World” as a java.lang.String, that is mapped in Greenplum (and PostgreSQL) as a TEXT.
*Step 3*: compile HelloWorld.java and create a *jar* of it, named HelloWorld.jar:

$ javac HelloWorld.java
$ jar cvf HelloWorld.jar HelloWorld.class

*Step 4*: copy HelloWorld.jar in the defult Greenplum classpath, and setup the pljava_classpath configuration option (in this example, we will perform it only for our session using set_config, for permanent changes set it in the master’s postgresql.conf file):

$ cp HelloWorld.jar /usr/local/greenplum-db-4.0.4.0/lib/postgresql/java/
$ psql -U gpadmin postgres
psql (8.2.14)
Type "help" for help.
postgres=# SELECT set_config('pljava_classpath', 'HelloWorld.jar', false);
set_config
----------------
HelloWorld.jar
(1 row)

*Step 5*: Now it is time to add the actual PL/Java function inside the database:

pljavatestdb=# CREATE FUNCTION say_hello()
pljavatestdb-# RETURNS TEXT
pljavatestdb-# AS 'mypkg.HelloWorld.say_hello'
pljavatestdb-# LANGUAGE java;
CREATE FUNCTION

Great! You can execute it by simply calling:

pljavatestdb=# SELECT say_hello();
say_hello
-------------
Hello World
(1 row)

**WARNING**
If you encounter any kind of heap memory error, I advice to add the pljava_vmoptions='-Xmx256m' option in postgres configuration file and set that to a resonable value for your goals.
That option sets the heap memory size for the Java Virtual Machine.
Obviously, you can organize your classes in packages, simply remember to specify the correct path in the AS 'path.to.function' row.
We encourage you to evaluate Greenplum Community Edition and [participate to the forum](http://www.greenplum.com/community/forums/), where Greenplum experts can help you getting started with it.