Greenplum 的Oracle兼容性之 - orafunc (orafce)

TAG 21 , TAG 11

作者

digoal

日期

2016-03-24

标签

PostgreSQL , Greenplum , Oracle , 兼容性 , orafce, orafunc

背景

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

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.