目录
1、查看是否已经安装了orafunc插件
# sudo find / -name orafunc*
/usr/local/greenplum-db-5.11.1/lib/postgresql/orafunc.so
/usr/local/greenplum-db-5.11.1/share/postgresql/contrib/orafunc.sql
/gpdb/app/lib/postgresql/orafunc.so
/gpdb/app/share/postgresql/contrib/orafunc.sql
以上标红的即可安装oracle函数的sql
2、安装oracle插件
[[email protected] contrib]$ psql -d chinadaas -f /gpdb/app/share/postgresql/contrib/orafunc.sql
CREATE SCHEMA
SET
BEGIN
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
psql:/gpdb/app/share/postgresql/contrib/orafunc.sql:172: NOTICE: aggregate oracompat.listagg(text) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
psql:/gpdb/app/share/postgresql/contrib/orafunc.sql:178: NOTICE: aggregate oracompat.listagg(text,text) does not exist, skipping
DROP AGGREGATE
CREATE AGGREGATE
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
COMMIT
3 查看greenplum安装的oracle函数
[[email protected] contrib]$ psql -d chinadaas
psql (8.3.23)
Type "help" for help.
chinadaas=# \df oracompat.*
List of functions
Schema | Name | Result data type | Argument data types | Type
-----------+------------------+--------------------------+-------------------------------------------------+--------
oracompat | add_months | date | day date, value integer | normal
oracompat | bitand | bigint | bigint, bigint | normal
oracompat | concat | text | anyarray, anyarray | normal
oracompat | concat | text | anyarray, text | normal
oracompat | concat | text | text, anyarray | normal
oracompat | concat | text | text, text | normal
oracompat | dump | character varying | "any" | normal
oracompat | dump | character varying | "any", integer | normal
oracompat | instr | integer | str text, patt text | normal
oracompat | instr | integer | str text, patt text, start integer | normal
oracompat | instr | integer | str text, patt text, start integer, nth integer | normal
oracompat | last_day | date | value date | normal
oracompat | listagg | text | text | agg
oracompat | listagg | text | text, text | agg
oracompat | listagg1_transfn | text | text, text | normal
oracompat | listagg2_transfn | text | text, text, text | normal
oracompat | lnnvl | boolean | boolean | normal
oracompat | months_between | numeric | date1 date, date2 date | normal
oracompat | nanvl | double precision | double precision, double precision | normal
oracompat | nanvl | numeric | numeric, numeric | normal
oracompat | nanvl | real | real, real | normal
oracompat | next_day | date | value date, weekday integer | normal
oracompat | next_day | date | value date, weekday text | normal
oracompat | nlssort | bytea | text, text | normal
oracompat | nvl | anyelement | anyelement, anyelement | normal
oracompat | nvl2 | anyelement | anyelement, anyelement, anyelement | normal
oracompat | reverse | text | str text | normal
oracompat | reverse | text | str text, start integer | normal
oracompat | reverse | text | str text, start integer, _end integer | normal
oracompat | round | date | value date | normal
oracompat | round | date | value date, fmt text | normal
oracompat | round | timestamp with time zone | value timestamp with time zone | normal
oracompat | round | timestamp with time zone | value timestamp with time zone, fmt text | normal
oracompat | substr | text | str text, start integer | normal
oracompat | substr | text | str text, start integer, len integer | normal
oracompat | trunc | date | value date | normal
oracompat | trunc | date | value date, fmt text | normal
oracompat | trunc | timestamp with time zone | value timestamp with time zone | normal
oracompat | trunc | timestamp with time zone | value timestamp with time zone, fmt text | normal
(39 rows)
重新连接后便可以看到以下的函数，在oracompat的schema下
4 给函数付给其他的schema使用
Oracle的兼容函数都安装在oracompat的schema下面。为了访问这些Oracle函数，可以指定oracompat前缀或者修改数据库的搜索路径
# show search_path;
search_path
----------------
"$user",public
(1 row)
付给其他的schema使用
ALTER DATABASE chinadaas SET search_path = history, public, oracompat,ods;
5 使用oracle函数
以上付给schema后需要重新连接以下数据库即可
chinadaas=# select nvl(null,8);
nvl
-----
8
(1 row)