PL/Java: stored procedures, triggers, and functions for PostgreSQL™

You can use PL/Java to implement store procedures in Java.

In this document I describe how to deploy and setup a Postgres database with one of the dockerized prebuild packages I have generated:

  • Postgres 11
  • Java 11
  • PL/Java 1.6.2

Dockerized PL/Java

Adapt this stack as per your convenience, then, run docker stack deploy -c docker-compose.yaml postgres or docker-compose -f docker-compose.yaml up --detach, wait for it to initialize completely, and visit http://swarm-ip:9000, http://localhost:9000, or http://host-ip:9000 (as appropriate).

The PWD button below allows you to deploy the stack and test if this is what you expect.

services:

  db:
    image: adrianescutia/postgres11-pljava:1.6.2
    restart: always
    environment:
      - POSTGRES_DB=ades
      - POSTGRES_USER=ades
      - POSTGRES_PASSWORD=supersecretpwd

  adminer:
    image: adminer
    depends_on:
      - db
    restart: always
    ports:
      - 9000:8080

Try in PWD

Issues with PWD? See troubleshooting below.

You will have to set pljava.libjvm_location and create the java extension, to make this configuration use the SQL in our resources, either, in Adminer UI, or executing following commands:

curl https://adrianes.rebelion.la//resources/postgres/pljava/setup.sql -o setup.sql
# Execute the SQL in the database container
#cat ./setup.sql | docker exec -i <container-name> psql -U <user> -d <database>
cat ./setup.sql | docker exec -i pljava_db_1 psql -U ades -d ades

NOTE: Be sure to update the database name accordingly.

Custom functions

  1. Copy your Java archives (JAR files) to the same directory on all Database hosts. i.e.: $HOME/pljava/.
  2. Install the JAR file:
    SELECT sqlj.install_jar(/var/lib/postgresql/share/postgresql/pljava/pljava-hello.jar, 'hello', true);
  3. Set the pljava_classpath server configuration parameter. For example:
    SELECT sqlj.set_classpath('javatest', 'hello');

I hope you find this document helpful. Thanks!

Troubleshooting

If the PWD stack doesn’t start.

  1. Start a new session.
  2. Click the link “Add New Instance”
  3. Drag and Drop the file from your desktop.
    1. Other option is to get the file with curl: curl https://adrianescutia.github.io/adrianes/resources/postgres/pljava/docker-compose.yaml -o docker-compose.yaml

The caveat with curl is that you need to type the full address!! It doesn’t support copy/paste (at least not when I tested).

File upload
Source: The best way to learn Docker for Free: Play-With-Docker (PWD)

References

If there is a specific version you require, you need to Build PL/Java and install your own version as per your requirements (Postgres and Java version).

For any feedback or request, don't hesitate, open an `issue` and let me know. Don't be shy.