edit-icon download-icon

Use PL_Java UDF

Last Updated: Mar 15, 2018

HybridDB for PostgreSQL supports compiling and uploading JAR software packages written in PL/Java languages, and using these JAR packages to create user-defined functions (UDF). The PL/Java language supported in this feature is Community Edition PL/Java 1.5.0 and the JVM version is 1.8.

This document describes how to create a PL/Java UDF. For more PL/Java examples, see PL/Java Code. You can also view How to Compile.

Procedure

  1. In HybridDB for PostgreSQL, run the following command to create a PL/Java plug-in. The command only needs to be ran once for the database.

    1. create extension pljava;
  2. Compile the UDF based on your business requirements. For example, you can use the following code to compile the Test.java file:

    1. public class Test
    2. {
    3. public static String substring(String text, int beginIndex,
    4. int endIndex)
    5. {
    6. return text.substring(beginIndex, endIndex);
    7. }
    8. }
  3. Compile the manifest.txt file.

    1. Manifest-Version: 1.0
    2. Main-Class: Test
    3. Specification-Title: "Test"
    4. Specification-Version: "1.0"
    5. Created-By: 1.7.0_99
    6. Build-Date: 01/20/2016 21:00 AM
  4. Run the following command to compile and package the program.

    1. javac Test.java
    2. jar cfm analytics.jar manifest.txt Test.class
  5. Upload the analytics.jar file generated in Step 4 to the OSS by using the following OSS console command.

    1. osscmd put analytics.jar oss://zzz
  6. In HybridDB for PostgreSQL, run the “Create Library” command to import the file to HybridDB for PostgreSQL.

    Note: The “Create Library” command only supports filepath and you can import one file a time. In addition, the “Create Library” command also supports byte streams to directly import files without using the OSS. For more information, see Use the Create Library Command.

    1. create library example language java from 'oss://oss-cn-hangzhou.aliyuncs.com filepath=analytics.jar id=xxx key=yyy bucket=zzz';
  7. In HybridDB for PostgreSQL, run the following command to create and use the UDF.

    1. create table temp (a varchar) distributed randomly;
    2. insert into temp values ('my string');
    3. create or replace function java_substring(varchar, int, int) returns varchar as 'Test.substring' language java;
    4. select java_substring(a, 1, 5) from temp;
Thank you! We've received your feedback.