hawq-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From foy...@apache.org
Subject [7/9] incubator-hawq git commit: HAWQ-149. Add orafce, gp_cancel_query, pgbench and extprotocol to HAWQ
Date Thu, 12 Nov 2015 20:25:24 GMT
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/expected/orafunc.out
----------------------------------------------------------------------
diff --git a/contrib/orafce/expected/orafunc.out b/contrib/orafce/expected/orafunc.out
new file mode 100644
index 0000000..0306adb
--- /dev/null
+++ b/contrib/orafce/expected/orafunc.out
@@ -0,0 +1,1393 @@
+\set ECHO none
+--
+-- test built-in date type oracle compatibility functions
+--
+SELECT add_months ('2003-08-01', 3);
+ add_months 
+------------
+ 2003-11-01
+(1 row)
+
+SELECT add_months ('2003-08-01', -3);
+ add_months 
+------------
+ 2003-05-01
+(1 row)
+
+SELECT add_months ('2003-08-21', -3);
+ add_months 
+------------
+ 2003-05-21
+(1 row)
+
+SELECT add_months ('2003-01-31', 1);
+ add_months 
+------------
+ 2003-02-28
+(1 row)
+
+SELECT add_months ('2008-02-28', 1);
+ add_months 
+------------
+ 2008-03-28
+(1 row)
+
+SELECT add_months ('2008-02-29', 1);
+ add_months 
+------------
+ 2008-03-31
+(1 row)
+
+SELECT add_months ('2008-01-31', 12);
+ add_months 
+------------
+ 2009-01-31
+(1 row)
+
+SELECT add_months ('2008-01-31', -12);
+ add_months 
+------------
+ 2007-01-31
+(1 row)
+
+SELECT add_months ('2008-01-31', 95903);
+ add_months 
+------------
+ 9999-12-31
+(1 row)
+
+SELECT add_months ('2008-01-31', -80640);
+  add_months   
+---------------
+ 4712-01-31 BC
+(1 row)
+
+SELECT last_day(to_date('2003/03/15', 'yyyy/mm/dd'));
+  last_day  
+------------
+ 2003-03-31
+(1 row)
+
+SELECT last_day(to_date('2003/02/03', 'yyyy/mm/dd'));
+  last_day  
+------------
+ 2003-02-28
+(1 row)
+
+SELECT last_day(to_date('2004/02/03', 'yyyy/mm/dd'));
+  last_day  
+------------
+ 2004-02-29
+(1 row)
+
+SELECT last_day('1900-02-01');
+  last_day  
+------------
+ 1900-02-28
+(1 row)
+
+SELECT last_day('2000-02-01');
+  last_day  
+------------
+ 2000-02-29
+(1 row)
+
+SELECT last_day('2007-02-01');
+  last_day  
+------------
+ 2007-02-28
+(1 row)
+
+SELECT last_day('2008-02-01');
+  last_day  
+------------
+ 2008-02-29
+(1 row)
+
+SELECT next_day ('2003-08-01', 'TUESDAY');
+  next_day  
+------------
+ 2003-08-05
+(1 row)
+
+SELECT next_day ('2003-08-06', 'WEDNESDAY');
+  next_day  
+------------
+ 2003-08-13
+(1 row)
+
+SELECT next_day ('2003-08-06', 'SUNDAY');
+  next_day  
+------------
+ 2003-08-10
+(1 row)
+
+SELECT next_day ('2008-01-01', 'sun');
+  next_day  
+------------
+ 2008-01-06
+(1 row)
+
+SELECT next_day ('2008-01-01', 'sunAAA');
+  next_day  
+------------
+ 2008-01-06
+(1 row)
+
+SELECT next_day ('2008-01-01', 1);
+  next_day  
+------------
+ 2008-01-06
+(1 row)
+
+SELECT next_day ('2008-01-01', 7);
+  next_day  
+------------
+ 2008-01-05
+(1 row)
+
+SELECT months_between (to_date ('2003/01/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd'));
+  months_between   
+-------------------
+ -2.41935483870968
+(1 row)
+
+SELECT months_between (to_date ('2003/07/01', 'yyyy/mm/dd'), to_date ('2003/03/14', 'yyyy/mm/dd'));
+  months_between  
+------------------
+ 3.58064516129032
+(1 row)
+
+SELECT months_between (to_date ('2003/07/02', 'yyyy/mm/dd'), to_date ('2003/07/02', 'yyyy/mm/dd'));
+ months_between 
+----------------
+              0
+(1 row)
+
+SELECT months_between (to_date ('2003/08/02', 'yyyy/mm/dd'), to_date ('2003/06/02', 'yyyy/mm/dd'));
+ months_between 
+----------------
+              2
+(1 row)
+
+SELECT months_between ('2007-02-28', '2007-04-30');
+ months_between 
+----------------
+             -2
+(1 row)
+
+SELECT months_between ('2008-01-31', '2008-02-29');
+ months_between 
+----------------
+             -1
+(1 row)
+
+SELECT months_between ('2008-02-29', '2008-03-31');
+ months_between 
+----------------
+             -1
+(1 row)
+
+SELECT months_between ('2008-02-29', '2008-04-30');
+ months_between 
+----------------
+             -2
+(1 row)
+
+SELECT trunc(months_between('21-feb-2008', '2008-02-29'));
+ trunc 
+-------
+     0
+(1 row)
+
+select length('jmenuji se Pavel Stehule'),dbms_pipe.pack_message('jmenuji se Pavel Stehule');
+ length | pack_message 
+--------+--------------
+     24 | 
+(1 row)
+
+select length('a bydlim ve Skalici'),dbms_pipe.pack_message('a bydlim ve Skalici');
+ length | pack_message 
+--------+--------------
+     19 | 
+(1 row)
+
+select dbms_pipe.send_message('pavel',0,1);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.send_message('pavel',0,2);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.receive_message('pavel',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select '>>>>'||dbms_pipe.unpack_message_text()||'<<<<';
+             ?column?             
+----------------------------------
+ >>>>jmenuji se Pavel Stehule<<<<
+(1 row)
+
+select '>>>>'||dbms_pipe.unpack_message_text()||'<<<<';
+          ?column?           
+-----------------------------
+ >>>>a bydlim ve Skalici<<<<
+(1 row)
+
+select dbms_pipe.receive_message('pavel',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.purge('bob');
+ purge 
+-------
+ 
+(1 row)
+
+select dbms_pipe.reset_buffer();
+ reset_buffer 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.pack_message('012345678901234+1');
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('bob',0,10);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.pack_message('012345678901234+2');
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('bob',0,10);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.pack_message('012345678901234+3');
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('bob',0,10);
+ send_message 
+--------------
+            0
+(1 row)
+
+--------------------------------------------
+select dbms_pipe.receive_message('bob',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.unpack_message_text();
+ unpack_message_text 
+---------------------
+ 012345678901234+1
+(1 row)
+
+select dbms_pipe.receive_message('bob',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.unpack_message_text();
+ unpack_message_text 
+---------------------
+ 012345678901234+2
+(1 row)
+
+select dbms_pipe.receive_message('bob',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.unpack_message_text();
+ unpack_message_text 
+---------------------
+ 012345678901234+3
+(1 row)
+
+select dbms_pipe.unique_session_name() LIKE 'PG$PIPE$%';
+ ?column? 
+----------
+ t
+(1 row)
+
+select dbms_pipe.pack_message('012345678901234-1');
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('bob',0,10);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.receive_message('bob',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.unpack_message_text();
+ unpack_message_text 
+---------------------
+ 012345678901234-1
+(1 row)
+
+select dbms_pipe.pack_message('012345678901234-2');
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('bob',0,10);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.send_message('bob',0,10);
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.receive_message('bob',0);
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.unpack_message_text();
+ unpack_message_text 
+---------------------
+ 012345678901234-2
+(1 row)
+
+select dbms_pipe.pack_message(TO_DATE('2006-10-11', 'YYYY-MM-DD'));
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('test_date');
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.receive_message('test_date');
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.next_item_type();
+ next_item_type 
+----------------
+             12
+(1 row)
+
+select dbms_pipe.unpack_message_date();
+ unpack_message_date 
+---------------------
+ 2006-10-11
+(1 row)
+
+select dbms_pipe.pack_message(to_timestamp('2008-10-30 01:23:45', 'YYYY-MM-DD HH24:MI:SS'));
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('test_timestamp');
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.receive_message('test_timestamp');
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.next_item_type();
+ next_item_type 
+----------------
+             13
+(1 row)
+
+select to_char(dbms_pipe.unpack_message_timestamp(), 'YYYY-MM-DD HH24:MI:SS');
+       to_char       
+---------------------
+ 2008-10-30 01:23:45
+(1 row)
+
+select dbms_pipe.pack_message(6262626262::numeric);
+ pack_message 
+--------------
+ 
+(1 row)
+
+select dbms_pipe.send_message('test_int');
+ send_message 
+--------------
+            0
+(1 row)
+
+select dbms_pipe.receive_message('test_int');
+ receive_message 
+-----------------
+               0
+(1 row)
+
+select dbms_pipe.next_item_type();
+ next_item_type 
+----------------
+              9
+(1 row)
+
+select dbms_pipe.unpack_message_number();
+ unpack_message_number 
+-----------------------
+            6262626262
+(1 row)
+
+select name, items, "limit", private, owner from dbms_pipe.db_pipes where name = 'bob';
+ name | items | limit | private | owner 
+------+-------+-------+---------+-------
+ bob  |     1 |    10 | f       | 
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 7, 9);
+ betwn 
+-------
+ and
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 7, 9, FALSE);
+ betwn 
+-------
+ n
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', -3, -1);
+ betwn 
+-------
+ ppy
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 'a', 'ry');
+ betwn 
+-------
+ arry
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 'a', 'ry', 1,1,FALSE,FALSE);
+ betwn 
+-------
+ r
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 'a', 'ry', 2,1,TRUE,FALSE);
+       betwn        
+--------------------
+ and Sally are very
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 'a', 'y', 2,1);
+   betwn   
+-----------
+ and Sally
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 'a', 'a', 2, 2);
+    betwn    
+-------------
+ and Sally a
+(1 row)
+
+select PLVstr.betwn('Harry and Sally are very happy', 'a', 'a', 2, 3, FALSE,FALSE);
+        betwn        
+---------------------
+ nd Sally are very h
+(1 row)
+
+select plvsubst.string('My name is %s %s.', ARRAY['Pavel','Stěhule']);
+          string           
+---------------------------
+ My name is Pavel Stěhule.
+(1 row)
+
+select plvsubst.string('My name is % %.', ARRAY['Pavel','Stěhule'], '%');
+          string           
+---------------------------
+ My name is Pavel Stěhule.
+(1 row)
+
+select plvsubst.string('My name is %s.', ARRAY['Stěhule']);
+       string        
+---------------------
+ My name is Stěhule.
+(1 row)
+
+select plvsubst.string('My name is %s %s.', 'Pavel,Stěhule');
+          string           
+---------------------------
+ My name is Pavel Stěhule.
+(1 row)
+
+select plvsubst.string('My name is %s %s.', 'Pavel|Stěhule','|');
+          string           
+---------------------------
+ My name is Pavel Stěhule.
+(1 row)
+
+select plvsubst.string('My name is %s.', 'Stěhule');
+       string        
+---------------------
+ My name is Stěhule.
+(1 row)
+
+select plvsubst.string('My name is %s.', '');
+ERROR:  too few parameters specified for template string
+select plvsubst.string('My name is empty.', '');
+      string       
+-------------------
+ My name is empty.
+(1 row)
+
+select round(to_date ('22-AUG-03', 'DD-MON-YY'),'YEAR')  =  to_date ('01-JAN-04', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select round(to_date ('22-AUG-03', 'DD-MON-YY'),'Q')  =  to_date ('01-OCT-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select round(to_date ('22-AUG-03', 'DD-MON-YY'),'MONTH') =  to_date ('01-SEP-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select round(to_date ('22-AUG-03', 'DD-MON-YY'),'DDD')  =  to_date ('22-AUG-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select round(to_date ('22-AUG-03', 'DD-MON-YY'),'DAY')  =  to_date ('24-AUG-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'YEAR')  =  to_date ('01-JAN-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'Q')  =  to_date ('01-JUL-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'MONTH') =  to_date ('01-AUG-03', 'DD-MON-YY');                                                                       
+ ?column? 
+----------
+ t
+(1 row)
+
+select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'DDD')  =  to_date ('22-AUG-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select trunc(to_date('22-AUG-03', 'DD-MON-YY'), 'DAY')  =  to_date ('17-AUG-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select next_day(to_date('01-Aug-03', 'DD-MON-YY'), 'TUESDAY')  =  to_date ('05-Aug-03', 'DD-MON-YY');                                                                 
+ ?column? 
+----------
+ t
+(1 row)
+
+select next_day(to_date('06-Aug-03', 'DD-MON-YY'), 'WEDNESDAY') =  to_date ('13-Aug-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select next_day(to_date('06-Aug-03', 'DD-MON-YY'), 'SUNDAY')  =  to_date ('10-Aug-03', 'DD-MON-YY');
+ ?column? 
+----------
+ t
+(1 row)
+
+select instr('Tech on the net', 'e') =2;
+ ?column? 
+----------
+ t
+(1 row)
+
+select instr('Tech on the net', 'e', 1, 1) = 2;
+ ?column? 
+----------
+ t
+(1 row)
+
+select instr('Tech on the net', 'e', 1, 2) = 11;
+ ?column? 
+----------
+ t
+(1 row)
+
+select instr('Tech on the net', 'e', 1, 3) = 14;
+ ?column? 
+----------
+ t
+(1 row)
+
+select instr('Tech on the net', 'e', -3, 2) = 2;
+ ?column? 
+----------
+ t
+(1 row)
+
+select instr('abc', NULL) IS NULL;
+ ?column? 
+----------
+ t
+(1 row)
+
+select 1 = instr('abc', '');
+ ?column? 
+----------
+ t
+(1 row)
+
+select 1 = instr('abc', 'a');
+ ?column? 
+----------
+ t
+(1 row)
+
+select 3 = instr('abc', 'c');
+ ?column? 
+----------
+ t
+(1 row)
+
+select 0 = instr('abc', 'z');
+ ?column? 
+----------
+ t
+(1 row)
+
+select 1 = instr('abcabcabc', 'abca', 1);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 4 = instr('abcabcabc', 'abca', 2);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 0 = instr('abcabcabc', 'abca', 7);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 0 = instr('abcabcabc', 'abca', 9);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 4 = instr('abcabcabc', 'abca', -1);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 1 = instr('abcabcabc', 'abca', -8);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 1 = instr('abcabcabc', 'abca', -9);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 0 = instr('abcabcabc', 'abca', -10);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 1 = instr('abcabcabc', 'abca', 1, 1);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 4 = instr('abcabcabc', 'abca', 1, 2);
+ ?column? 
+----------
+ t
+(1 row)
+
+select 0 = instr('abcabcabc', 'abca', 1, 3);
+ ?column? 
+----------
+ t
+(1 row)
+
+select oracle.substr('This is a test', 6, 2) = 'is';
+ ?column? 
+----------
+ t
+(1 row)
+
+select oracle.substr('This is a test', 6) =  'is a test';
+ ?column? 
+----------
+ t
+(1 row)
+
+select oracle.substr('TechOnTheNet', 1, 4) =  'Tech';
+ ?column? 
+----------
+ t
+(1 row)
+
+select oracle.substr('TechOnTheNet', -3, 3) =  'Net';
+ ?column? 
+----------
+ t
+(1 row)
+
+select oracle.substr('TechOnTheNet', -6, 3) =  'The';
+ ?column? 
+----------
+ t
+(1 row)
+
+select oracle.substr('TechOnTheNet', -8, 2) =  'On';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat('Tech on', ' the Net') =  'Tech on the Net';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat('a', 'b') =  'ab';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat('a', NULL) = 'a';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat(NULL, 'b') = 'b';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat('a', 2) = 'a2';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat(1, 'b') = '1b';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat(1, 2) = '12';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat(1, NULL) = '1';
+ ?column? 
+----------
+ t
+(1 row)
+
+select concat(NULL, 2) = '2';
+ ?column? 
+----------
+ t
+(1 row)
+
+select nvl('A'::text, 'B');
+ nvl 
+-----
+ A
+(1 row)
+
+select nvl(NULL::text, 'B');
+ nvl 
+-----
+ B
+(1 row)
+
+select nvl(NULL::text, NULL);
+ nvl 
+-----
+ 
+(1 row)
+
+select nvl(1, 2);
+ nvl 
+-----
+   1
+(1 row)
+
+select nvl(NULL, 2);
+ nvl 
+-----
+   2
+(1 row)
+
+select nvl2('A'::text, 'B', 'C');
+ nvl2 
+------
+ B
+(1 row)
+
+select nvl2(NULL::text, 'B', 'C');
+ nvl2 
+------
+ C
+(1 row)
+
+select nvl2('A'::text, NULL, 'C');
+ nvl2 
+------
+ 
+(1 row)
+
+select nvl2(NULL::text, 'B', NULL);
+ nvl2 
+------
+ 
+(1 row)
+
+select nvl2(1, 2, 3);
+ nvl2 
+------
+    2
+(1 row)
+
+select nvl2(NULL, 2, 3);
+ nvl2 
+------
+    3
+(1 row)
+
+select lnnvl(true);
+ lnnvl 
+-------
+ f
+(1 row)
+
+select lnnvl(false);
+ lnnvl 
+-------
+ t
+(1 row)
+
+select lnnvl(NULL);
+ lnnvl 
+-------
+ t
+(1 row)
+
+select decode(1, 1, 100, 2, 200);
+ decode 
+--------
+    100
+(1 row)
+
+select decode(2, 1, 100, 2, 200);
+ decode 
+--------
+    200
+(1 row)
+
+select decode(3, 1, 100, 2, 200);
+ decode 
+--------
+       
+(1 row)
+
+select decode(3, 1, 100, 2, 200, 300);
+ decode 
+--------
+    300
+(1 row)
+
+select decode(NULL, 1, 100, NULL, 200, 300);
+ decode 
+--------
+    200
+(1 row)
+
+select decode('1'::text, '1', 100, '2', 200);
+ decode 
+--------
+    100
+(1 row)
+
+select decode(2, 1, 'ABC', 2, 'DEF');
+ decode 
+--------
+ DEF
+(1 row)
+
+select decode('2009-02-05'::date, '2009-02-05', 'ok');
+ decode 
+--------
+ ok
+(1 row)
+
+select decode('2009-02-05 01:02:03'::timestamp, '2009-02-05 01:02:03', 'ok');
+ decode 
+--------
+ ok
+(1 row)
+
+select PLVstr.rvrs ('Jumping Jack Flash') ='hsalF kcaJ gnipmuJ';
+ ?column? 
+----------
+ t
+(1 row)
+
+select PLVstr.rvrs ('Jumping Jack Flash', 9) = 'hsalF kcaJ';
+ ?column? 
+----------
+ t
+(1 row)
+
+select PLVstr.rvrs ('Jumping Jack Flash', 4, 6) = 'nip';
+ ?column? 
+----------
+ t
+(1 row)
+
+select PLVstr.lstrip ('*val1|val2|val3|*', '*') = 'val1|val2|val3|*';
+ ?column? 
+----------
+ t
+(1 row)
+
+select PLVstr.lstrip (',,,val1,val2,val3,', ',', 3)= 'val1,val2,val3,';
+ ?column? 
+----------
+ t
+(1 row)
+
+select PLVstr.lstrip ('WHERE WHITE = ''FRONT'' AND COMP# = 1500', 'WHERE ') = 'WHITE = ''FRONT'' AND COMP# = 1500';
+ ?column? 
+----------
+ t
+(1 row)
+
+select plvstr.left('Příliš žluťoučký kůň',4) = pg_catalog.substr('Příl', 1, 4);
+ ?column? 
+----------
+ t
+(1 row)
+
+select pos,token from plvlex.tokens('select * from a.b.c join d ON x=y', true, true);
+ pos | token  
+-----+--------
+   0 | select
+   7 | *
+   9 | from
+  14 | a.b.c
+  20 | join
+  25 | d
+  27 | on
+  30 | x
+  31 | =
+  32 | y
+(10 rows)
+
+SET lc_numeric TO 'C';
+select to_char(22);
+ to_char 
+---------
+ 22
+(1 row)
+
+select to_char(-44444);
+ to_char 
+---------
+ -44444
+(1 row)
+
+select to_char(1234567890123456::bigint);
+     to_char      
+------------------
+ 1234567890123456
+(1 row)
+
+select to_char(123.456::real);
+  to_char   
+------------
+ 123.456001
+(1 row)
+
+select to_char(1234.5678::double precision);
+   to_char   
+-------------
+ 1234.567800
+(1 row)
+
+select to_char(12345678901234567890::numeric);
+       to_char        
+----------------------
+ 12345678901234567890
+(1 row)
+
+select to_char(1234567890.12345);
+     to_char      
+------------------
+ 1234567890.12345
+(1 row)
+
+SELECT to_number('123'::text);
+ to_number 
+-----------
+       123
+(1 row)
+
+SELECT to_number('123.456'::text);
+ to_number 
+-----------
+   123.456
+(1 row)
+
+SELECT to_date('2009-01-02');
+  to_date   
+------------
+ 2009-01-02
+(1 row)
+
+SELECT bitand(5,1), bitand(5,2), bitand(5,4);
+ bitand | bitand | bitand 
+--------+--------+--------
+      1 |      0 |      4
+(1 row)
+
+SELECT sinh(1.570796), cosh(1.570796), tanh(4);
+      sinh       |       cosh       |       tanh        
+-----------------+------------------+-------------------
+ 2.3012980823207 | 2.50917772660545 | 0.999329299739067
+(1 row)
+
+SELECT nanvl(12345, 1), nanvl('NaN', 1);
+ nanvl | nanvl 
+-------+-------
+ 12345 |     1
+(1 row)
+
+SELECT nanvl(12345::float4, 1), nanvl('NaN'::float4, 1);
+ nanvl | nanvl 
+-------+-------
+ 12345 |     1
+(1 row)
+
+SELECT nanvl(12345::float8, 1), nanvl('NaN'::float8, 1);
+ nanvl | nanvl 
+-------+-------
+ 12345 |     1
+(1 row)
+
+SELECT nanvl(12345::numeric, 1), nanvl('NaN'::numeric, 1);
+ nanvl | nanvl 
+-------+-------
+ 12345 |     1
+(1 row)
+
+select dbms_assert.enquote_literal('some text '' some text');
+     enquote_literal      
+--------------------------
+ 'some text '' some text'
+(1 row)
+
+select dbms_assert.enquote_name('''"AAA');
+ enquote_name 
+--------------
+ "'""aaa"
+(1 row)
+
+select dbms_assert.enquote_name('''"AAA', false);
+ enquote_name 
+--------------
+ "'""AAA"
+(1 row)
+
+select dbms_assert.noop('some string');
+    noop     
+-------------
+ some string
+(1 row)
+
+select dbms_assert.qualified_sql_name('aaa.bbb.ccc."aaaa""aaa"');
+   qualified_sql_name    
+-------------------------
+ aaa.bbb.ccc."aaaa""aaa"
+(1 row)
+
+select dbms_assert.qualified_sql_name('aaa.bbb.cc%c."aaaa""aaa"');
+ERROR:  string is not qualified SQL name
+select dbms_assert.schema_name('dbms_assert');
+ schema_name 
+-------------
+ dbms_assert
+(1 row)
+
+select dbms_assert.schema_name('jabadabado');
+ERROR:  invalid schema name
+select dbms_assert.simple_sql_name('"Aaa dghh shsh"');
+ simple_sql_name 
+-----------------
+ "Aaa dghh shsh"
+(1 row)
+
+select dbms_assert.simple_sql_name('ajajaj -- ajaj');
+ERROR:  string is not simple SQL name
+select dbms_assert.object_name('pg_catalog.pg_class');
+     object_name     
+---------------------
+ pg_catalog.pg_class
+(1 row)
+
+select dbms_assert.object_name('dbms_assert.fooo');
+ERROR:  invalid object name
+select plunit.assert_true(NULL);
+ERROR:  plunit.assert_true exception
+DETAIL:  Plunit.assertation fails (assert_true).
+select plunit.assert_true(1 = 2);
+ERROR:  plunit.assert_true exception
+DETAIL:  Plunit.assertation fails (assert_true).
+select plunit.assert_true(1 = 2, 'one is not two');
+ERROR:  one is not two
+DETAIL:  Plunit.assertation fails (assert_true).
+select plunit.assert_true(1 = 1);
+ assert_true 
+-------------
+ 
+(1 row)
+
+select plunit.assert_false(1 = 1);
+ERROR:  plunit.assert_false exception
+DETAIL:  Plunit.assertation fails (assert_false).
+select plunit.assert_false(1 = 1, 'trap is open');
+ERROR:  trap is open
+DETAIL:  Plunit.assertation fails (assert_false).
+select plunit.assert_false(NULL);
+ERROR:  plunit.assert_false exception
+DETAIL:  Plunit.assertation fails (assert_false).
+select plunit.assert_null(current_date);
+ERROR:  plunit.assert_null exception
+DETAIL:  Plunit.assertation fails (assert_null).
+select plunit.assert_null(NULL::date);
+ assert_null 
+-------------
+ 
+(1 row)
+
+select plunit.assert_not_null(current_date);
+ assert_not_null 
+-----------------
+ 
+(1 row)
+
+select plunit.assert_not_null(NULL::date);
+ERROR:  plunit.assert_not_null exception
+DETAIL:  Plunit.assertation fails (assert_not_null).
+select plunit.assert_equals('Pavel','Pa'||'vel');
+ assert_equals 
+---------------
+ 
+(1 row)
+
+select plunit.assert_equals(current_date, current_date + 1, 'diff dates');
+ERROR:  diff dates
+DETAIL:  Plunit.assertation fails (assert_equals).
+select plunit.assert_equals(10.2, 10.3, 0.5);
+ assert_equals 
+---------------
+ 
+(1 row)
+
+select plunit.assert_equals(10.2, 10.3, 0.01, 'attention some diff');
+ERROR:  attention some diff
+DETAIL:  Plunit.assertation fails (assert_equals).
+select plunit.assert_not_equals(current_date, current_date + 1, 'yestarday is today');
+ assert_not_equals 
+-------------------
+ 
+(1 row)
+
+select plunit.fail();
+ERROR:  plunit.assert_fail exception
+DETAIL:  Plunit.assertation (assert_fail).
+select plunit.fail('custom exception');
+ERROR:  custom exception
+DETAIL:  Plunit.assertation (assert_fail).
+SELECT dump('Yellow dog'::text) ~ E'^Typ=25 Len=(\\d+): \\d+(,\\d+)*$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump('Yellow dog'::text, 10) ~ E'^Typ=25 Len=(\\d+): \\d+(,\\d+)*$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump('Yellow dog'::text, 17) ~ E'^Typ=25 Len=(\\d+): .(,.)*$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump(10::int2) ~ E'^Typ=21 Len=2: \\d+(,\\d+){1}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump(10::int4) ~ E'^Typ=23 Len=4: \\d+(,\\d+){3}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump(10::int8) ~ E'^Typ=20 Len=8: \\d+(,\\d+){7}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump(10.23::float4) ~ E'^Typ=700 Len=4: \\d+(,\\d+){3}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump(10.23::float8) ~ E'^Typ=701 Len=8: \\d+(,\\d+){7}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump(10.23::numeric) ~ E'^Typ=1700 Len=(\\d+): \\d+(,\\d+)*$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump('2008-10-10'::date) ~ E'^Typ=1082 Len=4: \\d+(,\\d+){3}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+SELECT dump('2008-10-10'::timestamp) ~ E'^Typ=1114 Len=8: \\d+(,\\d+){7}$' AS t;
+ t 
+---
+ t
+(1 row)
+
+select listagg(i::text) from generate_series(1,3) g(i);
+ listagg 
+---------
+ 123
+(1 row)
+
+select listagg(i::text, ',') from generate_series(1,3) g(i);
+ listagg 
+---------
+ 1,2,3
+(1 row)
+
+select coalesce(listagg(i::text), '<NULL>') from (SELECT ''::text) g(i);
+ coalesce 
+----------
+ 
+(1 row)
+
+select coalesce(listagg(i::text), '<NULL>') from generate_series(1,0) g(i);
+ coalesce 
+----------
+ <NULL>
+(1 row)
+

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/file.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/file.c b/contrib/orafce/file.c
new file mode 100644
index 0000000..4455730
--- /dev/null
+++ b/contrib/orafce/file.c
@@ -0,0 +1,1083 @@
+#include "postgres.h"
+
+#include <unistd.h>
+#include <sys/stat.h>
+
+#include "executor/spi.h"
+
+#include "catalog/pg_type.h"
+#include "fmgr.h"
+#include "funcapi.h"
+#include "mb/pg_wchar.h"
+#include "miscadmin.h"
+#include "port.h"
+#include "storage/fd.h"
+#include "utils/builtins.h"
+#include "utils/memutils.h"
+#include "orafunc.h"
+#include "builtins.h"
+
+#ifndef ERRCODE_NO_DATA_FOUND
+#define ERRCODE_NO_DATA_FOUND				MAKE_SQLSTATE('P','0', '0','0','2')
+#endif
+
+#define INVALID_OPERATION		"UTL_FILE_INVALID_OPERATION"
+#define WRITE_ERROR				"UTL_FILE_WRITE_ERROR"
+#define READ_ERROR				"UTL_FILE_READ_ERROR"
+#define INVALID_FILEHANDLE		"UTL_FILE_INVALID_FILEHANDLE"
+#define INVALID_MAXLINESIZE		"UTL_FILE_INVALID_MAXLINESIZE"
+#define INVALID_MODE			"UTL_FILE_INVALID_MODE"
+#define	INVALID_PATH			"UTL_FILE_INVALID_PATH"
+#define VALUE_ERROR				"UTL_FILE_VALUE_ERROR"
+
+PG_FUNCTION_INFO_V1(utl_file_fopen);
+PG_FUNCTION_INFO_V1(utl_file_is_open);
+PG_FUNCTION_INFO_V1(utl_file_get_line);
+PG_FUNCTION_INFO_V1(utl_file_get_nextline);
+PG_FUNCTION_INFO_V1(utl_file_put);
+PG_FUNCTION_INFO_V1(utl_file_put_line);
+PG_FUNCTION_INFO_V1(utl_file_new_line);
+PG_FUNCTION_INFO_V1(utl_file_putf);
+PG_FUNCTION_INFO_V1(utl_file_fflush);
+PG_FUNCTION_INFO_V1(utl_file_fclose);
+PG_FUNCTION_INFO_V1(utl_file_fclose_all);
+PG_FUNCTION_INFO_V1(utl_file_fremove);
+PG_FUNCTION_INFO_V1(utl_file_frename);
+PG_FUNCTION_INFO_V1(utl_file_fcopy);
+PG_FUNCTION_INFO_V1(utl_file_fgetattr);
+PG_FUNCTION_INFO_V1(utl_file_tmpdir);
+
+#define CUSTOM_EXCEPTION(msg, detail) \
+	ereport(ERROR, \
+		(errcode(ERRCODE_RAISE_EXCEPTION), \
+		 errmsg("%s",msg), \
+		 errdetail("%s",detail)))
+
+#define INVALID_FILEHANDLE_EXCEPTION()	CUSTOM_EXCEPTION(INVALID_FILEHANDLE, "Used file handle isn't valid.")
+
+#define CHECK_FILE_HANDLE() \
+	if (PG_ARGISNULL(0)) \
+		CUSTOM_EXCEPTION(INVALID_FILEHANDLE, "Used file handle isn't valid.")
+
+#define NON_EMPTY_TEXT(dat) \
+	if (VARSIZE(dat) - VARHDRSZ == 0) \
+		ereport(ERROR, \
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE), \
+			 errmsg("invalid parameter"), \
+			 errdetail("Empty string isn't allowed.")));
+
+#define NOT_NULL_ARG(n) \
+	if (PG_ARGISNULL(n)) \
+		ereport(ERROR, \
+			(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), \
+			 errmsg("null value not allowed"), \
+			 errhint("%dth argument is NULL.", n)));
+
+#define MAX_LINESIZE		32767
+
+#define CHECK_LINESIZE(max_linesize) \
+	do { \
+		if ((max_linesize) < 1 || (max_linesize) > MAX_LINESIZE) \
+			CUSTOM_EXCEPTION(INVALID_MAXLINESIZE, "maxlinesize is out of range"); \
+	} while(0)
+
+typedef struct FileSlot
+{
+	FILE   *file;
+	int		max_linesize;
+	int		encoding;
+	int32	id;
+} FileSlot;
+
+#define MAX_SLOTS		50			/* Oracle 10g supports 50 files */
+#define INVALID_SLOTID	0			/* invalid slot id */
+
+static FileSlot	slots[MAX_SLOTS];	/* initilaized with zeros */
+static int32	slotid = 0;			/* next slot id */
+
+static void check_secure_locality(const char *path);
+static char *get_safe_path(text *location, text *filename);
+static int copy_text_file(FILE *srcfile, FILE *dstfile,
+						  int start_line, int end_line);
+
+/*
+ * get_descriptor(FILE *file) find any free slot for FILE pointer.
+ * If isn't realloc array slots and add 32 new free slots.
+ *
+ */
+static int
+get_descriptor(FILE *file, int max_linesize, int encoding)
+{
+	int i;
+
+	for (i = 0; i < MAX_SLOTS; i++)
+	{
+		if (slots[i].id == INVALID_SLOTID)
+		{
+			slots[i].id = ++slotid;
+			if (slots[i].id == INVALID_SLOTID)
+				slots[i].id = ++slotid;	/* skip INVALID_SLOTID */
+			slots[i].file = file;
+			slots[i].max_linesize = max_linesize;
+			slots[i].encoding = encoding;
+			return slots[i].id;
+		}
+	}
+
+	return INVALID_SLOTID;
+}
+
+/* return stored pointer to FILE */
+static FILE *
+get_stream(int d, int *max_linesize, int *encoding)
+{
+	int i;
+
+	if (d == INVALID_SLOTID)
+		INVALID_FILEHANDLE_EXCEPTION();
+
+	for (i = 0; i < MAX_SLOTS; i++)
+	{
+		if (slots[i].id == d)
+		{
+			if (max_linesize)
+				*max_linesize = slots[i].max_linesize;
+			if (encoding)
+				*encoding = slots[i].encoding;
+			return slots[i].file;
+		}
+	}
+
+	INVALID_FILEHANDLE_EXCEPTION();
+	return NULL;	/* keep compiler quiet */
+}
+
+static void
+IO_EXCEPTION(void)
+{
+	switch (errno)
+	{
+		case EACCES:
+		case ENAMETOOLONG:
+		case ENOENT:
+		case ENOTDIR:
+			CUSTOM_EXCEPTION(INVALID_PATH, strerror(errno));
+			break;
+
+		default:
+			CUSTOM_EXCEPTION(INVALID_OPERATION, strerror(errno));
+	}
+}
+
+/*
+ * FUNCTION UTL_FILE.FOPEN(location text,
+ *			   filename text,
+ *			   open_mode text,
+ *			   max_linesize integer)
+ *          RETURNS UTL_FILE.FILE_TYPE;
+ *
+ * The FOPEN function opens specified file and returns file handle.
+ *  open_mode: ['R', 'W', 'A']
+ *  max_linesize: [1 .. 32767]
+ *
+ * Exceptions:
+ *  INVALID_MODE, INVALID_OPERATION, INVALID_PATH, INVALID_MAXLINESIZE
+ */
+Datum
+utl_file_fopen(PG_FUNCTION_ARGS)
+{
+	text	   *open_mode;
+	int			max_linesize;
+	int			encoding;
+	const char *mode = NULL;
+	FILE	   *file;
+	char	   *fullname;
+	int			d;
+
+	NOT_NULL_ARG(0);
+	NOT_NULL_ARG(1);
+	NOT_NULL_ARG(2);
+	NOT_NULL_ARG(3);
+
+	open_mode = PG_GETARG_TEXT_P(2);
+
+	NON_EMPTY_TEXT(open_mode);
+
+	max_linesize = PG_GETARG_INT32(3);
+	CHECK_LINESIZE(max_linesize);
+
+	if (PG_NARGS() > 4 && !PG_ARGISNULL(4))
+	{
+		const char *encname = NameStr(*PG_GETARG_NAME(4));
+		encoding = pg_char_to_encoding(encname);
+		if (encoding < 0)
+			ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("invalid encoding name \"%s\"", encname)));
+	}
+	else
+		encoding = GetDatabaseEncoding();
+
+	if (VARSIZE(open_mode) - VARHDRSZ != 1)
+		CUSTOM_EXCEPTION(INVALID_MODE, "open mode is different than [R,W,A]");
+
+	switch (*((char*)VARDATA(open_mode)))
+	{
+		case 'a':
+		case 'A':
+			mode = "a";
+			break;
+
+		case 'r':
+		case 'R':
+			mode = "r";
+			break;
+
+		case 'w':
+		case 'W':
+			mode = "w";
+			break;
+
+		default:
+			CUSTOM_EXCEPTION(INVALID_MODE, "open mode is different than [R,W,A]");
+	}
+
+	/* open file */
+	fullname = get_safe_path(PG_GETARG_TEXT_P(0), PG_GETARG_TEXT_P(1));
+
+	/*
+	 * We cannot use AllocateFile here because those files are automatically
+	 * closed at the end of (sub)transactions, but we want to keep them open
+	 * for oracle compatibility.
+	 */
+#if NOT_USED
+	fullname = convert_encoding_server_to_platform(fullname);
+#endif
+	file = fopen(fullname, mode);
+	if (!file)
+		IO_EXCEPTION();
+
+	d = get_descriptor(file, max_linesize, encoding);
+	if (d == INVALID_SLOTID)
+	{
+		fclose(file);
+		ereport(ERROR,
+		    (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+		     errmsg("program limit exceeded"),
+		     errdetail("Too much concurent opened files"),
+		     errhint("You can only open a maximum of ten files for each session")));
+	}
+
+	PG_RETURN_INT32(d);
+}
+
+Datum
+utl_file_is_open(PG_FUNCTION_ARGS)
+{
+	if (!PG_ARGISNULL(0))
+	{
+		int	i;
+		int	d = PG_GETARG_INT32(0);
+
+		for (i = 0; i < MAX_SLOTS; i++)
+		{
+			if (slots[i].id == d)
+				PG_RETURN_BOOL(slots[i].file != NULL);
+		}
+	}
+
+	PG_RETURN_BOOL(false);
+}
+
+#define CHECK_LENGTH(l) \
+	if (l > max_linesize) \
+		CUSTOM_EXCEPTION(VALUE_ERROR, "buffer is too short");
+
+/* read line from file. set eof if is EOF */
+
+static text *
+get_line(FILE *f, int max_linesize, int encoding, bool *iseof)
+{
+	int c;
+	char *buffer = NULL;
+	char *bpt;
+	int csize = 0;
+	text *result = NULL;
+
+	bool eof = true;
+
+	buffer = palloc(max_linesize + 2);
+	bpt = buffer;
+
+	errno = 0;
+
+	while (csize < max_linesize && (c = fgetc(f)) != EOF)
+	{
+		eof = false; 	/* I was able read one char */
+
+		if (c == '\r')  /* lookin ahead \n */
+		{
+			c = fgetc(f);
+			if (c == EOF)
+				break;  /* last char */
+
+			if (c != '\n')
+				ungetc(c, f);
+			/* skip \r\n */
+			break;
+		}
+		else if (c == '\n')
+			break;
+
+		++csize;
+		*bpt++ = c;
+	}
+
+	if (!eof)
+	{
+		char   *decoded;
+		int		len;
+
+		pg_verify_mbstr(encoding, buffer, csize, false);
+		decoded = (char *) pg_do_encoding_conversion((unsigned char *) buffer,
+									 csize, encoding, GetDatabaseEncoding());
+		len = (decoded == buffer ? csize : strlen(decoded));
+		result = palloc(len + VARHDRSZ);
+		memcpy(VARDATA(result), decoded, len);
+		SET_VARSIZE(result, len + VARHDRSZ);
+		if (decoded != buffer)
+			pfree(decoded);
+		*iseof = false;
+	}
+	else
+	{
+		switch (errno)
+		{
+			case 0:
+				break;
+
+			case EBADF:
+				CUSTOM_EXCEPTION(INVALID_OPERATION, "file descriptor isn't valid for reading");
+				break;
+
+			default:
+				CUSTOM_EXCEPTION(READ_ERROR, strerror(errno));
+				break;
+		}
+
+		*iseof = true;
+	}
+
+	pfree(buffer);
+	return result;
+}
+
+
+/*
+ * FUNCTION UTL_FILE.GET_LINE(file UTL_TYPE.FILE_TYPE, line int DEFAULT NULL)
+ *          RETURNS text;
+ *
+ * Reads one line from file.
+ *
+ * Exceptions:
+ *  NO_DATA_FOUND, INVALID_FILEHANDLE, INVALID_OPERATION, READ_ERROR
+ */
+Datum
+utl_file_get_line(PG_FUNCTION_ARGS)
+{
+	int		max_linesize = 0;
+	int		encoding = 0;
+	FILE   *f;
+	text   *result;
+	bool	iseof;
+
+	CHECK_FILE_HANDLE();
+	f = get_stream(PG_GETARG_INT32(0), &max_linesize, &encoding);
+
+	/* 'len' overwrites max_linesize, but must be smaller than max_linesize */
+	if (PG_NARGS() > 1 && !PG_ARGISNULL(1))
+	{
+		int	len = PG_GETARG_INT32(1);
+		CHECK_LINESIZE(len);
+		if (max_linesize > len)
+			max_linesize = len;
+	}
+
+	result = get_line(f, max_linesize, encoding, &iseof);
+
+	if (iseof)
+	    	ereport(ERROR,
+				(errcode(ERRCODE_NO_DATA_FOUND),
+		    		 errmsg("no data found")));
+
+	PG_RETURN_TEXT_P(result);
+}
+
+
+/*
+ * FUNCTION UTL_FILE.GET_NEXTLINE(file UTL_TYPE.FILE_TYPE)
+ *          RETURNS text;
+ *
+ * Reads one line from file or retutns NULL
+ * by Steven Feuerstein.
+ *
+ * Exceptions:
+ *  INVALID_FILEHANDLE, INVALID_OPERATION, READ_ERROR
+ */
+Datum
+utl_file_get_nextline(PG_FUNCTION_ARGS)
+{
+	int		max_linesize = 0;
+	int		encoding = 0;
+	FILE   *f;
+	text   *result;
+	bool	iseof;
+
+	CHECK_FILE_HANDLE();
+	f = get_stream(PG_GETARG_INT32(0), &max_linesize, &encoding);
+
+	result = get_line(f, max_linesize, encoding, &iseof);
+
+	if (iseof)
+		PG_RETURN_NULL();
+
+	PG_RETURN_TEXT_P(result);
+}
+
+static void
+do_flush(FILE *f)
+{
+	if (fflush(f) != 0)
+	{
+		if (errno == EBADF)
+			CUSTOM_EXCEPTION(INVALID_OPERATION, "File is not an opened, or is not open for writing");
+		else
+			CUSTOM_EXCEPTION(WRITE_ERROR, strerror(errno));
+	}
+}
+
+/*
+ * FUNCTION UTL_FILE.PUT(file UTL_FILE.FILE_TYPE, buffer text)
+ *          RETURNS bool;
+ *
+ * The PUT function puts data out to specified file. Buffer length allowed is
+ * 32K or 1024 (max_linesize);
+ *
+ * Exceptions:
+ *  INVALID_FILEHANDLE, INVALID_OPERATION, WRITE_ERROR, VALUE_ERROR
+ *
+ * Note: returns bool because I cannot do envelope over void function
+ */
+
+#define CHECK_ERRNO_PUT()  \
+	switch (errno) \
+	{ \
+		case EBADF: \
+			CUSTOM_EXCEPTION(INVALID_OPERATION, "file descriptor isn't valid for writing"); \
+			break; \
+		default: \
+			CUSTOM_EXCEPTION(WRITE_ERROR, strerror(errno)); \
+	}
+
+/* encode(t, encoding) */
+static char *
+encode_text(int encoding, text *t, int *length)
+{
+	char	   *src = VARDATA_ANY(t);
+	char	   *encoded;
+
+	encoded = (char *) pg_do_encoding_conversion((unsigned char *) src,
+					VARSIZE_ANY_EXHDR(t), GetDatabaseEncoding(), encoding);
+
+	*length = (src == encoded ? VARSIZE_ANY_EXHDR(t) : strlen(encoded));
+	return encoded;
+}
+
+/* fwrite(encode(args[n], encoding), f) */
+static int
+do_write(PG_FUNCTION_ARGS, int n, FILE *f, int max_linesize, int encoding)
+{
+	text	   *arg = PG_GETARG_TEXT_P(n);
+	char	   *str;
+	int			len;
+
+	str = encode_text(encoding, arg, &len);
+	CHECK_LENGTH(len);
+
+	if (fwrite(str, 1, len, f) != len)
+		CHECK_ERRNO_PUT();
+
+	if (VARDATA(arg) != str)
+		pfree(str);
+	PG_FREE_IF_COPY(arg, n);
+
+	return len;
+}
+
+static FILE *
+do_put(PG_FUNCTION_ARGS)
+{
+	FILE   *f;
+	int		max_linesize = 0;
+	int		encoding = 0;
+
+	CHECK_FILE_HANDLE();
+	f = get_stream(PG_GETARG_INT32(0), &max_linesize, &encoding);
+
+	NOT_NULL_ARG(1);
+	do_write(fcinfo, 1, f, max_linesize, encoding);
+	return f;
+}
+
+Datum
+utl_file_put(PG_FUNCTION_ARGS)
+{
+	do_put(fcinfo);
+	PG_RETURN_BOOL(true);
+}
+
+static void
+do_new_line(FILE *f, int lines)
+{
+	int	i;
+	for (i = 0; i < lines; i++)
+	{
+#ifndef WIN32
+		if (fputc('\n', f) == EOF)
+		    CHECK_ERRNO_PUT();
+#else
+		if (fputs("\r\n", f) == EOF)
+		    CHECK_ERRNO_PUT();
+#endif
+	}
+}
+
+Datum
+utl_file_put_line(PG_FUNCTION_ARGS)
+{
+	FILE   *f;
+	bool	autoflush;
+
+	f = do_put(fcinfo);
+
+	autoflush = PG_GETARG_IF_EXISTS(2, BOOL, false);
+
+	do_new_line(f, 1);
+
+	if (autoflush)
+		do_flush(f);
+
+	PG_RETURN_BOOL(true);
+}
+
+Datum
+utl_file_new_line(PG_FUNCTION_ARGS)
+{
+	FILE   *f;
+	int		lines;
+
+	CHECK_FILE_HANDLE();
+	f = get_stream(PG_GETARG_INT32(0), NULL, NULL);
+	lines = PG_GETARG_IF_EXISTS(1, INT32, 1);
+
+	do_new_line(f, lines);
+
+	PG_RETURN_BOOL(true);
+}
+
+/*
+ * FUNCTION UTL_FILE.PUTF(file UTL_FILE.FILE_TYPE,
+ *			format text,
+ *			arg1 text,
+ *			arg2 text,
+ *			arg3 text,
+ *			arg4 text,
+ *			arg5 text)
+ *	    RETURNS bool;
+ *
+ * Puts formated data to file. Allows %s like subst symbol.
+ *
+ * Exception:
+ *  INVALID_FILEHANDLE, INVALID_OPERATION, WRITE_ERROR
+ */
+Datum
+utl_file_putf(PG_FUNCTION_ARGS)
+{
+	FILE   *f;
+	char   *format;
+	int		max_linesize = 0;
+	int		encoding = 0;
+	int		format_length;
+	char   *fpt;
+	int		cur_par = 0;
+	int		cur_len = 0;
+
+	CHECK_FILE_HANDLE();
+	f = get_stream(PG_GETARG_INT32(0), &max_linesize, &encoding);
+
+	NOT_NULL_ARG(1);
+	format = encode_text(encoding, PG_GETARG_TEXT_P(1), &format_length);
+
+	for (fpt = format; format_length > 0; fpt++, format_length--)
+	{
+		if (format_length == 1)
+		{
+			/* last char */
+			CHECK_LENGTH(++cur_len);
+			if (fputc(*fpt, f) == EOF)
+				CHECK_ERRNO_PUT();
+			continue;
+		}
+		/* ansi compatible string */
+		if (fpt[0] == '\\' && fpt[1] == 'n')
+		{
+			CHECK_LENGTH(++cur_len);
+			if (fputc('\n', f) == EOF)
+				CHECK_ERRNO_PUT();
+			fpt++; format_length--;
+			continue;
+		}
+		if (fpt[0] == '%')
+		{
+			if (fpt[1] == '%')
+			{
+				CHECK_LENGTH(++cur_len);
+				if (fputc('%', f) == EOF)
+					CHECK_ERRNO_PUT();
+			}
+			else if (fpt[1] == 's' && ++cur_par <= 5 && !PG_ARGISNULL(cur_par + 1))
+			{
+				cur_len += do_write(fcinfo, cur_par + 1, f, max_linesize - cur_len, encoding);
+			}
+			fpt++; format_length--;
+			continue;
+		}
+		CHECK_LENGTH(++cur_len);
+		if (fputc(fpt[0], f) == EOF)
+			CHECK_ERRNO_PUT();
+	}
+
+	PG_RETURN_BOOL(true);
+}
+
+
+/*
+ * FUNCTION UTL_FILE.FFLUSH(file UTL_FILE.FILE_TYPE)
+ *          RETURNS void;
+ *
+ * This function makes sure that all pending data for the specified file is written
+ * physically out to file.
+ *
+ * Exceptions:
+ *  INVALID_FILEHANDLE, INVALID_OPERATION, WRITE_ERROR
+ */
+Datum
+utl_file_fflush(PG_FUNCTION_ARGS)
+{
+	FILE *f;
+
+	CHECK_FILE_HANDLE();
+	f = get_stream(PG_GETARG_INT32(0), NULL, NULL);
+	do_flush(f);
+
+	PG_RETURN_VOID();
+}
+
+
+/*
+ * FUNCTION UTL_FILE.FCLOSE(file UTL_FILE.FILE_TYPE)
+ *          RETURNS NULL
+ *
+ * Close an open file. This function reset file handle to NULL on Oracle platform.
+ * It isn't possible in PostgreSQL, and then you have to call fclose function
+ * like:
+ *        file := utl_file.fclose(file);
+ *
+ * Exception:
+ *  INVALID_FILEHANDLE, WRITE_ERROR
+ */
+Datum
+utl_file_fclose(PG_FUNCTION_ARGS)
+{
+	int i;
+	int	d = PG_GETARG_INT32(0);
+
+	for (i = 0; i < MAX_SLOTS; i++)
+	{
+		if (slots[i].id == d)
+		{
+			if (slots[i].file && fclose(slots[i].file) != 0)
+			{
+				if (errno == EBADF)
+					CUSTOM_EXCEPTION(INVALID_FILEHANDLE, "File is not an opened");
+				else
+					CUSTOM_EXCEPTION(WRITE_ERROR, strerror(errno));
+			}
+			slots[i].file = NULL;
+			slots[i].id = INVALID_SLOTID;
+			PG_RETURN_NULL();
+		}
+	}
+
+	INVALID_FILEHANDLE_EXCEPTION();
+	PG_RETURN_NULL();
+}
+
+
+/*
+ * FUNCTION UTL_FILE.FCLOSE_ALL()
+ *          RETURNS void
+ *
+ * Close all opened files.
+ *
+ * Exceptions: WRITE_ERROR
+ */
+Datum
+utl_file_fclose_all(PG_FUNCTION_ARGS)
+{
+	int i;
+
+	for (i = 0; i < MAX_SLOTS; i++)
+	{
+		if (slots[i].id != INVALID_SLOTID)
+		{
+			if (slots[i].file && fclose(slots[i].file) != 0)
+			{
+				if (errno == EBADF)
+					CUSTOM_EXCEPTION(INVALID_FILEHANDLE, "File is not an opened");
+				else
+					CUSTOM_EXCEPTION(WRITE_ERROR, strerror(errno));
+			}
+			slots[i].file = NULL;
+			slots[i].id = INVALID_SLOTID;
+		}
+	}
+
+	PG_RETURN_VOID();
+}
+
+
+/*
+ * utl_file_dir security .. is solved with aux. table.
+ *
+ * Raise exception if don't find string in table.
+ */
+static void
+check_secure_locality(const char *path)
+{
+	static SPIPlanPtr	plan = NULL;
+
+	Oid		argtypes[] = {TEXTOID};
+	Datum	values[1];
+	char	nulls[1] = {' '};
+
+	/* hack for availbility regress test */
+	if (strcmp(path, "/tmp/regress_orafce") == 0)
+		return;
+
+	values[0] = CStringGetTextDatum(path);
+
+	/*
+	 * SELECT 1 FROM utl_file.utl_file_dir
+	 *   WHERE substring($1, 1, length(dir) + 1) = dir || '/'
+	 */
+
+	if (SPI_connect() < 0)
+		ereport(ERROR,
+			(errcode(ERRCODE_INTERNAL_ERROR),
+			 errmsg("SPI_connect failed")));
+
+	if (!plan)
+	{
+		/* Don't use LIKE not to escape '_' and '%' */
+		SPIPlanPtr p = SPI_prepare(
+		    "SELECT 1 FROM utl_file.utl_file_dir"
+			" WHERE substring($1, 1, length(dir) + 1) = dir || '/'",
+		    1, argtypes);
+
+		if (p == NULL || (plan = SPI_saveplan(p)) == NULL)
+			ereport(ERROR,
+				(errcode(ERRCODE_INTERNAL_ERROR),
+				errmsg("SPI_prepare_failed")));
+	}
+
+	if (SPI_OK_SELECT != SPI_execute_plan(plan, values, nulls, false, 1))
+		ereport(ERROR,
+			(errcode(ERRCODE_INTERNAL_ERROR),
+			 errmsg("can't execute sql")));
+
+	if (SPI_processed == 0)
+		ereport(ERROR,
+			(errcode(ERRCODE_RAISE_EXCEPTION),
+			 errmsg(INVALID_PATH),
+			 errdetail("you cannot access locality"),
+			 errhint("locality is not found in utl_file_dir table")));
+	SPI_finish();
+}
+
+/*
+ * get_safe_path - make a fullpath and check security.
+ */
+static char *
+get_safe_path(text *location, text *filename)
+{
+	char   *fullname;
+	int		aux_pos;
+	int		aux_len;
+
+	NON_EMPTY_TEXT(location);
+	NON_EMPTY_TEXT(filename);
+
+	aux_pos = VARSIZE_ANY_EXHDR(location);
+	aux_len = VARSIZE_ANY_EXHDR(filename);
+
+	fullname = palloc(aux_pos + 1 + aux_len + 1);
+	memcpy(fullname, VARDATA(location), aux_pos);
+	fullname[aux_pos] = '/';
+	memcpy(fullname + aux_pos + 1, VARDATA(filename), aux_len);
+	fullname[aux_pos + aux_len + 1] = '\0';
+
+	/* check locality in canonizalized form of path */
+	canonicalize_path(fullname);
+	check_secure_locality(fullname);
+
+	return fullname;
+}
+
+/*
+ * CREATE FUNCTION utl_file.fremove(
+ *     location		text,
+ *     filename		text)
+ */
+Datum
+utl_file_fremove(PG_FUNCTION_ARGS)
+{
+	char	   *fullname;
+
+	NOT_NULL_ARG(0);
+	NOT_NULL_ARG(1);
+
+	fullname = get_safe_path(PG_GETARG_TEXT_P(0), PG_GETARG_TEXT_P(1));
+
+	if (unlink(fullname) != 0)
+		IO_EXCEPTION();
+
+	PG_RETURN_VOID();
+}
+
+/*
+ * CREATE FUNCTION utl_file.frename(
+ *     location		text,
+ *     filename		text,
+ *     dest_dir		text,
+ *     dest_file	text,
+ *     overwrite	boolean DEFAULT false)
+ */
+Datum
+utl_file_frename(PG_FUNCTION_ARGS)
+{
+	char	   *srcpath;
+	char	   *dstpath;
+	bool		overwrite;
+
+	NOT_NULL_ARG(0);
+	NOT_NULL_ARG(1);
+	NOT_NULL_ARG(2);
+	NOT_NULL_ARG(3);
+
+	overwrite = PG_GETARG_IF_EXISTS(4, BOOL, false);
+	srcpath = get_safe_path(PG_GETARG_TEXT_P(0), PG_GETARG_TEXT_P(1));
+	dstpath = get_safe_path(PG_GETARG_TEXT_P(2), PG_GETARG_TEXT_P(3));
+
+	if (!overwrite)
+	{
+		struct stat	st;
+		if (stat(dstpath, &st) == 0)
+			CUSTOM_EXCEPTION(WRITE_ERROR, "File exists");
+		else if (errno != ENOENT)
+			IO_EXCEPTION();
+	}
+
+	/* rename() overwrites existing files. */
+	if (rename(srcpath, dstpath) != 0)
+		IO_EXCEPTION();
+
+	PG_RETURN_VOID();
+}
+
+/*
+ * CREATE FUNCTION utl_file.fcopy(
+ *     src_location		text,
+ *     src_filename		text,
+ *     dest_location	text,
+ *     dest_filename	text,
+ *     start_line		integer DEFAULT NULL
+ *     end_line			integer DEFAULT NULL)
+ */
+Datum
+utl_file_fcopy(PG_FUNCTION_ARGS)
+{
+	char	   *srcpath;
+	char	   *dstpath;
+	int			start_line;
+	int			end_line;
+	FILE	   *srcfile;
+	FILE	   *dstfile;
+
+	NOT_NULL_ARG(0);
+	NOT_NULL_ARG(1);
+	NOT_NULL_ARG(2);
+	NOT_NULL_ARG(3);
+
+	srcpath = get_safe_path(PG_GETARG_TEXT_P(0), PG_GETARG_TEXT_P(1));
+	dstpath = get_safe_path(PG_GETARG_TEXT_P(2), PG_GETARG_TEXT_P(3));
+
+	start_line = PG_GETARG_IF_EXISTS(4, INT32, 1);
+	if (start_line <= 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("start_line must be positive (%d passed)", start_line)));
+
+	end_line = PG_GETARG_IF_EXISTS(5, INT32, INT_MAX);
+	if (end_line <= 0)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				errmsg("end_line must be positive (%d passed)", end_line)));
+
+	srcfile = AllocateFile(srcpath, "rt");
+	if (srcfile == NULL)
+	{
+		/* failed to open src file. */
+		IO_EXCEPTION();
+	}
+
+	dstfile = AllocateFile(dstpath, "wt");
+	if (dstfile == NULL)
+	{
+		/* failed to open dst file. */
+		fclose(srcfile);
+		IO_EXCEPTION();
+	}
+
+	if (copy_text_file(srcfile, dstfile, start_line, end_line))
+		IO_EXCEPTION();
+
+	FreeFile(srcfile);
+	FreeFile(dstfile);
+
+	PG_RETURN_VOID();
+}
+
+/*
+ * Copy srcfile to dstfile. Return 0 if succeeded, or non-0 if error.
+ */
+static int
+copy_text_file(FILE *srcfile, FILE *dstfile, int start_line, int end_line)
+{
+	char	buffer[MAX_LINESIZE];
+	size_t	len;
+	int		i;
+
+	errno = 0;
+	/* skip first start_line. */
+	for (i = 1; i < start_line; i++)
+	{
+		CHECK_FOR_INTERRUPTS();
+		do
+		{
+			if (fgets(buffer, lengthof(buffer), srcfile) == NULL)
+				return errno;
+			len = strlen(buffer);
+		} while(buffer[len - 1] != '\n');
+	}
+
+	/* copy until end_line. */
+	for (; i <= end_line; i++)
+	{
+		CHECK_FOR_INTERRUPTS();
+		do
+		{
+			if (fgets(buffer, lengthof(buffer), srcfile) == NULL)
+				return errno;
+			len = strlen(buffer);
+			if (fwrite(buffer, 1, len, dstfile) != len)
+				return errno;
+		} while(buffer[len - 1] != '\n');
+	}
+
+	return 0;
+}
+
+/*
+ * CREATE FUNCTION utl_file.fgetattr(
+ *     location		text,
+ *     filename		text
+ * ) RETURNS (
+ *     fexists		boolean,
+ *     file_length	bigint,
+ *     blocksize	integer)
+ */
+Datum
+utl_file_fgetattr(PG_FUNCTION_ARGS)
+{
+	char	   *fullname;
+	struct stat	st;
+	TupleDesc	tupdesc;
+	Datum		result;
+	HeapTuple	tuple;
+	Datum		values[3];
+	bool		nulls[3] = { 0 };
+
+	NOT_NULL_ARG(0);
+	NOT_NULL_ARG(1);
+
+	/* Build a tuple descriptor for our result type */
+	if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
+		elog(ERROR, "return type must be a row type");
+
+	fullname = get_safe_path(PG_GETARG_TEXT_P(0), PG_GETARG_TEXT_P(1));
+
+	if (stat(fullname, &st) == 0)
+	{
+		values[0] = BoolGetDatum(true);
+		values[1] = Int64GetDatum(st.st_size);
+#ifndef WIN32
+		values[2] = Int32GetDatum(st.st_blksize);
+#else
+		values[2] = 512;	/* NTFS block size */
+#endif
+	}
+	else
+	{
+		values[0] = BoolGetDatum(false);
+		nulls[1] = true;
+		nulls[2] = true;
+	}
+
+	tuple = heap_form_tuple(tupdesc, values, nulls);
+	result = HeapTupleGetDatum(tuple);
+
+	PG_RETURN_DATUM(result);
+}
+
+Datum
+utl_file_tmpdir(PG_FUNCTION_ARGS)
+{
+#ifndef WIN32
+	const char *tmpdir = getenv("TMPDIR");
+
+	if (!tmpdir)
+		tmpdir = "/tmp";
+#else
+	char		tmpdir[MAXPGPATH];
+	int			ret;
+
+	ret = GetTempPath(MAXPGPATH, tmpdir);
+	if (ret == 0 || ret > MAXPGPATH)
+		CUSTOM_EXCEPTION(INVALID_PATH, strerror(errno));
+
+	canonicalize_path(tmpdir);
+#endif
+
+	PG_RETURN_TEXT_P(cstring_to_text(tmpdir));
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/generate_hash.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/generate_hash.c b/contrib/orafce/generate_hash.c
new file mode 100644
index 0000000..e4fac1c
--- /dev/null
+++ b/contrib/orafce/generate_hash.c
@@ -0,0 +1,102 @@
+#include <stdio.h>
+#include <string.h>
+#include <limits.h>
+
+static char *days[] = {"sunday", "monday", "tuesday", "wednesday", "thursday", "friday", "saturday"};
+
+
+static char *
+num2day(int i)
+{
+	if (i > 6) return "";
+	return days[i];
+}
+
+#include "tdhfunc.c"
+
+int
+main(void)
+{
+	int i;
+	int hashes[7];
+
+	for (i = 0; i < 7; i++)
+	{
+		hashes[i] = tdhfunc(days[i]);
+	}
+
+	for (i = 7; i < INT_MAX; i++)
+	{
+		int j;
+		int collide = 0;
+
+		for (j = 0; j < 7; j++)
+		{
+			int k;
+
+			int h = hashes[j] % i;
+			//printf("h(%i) = %i\n",j,h);
+
+			for (k = 0; k < 7; k++)
+			{
+				if (j == k) continue;
+				if (h == (hashes[k] % i))
+				{
+					//printf("collide on %i = %i\n", j, k);
+					collide = 1;
+					break;
+				}
+			}
+			if (collide)
+				break;
+		}
+
+		if (!collide)
+		{
+			int m = 0;
+			int table[i];
+			
+			memset(table, -1, i * sizeof(int));
+
+			for (m = 0; m < 7; m++)
+			{
+				table[hashes[m] % i] = m;
+			}
+
+			printf("/* generated by %s. This makes tdhfunc() a perfect hash. */\n", __FILE__);
+			printf("typedef struct daybucket\n"
+				   "{\n"
+				   "	int8 dow; /* the day of the week, starting at zero for Sunday */\n"
+				   "	uint8 daylen; /* length of the field below, cached */\n"
+				   "	char *day; /* the day name */\n"
+				   "} daybucket;\n");
+			printf("#define TDH_SIZE %i\n", i);
+			printf("static const daybucket TDH[TDH_SIZE] = {");
+
+			for (m = 0; m < i; m++)
+			{
+				if (m)
+					printf(", ");
+				if (m && m % 2 == 0)
+					printf("\n\t\t");
+			
+
+				printf("{%i, ", table[m]);
+
+				if (table[m] >= 0)
+				{
+					char *s = num2day(table[m]);
+
+					printf("%u, \"%s\"}", (unsigned int)strlen(s), s);
+				}
+				else
+					printf("0, \"\"}");
+
+				}
+			printf("};\n\n");
+			break;
+		}
+	}
+
+	return 0;
+}

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/magic.c
----------------------------------------------------------------------
diff --git a/contrib/orafce/magic.c b/contrib/orafce/magic.c
new file mode 100644
index 0000000..acc21a4
--- /dev/null
+++ b/contrib/orafce/magic.c
@@ -0,0 +1,6 @@
+#include "postgres.h"
+#include "fmgr.h"
+
+#ifdef PG_MODULE_MAGIC
+PG_MODULE_MAGIC;
+#endif

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/orafunc-8.1.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/orafunc-8.1.sql b/contrib/orafce/orafunc-8.1.sql
new file mode 100644
index 0000000..ed9a26b
--- /dev/null
+++ b/contrib/orafce/orafunc-8.1.sql
@@ -0,0 +1,13 @@
+CREATE FUNCTION pg_catalog.reverse(str text)
+RETURNS text
+AS $$ SELECT plvstr.rvrs($1,1,NULL);$$
+LANGUAGE SQL IMMUTABLE STRICT;
+COMMENT ON FUNCTION pg_catalog.reverse(text) IS 'Reverse string or part of string';
+
+CREATE FUNCTION concat(text, text)
+RETURNS text
+AS 'MODULE_PATHNAME','ora_concat'
+LANGUAGE C IMMUTABLE;
+COMMENT ON FUNCTION concat(text, text) IS 'Concat two strings';
+
+COMMIT;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/orafunc-8.2.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/orafunc-8.2.sql b/contrib/orafce/orafunc-8.2.sql
new file mode 100644
index 0000000..ed9a26b
--- /dev/null
+++ b/contrib/orafce/orafunc-8.2.sql
@@ -0,0 +1,13 @@
+CREATE FUNCTION pg_catalog.reverse(str text)
+RETURNS text
+AS $$ SELECT plvstr.rvrs($1,1,NULL);$$
+LANGUAGE SQL IMMUTABLE STRICT;
+COMMENT ON FUNCTION pg_catalog.reverse(text) IS 'Reverse string or part of string';
+
+CREATE FUNCTION concat(text, text)
+RETURNS text
+AS 'MODULE_PATHNAME','ora_concat'
+LANGUAGE C IMMUTABLE;
+COMMENT ON FUNCTION concat(text, text) IS 'Concat two strings';
+
+COMMIT;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/orafunc-8.3.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/orafunc-8.3.sql b/contrib/orafce/orafunc-8.3.sql
new file mode 100644
index 0000000..cfeee55
--- /dev/null
+++ b/contrib/orafce/orafunc-8.3.sql
@@ -0,0 +1,65 @@
+CREATE FUNCTION pg_catalog.reverse(str text)
+RETURNS text
+AS $$ SELECT plvstr.rvrs($1,1,NULL);$$
+LANGUAGE SQL IMMUTABLE STRICT;
+COMMENT ON FUNCTION pg_catalog.reverse(text) IS 'Reverse string or part of string';
+
+CREATE FUNCTION dump(text) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION dump(text, integer) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION concat(text, text)
+RETURNS text
+AS 'MODULE_PATHNAME','ora_concat'
+LANGUAGE C IMMUTABLE;
+COMMENT ON FUNCTION concat(text, text) IS 'Concat two strings';
+
+CREATE FUNCTION concat(text, anyarray)
+RETURNS text
+AS 'SELECT concat($1, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anyarray, text)
+RETURNS text
+AS 'SELECT concat($1::text, $2)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anyarray, anyarray)
+RETURNS text
+AS 'SELECT concat($1::text, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(text, anynonarray)
+RETURNS text
+AS 'SELECT concat($1, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anynonarray, text)
+RETURNS text
+AS 'SELECT concat($1::text, $2)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anynonarray, anynonarray)
+RETURNS text
+AS 'SELECT concat($1::text, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement, autoflush bool)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text, true); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement, bool) IS 'Puts data to specified file and append newline character';
+
+COMMIT;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/orafunc-8.4.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/orafunc-8.4.sql b/contrib/orafce/orafunc-8.4.sql
new file mode 100644
index 0000000..820b7f5
--- /dev/null
+++ b/contrib/orafce/orafunc-8.4.sql
@@ -0,0 +1,124 @@
+CREATE FUNCTION pg_catalog.reverse(str text)
+RETURNS text
+AS $$ SELECT plvstr.rvrs($1,1,NULL);$$
+LANGUAGE SQL IMMUTABLE STRICT;
+COMMENT ON FUNCTION pg_catalog.reverse(text) IS 'Reverse string or part of string';
+
+CREATE FUNCTION dump(text) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION dump(text, integer) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION concat(text, text)
+RETURNS text
+AS 'MODULE_PATHNAME','ora_concat'
+LANGUAGE C IMMUTABLE;
+COMMENT ON FUNCTION concat(text, text) IS 'Concat two strings';
+
+CREATE FUNCTION concat(text, anyarray)
+RETURNS text
+AS 'SELECT concat($1, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anyarray, text)
+RETURNS text
+AS 'SELECT concat($1::text, $2)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anyarray, anyarray)
+RETURNS text
+AS 'SELECT concat($1::text, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(text, anynonarray)
+RETURNS text
+AS 'SELECT concat($1, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anynonarray, text)
+RETURNS text
+AS 'SELECT concat($1::text, $2)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anynonarray, anynonarray)
+RETURNS text
+AS 'SELECT concat($1::text, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement, autoflush bool)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text, true); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement, bool) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION pg_catalog.listagg1_transfn(internal, text)
+RETURNS internal 
+AS 'MODULE_PATHNAME','orafce_listagg1_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.listagg2_transfn(internal, text, text)
+RETURNS internal 
+AS 'MODULE_PATHNAME','orafce_listagg2_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.listagg_finalfn(internal)
+RETURNS text
+AS 'MODULE_PATHNAME','orafce_listagg_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE AGGREGATE pg_catalog.listagg(text) (
+  SFUNC=pg_catalog.listagg1_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.listagg_finalfn
+);
+
+CREATE AGGREGATE pg_catalog.listagg(text, text) (
+  SFUNC=pg_catalog.listagg2_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.listagg_finalfn
+);
+
+CREATE FUNCTION pg_catalog.median4_transfn(internal, real)
+RETURNS internal
+AS 'MODULE_PATHNAME','orafce_median4_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median4_finalfn(internal)
+RETURNS real
+AS 'MODULE_PATHNAME','orafce_median4_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median8_transfn(internal, double precision)
+RETURNS internal
+AS 'MODULE_PATHNAME','orafce_median8_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median8_finalfn(internal)
+RETURNS double precision
+AS 'MODULE_PATHNAME','orafce_median8_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE AGGREGATE pg_catalog.median(real) (
+  SFUNC=pg_catalog.median4_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.median4_finalfn
+);
+
+CREATE AGGREGATE pg_catalog.median(double precision) (
+  SFUNC=pg_catalog.median8_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.median8_finalfn
+);
+
+COMMIT;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/orafunc-9.0.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/orafunc-9.0.sql b/contrib/orafce/orafunc-9.0.sql
new file mode 100644
index 0000000..820b7f5
--- /dev/null
+++ b/contrib/orafce/orafunc-9.0.sql
@@ -0,0 +1,124 @@
+CREATE FUNCTION pg_catalog.reverse(str text)
+RETURNS text
+AS $$ SELECT plvstr.rvrs($1,1,NULL);$$
+LANGUAGE SQL IMMUTABLE STRICT;
+COMMENT ON FUNCTION pg_catalog.reverse(text) IS 'Reverse string or part of string';
+
+CREATE FUNCTION dump(text) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION dump(text, integer) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION concat(text, text)
+RETURNS text
+AS 'MODULE_PATHNAME','ora_concat'
+LANGUAGE C IMMUTABLE;
+COMMENT ON FUNCTION concat(text, text) IS 'Concat two strings';
+
+CREATE FUNCTION concat(text, anyarray)
+RETURNS text
+AS 'SELECT concat($1, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anyarray, text)
+RETURNS text
+AS 'SELECT concat($1::text, $2)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anyarray, anyarray)
+RETURNS text
+AS 'SELECT concat($1::text, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(text, anynonarray)
+RETURNS text
+AS 'SELECT concat($1, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anynonarray, text)
+RETURNS text
+AS 'SELECT concat($1::text, $2)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION concat(anynonarray, anynonarray)
+RETURNS text
+AS 'SELECT concat($1::text, $2::text)'
+LANGUAGE sql IMMUTABLE;
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement, autoflush bool)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text, true); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement, bool) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION pg_catalog.listagg1_transfn(internal, text)
+RETURNS internal 
+AS 'MODULE_PATHNAME','orafce_listagg1_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.listagg2_transfn(internal, text, text)
+RETURNS internal 
+AS 'MODULE_PATHNAME','orafce_listagg2_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.listagg_finalfn(internal)
+RETURNS text
+AS 'MODULE_PATHNAME','orafce_listagg_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE AGGREGATE pg_catalog.listagg(text) (
+  SFUNC=pg_catalog.listagg1_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.listagg_finalfn
+);
+
+CREATE AGGREGATE pg_catalog.listagg(text, text) (
+  SFUNC=pg_catalog.listagg2_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.listagg_finalfn
+);
+
+CREATE FUNCTION pg_catalog.median4_transfn(internal, real)
+RETURNS internal
+AS 'MODULE_PATHNAME','orafce_median4_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median4_finalfn(internal)
+RETURNS real
+AS 'MODULE_PATHNAME','orafce_median4_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median8_transfn(internal, double precision)
+RETURNS internal
+AS 'MODULE_PATHNAME','orafce_median8_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median8_finalfn(internal)
+RETURNS double precision
+AS 'MODULE_PATHNAME','orafce_median8_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE AGGREGATE pg_catalog.median(real) (
+  SFUNC=pg_catalog.median4_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.median4_finalfn
+);
+
+CREATE AGGREGATE pg_catalog.median(double precision) (
+  SFUNC=pg_catalog.median8_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.median8_finalfn
+);
+
+COMMIT;

http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/e74af545/contrib/orafce/orafunc-9.1.sql
----------------------------------------------------------------------
diff --git a/contrib/orafce/orafunc-9.1.sql b/contrib/orafce/orafunc-9.1.sql
new file mode 100644
index 0000000..cd7bed7
--- /dev/null
+++ b/contrib/orafce/orafunc-9.1.sql
@@ -0,0 +1,82 @@
+CREATE FUNCTION dump(text) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION dump(text, integer) 
+RETURNS varchar
+AS 'MODULE_PATHNAME', 'orafce_dump'
+LANGUAGE C;
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION utl_file.put_line(file utl_file.file_type, buffer anyelement, autoflush bool)
+RETURNS bool
+AS $$SELECT utl_file.put_line($1, $2::text, true); $$
+LANGUAGE SQL VOLATILE;
+COMMENT ON FUNCTION utl_file.put_line(utl_file.file_type, anyelement, bool) IS 'Puts data to specified file and append newline character';
+
+CREATE FUNCTION pg_catalog.listagg1_transfn(internal, text)
+RETURNS internal 
+AS 'MODULE_PATHNAME','orafce_listagg1_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.listagg2_transfn(internal, text, text)
+RETURNS internal 
+AS 'MODULE_PATHNAME','orafce_listagg2_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.listagg_finalfn(internal)
+RETURNS text
+AS 'MODULE_PATHNAME','orafce_listagg_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE AGGREGATE pg_catalog.listagg(text) (
+  SFUNC=pg_catalog.listagg1_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.listagg_finalfn
+);
+
+CREATE AGGREGATE pg_catalog.listagg(text, text) (
+  SFUNC=pg_catalog.listagg2_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.listagg_finalfn
+);
+
+CREATE FUNCTION pg_catalog.median4_transfn(internal, real)
+RETURNS internal
+AS 'MODULE_PATHNAME','orafce_median4_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median4_finalfn(internal)
+RETURNS real
+AS 'MODULE_PATHNAME','orafce_median4_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median8_transfn(internal, double precision)
+RETURNS internal
+AS 'MODULE_PATHNAME','orafce_median8_transfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE FUNCTION pg_catalog.median8_finalfn(internal)
+RETURNS double precision
+AS 'MODULE_PATHNAME','orafce_median8_finalfn'
+LANGUAGE C IMMUTABLE;
+
+CREATE AGGREGATE pg_catalog.median(real) (
+  SFUNC=pg_catalog.median4_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.median4_finalfn
+);
+
+CREATE AGGREGATE pg_catalog.median(double precision) (
+  SFUNC=pg_catalog.median8_transfn, 
+  STYPE=internal, 
+  FINALFUNC=pg_catalog.median8_finalfn
+);
+
+COMMIT;


Mime
View raw message