GreenPlum 集群中通过orafunc实现Oracle兼容函数

oracle数据迁移到greenplum有一个不得不面对的难题，那就是很多存储过程中调用的存储函数，一个个改起来太麻烦了，所以如果想迁移顺利，一个比较的办法是，在greenplum里面自己实现同名字同功能的函数。

1，查看是否已经安装了orafunc插件

[root@dwhm01_2_111 ~]# find / -name orafunc*/data/soft/yes/share/postgresql/contrib/orafunc.sql/data/soft/yes/lib/postgresql/orafunc.so/data/greenplum-db-4.3.12.0/share/postgresql/contrib/orafunc.sql/data/greenplum-db-4.3.12.0/lib/postgresql/orafunc.so[root@dwhm01_2_111 ~]# 

OK，看到orafunc.sql已经有了，那就可以实行安装了。

2，准备测试数据

yueworld_db=# create table z4(name varchar, d date);NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Greenplum Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.CREATE TABLEyueworld_db=# yueworld_db=# yueworld_db=# insert into z4 values('zhangsan','2017-03-05');INSERT 0 1yueworld_db=# insert into z4 values('lisi','2017-03-09');INSERT 0 1yueworld_db=# insert into z4 values(null,'2017-03-11');INSERT 0 1yueworld_db=# select * from z4;   name   |     d      ----------+------------          | 2017-03-11 lisi     | 2017-03-09 zhangsan | 2017-03-05(3 rows)yueworld_db=# 

3，安装oracle函数

安装命令：psql -dstu_db -f $GPHOME/share/postgresql/contrib/orafunc.sql

安装过程：

[gpadmin@dwhm01_2_111 ~]$ psql -dstu_db -f $GPHOME/share/postgresql/contrib/orafunc.sqlCREATE SCHEMASETBEGINCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONpsql:/usr/local/greenplum-db/share/postgresql/contrib/orafunc.sql:172: NOTICE:  aggregate oracompat.listagg(text) does not exist, skippingDROP AGGREGATECREATE AGGREGATEpsql:/usr/local/greenplum-db/share/postgresql/contrib/orafunc.sql:178: NOTICE:  aggregate oracompat.listagg(text,text) does not exist, skippingDROP AGGREGATECREATE AGGREGATECREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCREATE FUNCTIONCOMMIT[gpadmin@dwhm01_2_111 ~]$ 

4，查看已经安装的函数组件

[gpadmin@dwhm01_2_111 lib]$ psql -dstu_dbpsql (8.2.15)Type "help" for help.yueworld_db=# \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)yueworld_db=# 

5，去check函数是否可以执行：

yueworld_db=# select nvl(null,8);ERROR:  function nvl(unknown, integer) does not existLINE 1: select nvl(null,8);               ^HINT:  No function matches the given name and argument types. You may need to add explicit type casts.yueworld_db=# 

查看当前路径地址：

yueworld_db=# show search_path;  search_path   ---------------- "$user",public(1 row)yueworld_db=# 

Oracle的兼容函数都安装在oracompat的schema下面。为了访问这些Oracle函数，可以指定oracompat前缀或者修改数据库的搜索路径：

ALTER DATABASEstu_db SET search_path = mch,dw, public, oracompat;yueworld_db=# ALTER DATABASE stu_db SET search_path = dw, public, oracompat;ALTER DATABASEyueworld_db=# 

然后重新登陆测试

[gpadmin@dwhm01_2_111 lib]$ psql -dstu_dbpsql (8.2.15)Type "help" for help.yueworld_db=# select nvl(null,8); nvl -----   8(1 row)stu_db=# stu_db=# select nvl(name,'null'),decode(name,'zhangsan',1,'lisi',2,0),d from z4;   nvl    | case |     d      ----------+------+------------ lisi     |    2 | 2017-03-09 null     |    0 | 2017-03-11 zhangsan |    1 | 2017-03-05(3 rows)stu_db=# 

OK，好，问题来了，我现在登录的是gpadmin账号，超级管理员，所以生效，下面切换回普通用户是否也能生效呢？

blog源地址：http://blog.csdn.net/mchdba/article/details/72475802，作者黄杉 mchdba，谢绝转载。

6，普通用户mch是否能使用这些函数

[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -WPassword for user mch: psql: FATAL:  no pg_hba.conf entry for host "[local]", user "mch", database "stu_db", SSL off[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W -h192.168.2.111Password for user mch: psql (8.2.15)Type "help" for help.stu_db=> stu_db=> select instr('greenplum', 'e')  from z4;ERROR:  function instr(unknown, unknown) does not existLINE 1: select instr('greenplum', 'e')  from z4;               ^HINT:  No function matches the given name and argument types. You may need to add explicit type casts.stu_db=> 

查看下当前路径，是否可以看到oracompat下面的函数信息

stu_db=> \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)stu_db=> 

既然可以看到oracompat下面的函数信息，那为什么不能调用呢？

问题分析，因为oracompat的schema是超级管理员所建，所以普通用户mch无法访问，那么需要将这个schema下面的权限授予mch用户才行，切换回gpadmin超级管理员，赋予mch对schema oracompat的权限，执行授权命令：grant all on schema oracompat to mch;

[gpadmin@dwhm01_2_111 lib]$ psql -dstu_dbpsql (8.2.15)Type "help" for help.stu_db=# grant all on schema oracompat to mch;GRANTstu_db=# \q

然后再切换成mch用户问题解决，执行查询函数，OK，可以调用decode、instr函数了。

[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W -h192.168.2.111Password for user mch: psql (8.2.15)Type "help" for help.stu_db=> select nvl(name,'None'),decode(name,'zhangsan',1,'lisi',2,0),instr(name,'li'),d from z4;   nvl    | case | instr |     d      ----------+------+-------+------------ lisi     |    2 |     1 | 2017-03-09 None     |    0 |       | 2017-03-11 zhangsan |    1 |     0 | 2017-03-05(3 rows)stu_db=> 

7，诡异的问题，psql突然连接不上

[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch psql: FATAL:  no pg_hba.conf entry for host "[local]", user "mch", database "stu_db", SSL off[gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -WPassword for user mch: psql: FATAL:  no pg_hba.conf entry for host "[local]", user "mch", database "stu_db", SSL off[gpadmin@dwhm01_2_111 gpseg-1]$ 

【问题解决】：需要加上-h参数

[gpadmin@dwhm01_2_111 gpseg-1]$ [gpadmin@dwhm01_2_111 gpseg-1]$ psql -dstu_db -U mch -W -h192.168.2.111 Password for user mch: psql (8.2.15)Type "help" for help.stu_db=> \q[gpadmin@dwhm01_2_111 gpseg-1]$ 

601777 601258，共享汽车2个概念股，而且还捎带了雄安概念

8，卸载oracle函数

最后，如果你觉得这个不太方便，也可以卸载这些oracle函数

psql -dstu_db -f $GPHOME/share/postgresql/contrib/uninstall_orafunc.sql

9，存储函数，授权

建立存储函数

CREATE FUNCTION add(integer, integer) RETURNS integer    AS 'select $1 + $2;'    LANGUAGE SQL    IMMUTABLE    RETURNS NULL ON NULL INPUT;

函数赋予给另外一个用户mch

stu_db=# grant execute on function add(integer,integer)  to mch;GRANTstu_db=# 

参考文章地址：http://gpdb.docs.pivotal.io/4360/utility_guide/orafce_ref.html?spm=5176.100239.blogcont8943.6.7TCKiv