Install PLJava Postgresql 8.4 on Ubuntu LTS 10.04 Lucid Lynx

Before you can use java stored procedures on PostreSQL you have to install PLJava. The following assumes you already have Java 6 (Sun/Oracle or OpenJdk) installed on your machine.

Download the PLJava binary

This binary can be found on the pljava page of PgGoundry. Since we are running a 64 bit server with PostgreSQL 8.4 we used this distribution.

Next we extract this tar.gz file in the newly created directory /usr/src/pljava.

Setup PLJava

Next we create a file /etc/ld.so.conf.d/postgres.conf with the following content:

 /usr/lib/jvm/java-6-sun/lib
 /usr/lib/jvm/java-6-sun/jre/lib/amd64/server

Now run:

sudo ldconfig

Next edit the /etc/postgresql/8.4/main/postgresql.conf and add the following lines:

custom_variable_classes = 'pljava' 
pljava.classpath = '/usr/lib/postgresql/8.4/lib/pljava.jar'

Now copy the files pljava.jar and pljava.so to /usr/lib/postgresql/8.4/lib:

sudo cp /usr/src/pljava/pljava.jar /usr/lib/postgresql/8.4/lib
sudo cp /usr/src/pljava/pljava.so /usr/lib/postgresql/8.4/lib

Finally restart postgres:

sudo /etc/init.d/postgresql-8.4 restart

Install PLJava

Go to the /usr/src/pljava directory and run:

sudo su postgres -c "/usr/bin/psql -d template1 -f /usr/src/pljava/install.sql"

Where template1 is the database you want to install pljava. The result should look simular to this:

CREATE SCHEMA
GRANT
CREATE FUNCTION
CREATE LANGUAGE
CREATE FUNCTION
CREATE LANGUAGE
psql:/usr/src/pljava/install.sql:23: NOTICE:  CREATE TABLE will create implicit sequence "jar_repository_jarid_seq" for serial column "jar_repository.jarid"
psql:/usr/src/pljava/install.sql:23: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "jar_repository_pkey" for table "jar_repository"
psql:/usr/src/pljava/install.sql:23: NOTICE:  CREATE TABLE / UNIQUE will create implicit index "jar_repository_jarname_key" for table "jar_repository"
CREATE TABLE
GRANT
psql:/usr/src/pljava/install.sql:32: NOTICE:  CREATE TABLE will create implicit sequence "jar_entry_entryid_seq" for serial column "jar_entry.entryid"
psql:/usr/src/pljava/install.sql:32: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "jar_entry_pkey" for table "jar_entry"
psql:/usr/src/pljava/install.sql:32: NOTICE:  CREATE TABLE / UNIQUE will create implicit index "jar_entry_jarid_key" for table "jar_entry"
CREATE TABLE
GRANT
ALTER TABLE
psql:/usr/src/pljava/install.sql:43: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "classpath_entry_pkey" for table "classpath_entry"
CREATE TABLE
GRANT
psql:/usr/src/pljava/install.sql:50: NOTICE:  CREATE TABLE will create implicit sequence "typemap_entry_mapid_seq" for serial column "typemap_entry.mapid"
psql:/usr/src/pljava/install.sql:50: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "typemap_entry_pkey" for table "typemap_entry"
CREATE TABLE
GRANT
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION

You are now ready to use Java stored procedures on PostreSQL 8.4 !!!

Test PLJava

To verify that it all works we are going to install a java stored procedure. Open your favorite SQL-editor and connect to the database and run:

CREATE FUNCTION getsysprop(VARCHAR)
  RETURNS VARCHAR
  AS 'java.lang.System.getProperty'
  LANGUAGE java;

Now run:

SELECT getsysprop('user.home');

This should return something like:

 getsysprop
 -------------------
 /var/lib/postgresql

Many thanks to BANYM'S BLOG for the inspiration for this blog.