Greenplum 的Oracle兼容性之 - orafunc (orafce)
背景
Greenplum gpAux下面有一个插件是orafunc，包含了常用的oracle函数：
add_months
bitand
concat
cosh
decode
dump
instr
last_day
listagg
lnnvl
months_between
nanvl
next_day
nlssort
nvl
nvl2
oracle.substr
reverse
round
sinh
tanh
trunc
安装方法：
cd gpsrc/gpAux/extensions/orafce/
export PATH=/home/digoal/gphome/bin:$PATH
make USE_PGXS=1
make USE_PGXS=1 install
将so拷贝到其他主机
gpscp -f ./host orafunc.so =:/home/digoal/gphome/lib/postgresql/orafunc.so
在需要安装oracle function的数据库中调用orafunc.sql
psql -f /home/digoal/gphome/share/postgresql/contrib/orafunc.sql
这些函数被安装到了oracompat schema的下面
postgres=# \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)
附加 concat兼容
默认情况下orafunc的concat是两个参数的，如果有任意个参数需要合并，那么有两种方法：
1、variadic参数，因为内部需要unnest，所以仅支持PostgreSQL
create or replace function concat(VARIADIC text[]) returns text as $$
select string_agg(xx,'') from unnest($1) as t(xx);
$$ language sql strict immutable;
2、定义若干个concat，不同的参数个数，这个方法比较笨，但是Greenplum也只能这么干才可以支持多个输入的合并。
create or replace function concat(text,text) returns text as $$
select string_agg(xx,'') from unnest(array[$1,$2]) as t(xx);
$$ language sql strict immutable;
create or replace function concat(text,text,text) returns text as $$
select string_agg(xx,'') from unnest(array[$1,$2,$3]) as t(xx);
$$ language sql strict immutable;
........
文档参考
http://gpdb.docs.pivotal.io/4360/utility_guide/orafce_ref.html