Postgesql原生内部不支持java编写函数，触发器，存储过程，这里需要第三方插件PL/java，这里介绍一下，如何安装PL/java。
- 安装gcc-c++, gcc, java 1.8.x, openssl
# yum install gcc* openssl* java-1.8.0-openjdk java-1.8.0-openjdk-devel
# java -version
openjdk version "1.8.0_242"
OpenJDK Runtime Environment (build 1.8.0_242-b07)
OpenJDK 64-Bit Server VM (build 25.242-b07, mixed mode)
# gcc --version
gcc (GCC) 6.2.0
Copyright (C) 2016 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
- 安装Apache Maven
wget http://mirror.easyname.ch/apache/maven/maven-3/3.3.9/binaries/apache-maven-3.3.9-bin.tar.gz
tar -zxvf apache-maven-3.3.9-bin.tar.gz
mv apache-maven-3.3.9 /usr/local/
cd apache-maven-3.3.9
mvn -version
Apache Maven 3.3.9 (bb52d8502b132ec0a5a3f4c09453c07478323dc5; 2015-11-11T00:41:47+08:00)
Maven home: /usr/local/apache-maven-3.3.9
Java version: 1.8.0_151, vendor: Oracle Corporation
Java home: /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.151-1.b12.el6_9.x86_64/jre
Default locale: en_US, platform encoding: UTF-8
OS name: "linux", version: "2.6.32-642.3.1.el6.x86_64", arch: "amd64", family: "unix"
- 安装postgresql11.3（略）
- 下载PL/java,这里没有下git 的master版本，下载了以前的稳定版本。
wget https://github.com/tada/pljava/archive/V1_5_2.tar.gz
tar -xvf V1_5_2.tar.gz
cd pljava-1_5_2/
mvn clean install
[INFO] Executed tasks
[INFO]
[INFO] --- maven-install-plugin:2.5.2:install (default-install) @ pljava-packaging ---
[INFO] Installing /home/postgres/pljava-1_5_2/pljava-packaging/pom.xml to /home/postgres/.m2/repository/org/postgresql/pljava-packaging/1.5.2/pljava-packaging-1.5.2.pom
[INFO] ------------------------------------------------------------------------
[INFO] Reactor Summary:
[INFO]
[INFO] PostgreSQL PL/Java ................................. SUCCESS [ 4.954 s]
[INFO] PL/Java API ........................................ SUCCESS [ 1.435 s]
[INFO] PL/Java backend Java code .......................... SUCCESS [ 0.488 s]
[INFO] PL/Java backend native code ........................ SUCCESS [ 7.580 s]
[INFO] PL/Java Deploy ..................................... SUCCESS [ 0.189 s]
[INFO] PL/Java Ant tasks .................................. SUCCESS [ 0.113 s]
[INFO] PL/Java examples ................................... SUCCESS [ 0.109 s]
[INFO] PL/Java packaging .................................. SUCCESS [ 1.572 s]
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 17.410 s
[INFO] Finished at: 2020-03-18T10:11:53+08:00
[INFO] Final Memory: 62M/2055M
[INFO] ------------------------------------------------------------------------
cd pljava-1_5_2/
java -jar pljava-packaging/target/pljava-pg11.3-amd64-Linux-gpp.jar
/opt/pgsql11.3/lib/libpljava-so-1.5.2.so as bytes
/opt/pgsql11.3/share/pljava/pljava-1.5.2.jar as bytes
/opt/pgsql11.3/share/pljava/pljava-api-1.5.2.jar as bytes
/opt/pgsql11.3/share/pljava/pljava-examples-1.5.2.jar as bytes
/opt/pgsql11.3/share/extension/pljava.control as lines (ASCII)
/opt/pgsql11.3/share/pljava/pljava--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--unpackaged--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.1--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.1-BETA3--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.1-BETA2--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.1-BETA1--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.0--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.0-BETA3--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.0-BETA2--1.5.2.sql as lines (UTF8)
/opt/pgsql11.3/share/pljava/pljava--1.5.0-BETA1--1.5.2.sql as lines (UTF8)
- postgres系统用户环境变量
cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export PS1="$USER@`/bin/hostname -s`-> "
export PGUSER=hank
export PGPORT=1921
export PGDATA=/opt/pgdata11.3/pg_root
export LANG=en_US.utf8
export PGHOME=/opt/pgsql11.3
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:/usr/local/apache-maven-3.3.9/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'
export PKG_CONFIG_PATH=/usr/local/lib/pkgconfig:$PKG_CONFIG_PATH
export JAVA_HOME=/usr/local/jdk1.8.0_121
export CLASSPATH=/usr/local/jdk1.8.0_121/lib/ext
另外，maven仓库地址需要修改一下，因为从maven的中央仓库下载速度太慢，我们修改使用阿里云的仓库,修改添加以下内容
vi /usr/local/apache-maven-3.3.9/conf/settings.xml
修改
<mirror>
<id>alimaven</id>
<name>aliyun maven</name>
<url>
http://maven.aliyun.com/nexus/content/groups/public/
</url>
<mirrorOf>central</mirrorOf>
</mirror>
<profile>
<id>NEXUS2</id>
<repositories>
<repository>
<!-- 用maven.aliyun.com替换中央库 central -->
<id>maven-aliyun</id>
<name>public repository in maven.aliyun.com</name>
<url>http://maven.aliyun.com/nexus/content/groups/public</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</repository>
<repository>
<id>snapshots3.46</id>
<name>release repository in 3.46</name>
<url>http://172.16.3.46:8081/nexus/content/groups/public-snapshots</url>
<releases>
<enabled>true</enabled>
</releases>
<snapshots>
<enabled>true</enabled>
</snapshots>
</repository>
</repositories>
<pluginRepositories>
<pluginRepository>
<id>maven-aliyun</id>
<name>public repository in maven.aliyun.com/</name>
<url>http://maven.aliyun.com/content/groups/public/</url>
<snapshots>
<enabled>false</enabled>
</snapshots>
</pluginRepository>
</pluginRepositories>
</profile>
<activeProfiles>
<activeProfile>NEXUS2</activeProfile>
</activeProfiles>
注意：
我的本地仓库是在/home/postgres/.m2/repository，如果mvn clean insall 失败，可以删除该本地仓库，让其重新下载安装。
- 进入数据库，安装插件
psql -h 127.0.0.1
hank=# \c hank postgres
You are now connected to database "hank" as user "postgres".
hank=# create extension pljava ;
WARNING: Java virtual machine not yet loaded
DETAIL: libjvm: cannot open shared object file: No such file or directory
HINT: SET pljava.libjvm_location TO the correct path to the jvm library (libjvm.so or jvm.dll, etc.)
ERROR: cannot use PL/Java before successfully completing its setup
HINT: Check the log for messages closely preceding this one, detailing what step of setup failed and what will be needed, probably setting one of the "pljava." configuration variables, to complete the setup. If there is not enough help in the log, try again with different settings for "log_min_messages" or "log_error_verbosity".
出现以上错误，说明没有正确配置pljava.libjvm_location参数，找到匹配的libjvm.so文件，在postgresql.conf添加路径配置
find / -name "*libjvm.so*"
/usr/local/jdk1.8.0_121/jre/lib/amd64/server/libjvm.so
/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.201.x86_64/jre/lib/amd64/server/libjvm.so
/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b07-1.el6_10.x86_64/jre/lib/amd64/server/libjvm.so
/usr/lib/jvm/java-1.5.0-gcj-1.5.0.0/jre/lib/x86_64/client/libjvm.so
/usr/lib/jvm/java-1.5.0-gcj-1.5.0.0/jre/lib/x86_64/server/libjvm.so
vi postgresql.conf
pljava.libjvm_location='/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.242.b07-1.el6_10.x86_64/jre/lib/amd64/server/libjvm.so'
重载配置：
pg_ctl reload
再次安装插件
hank=# \c hank postgres
You are now connected to database "hank" as user "postgres".
hank=# create extension pljava ;
CREATE EXTENSION
hank=# \dx pljava
List of installed extensions
Name | Version | Schema | Description
--------+---------+--------+--------------------------------------------------------------
pljava | 1.5.2 | sqlj | PL/Java procedural language (https://tada.github.io/pljava/)
hank=# CREATE FUNCTION getsysprop(VARCHAR)
hank-# RETURNS VARCHAR
hank-# AS 'java.lang.System.getProperty'
hank-# LANGUAGE java;
CREATE FUNCTION
hank=# SELECT getsysprop('user.home');
getsysprop
----------------
/home/postgres
(1 row)
参考：
https://tada.github.io/pljava/install/install.html#PLJava_configuration_variables
https://raghavt.blog/install-pl-java-1-5-2-in-postgresql-11/
