-- pg_orca regexp regression tests
-- Ported from Greenplum testrepo/query/regexp
LOAD 'pg_orca';
SET pg_orca.enable_orca = on;
SET client_min_messages = warning;
-- query01.sql
SELECT regexp_matches('foobarbequebaz', '(bar)(beque)');
 regexp_matches 
----------------
 {bar,beque}
(1 row)

SELECT regexp_matches('foobarbequebazilbarfbonk', '(b[^b]+)(b[^b]+)', 'g');
 regexp_matches 
----------------
 {bar,beque}
 {bazil,barf}
(2 rows)

SELECT regexp_matches('foobarbequebaz', 'barbeque');
 regexp_matches 
----------------
 {barbeque}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox jumped over the lazy dog', E'\\\s+') AS foo;
  foo   
--------
 the
 quick
 brown
 fox
 jumped
 over
 the
 lazy
 dog
(9 rows)

SELECT regexp_split_to_array('the quick brown fox jumped over the lazy dog', E'\\s+');
             regexp_split_to_array              
------------------------------------------------
 {the,quick,brown,fox,jumped,over,the,lazy,dog}
(1 row)

SELECT foo FROM regexp_split_to_table('the quick brown fox', E'\\s*') AS foo;
 foo 
-----
 t
 h
 e
 q
 u
 i
 c
 k
 b
 r
 o
 w
 n
 f
 o
 x
(16 rows)

SELECT '123' ~ E'^\\d{3}';
 ?column? 
----------
 t
(1 row)

SELECT 'abc' SIMILAR TO 'abc';
 ?column? 
----------
 t
(1 row)

SELECT 'abc' SIMILAR TO 'a';
 ?column? 
----------
 f
(1 row)

SELECT 'abc' SIMILAR TO '%(b|d)%';
 ?column? 
----------
 t
(1 row)

SELECT 'abc' SIMILAR TO '(b|c)%';
 ?column? 
----------
 f
(1 row)

SELECT substring('foobar' from '%#"o_b#"%' for '#');
 substring 
-----------
 oob
(1 row)

SELECT substring('foobar' from '#"o_b#"%' for '#');
 substring 
-----------
 
(1 row)

SELECT substring('foobar' from 'o.b');
 substring 
-----------
 oob
(1 row)

SELECT substring('foobar' from 'o(.)b');
 substring 
-----------
 o
(1 row)

SELECT regexp_replace('foobarbaz', 'b..', 'X');
 regexp_replace 
----------------
 fooXbaz
(1 row)

SELECT regexp_replace('foobarbaz', 'b..', 'X', 'g');
 regexp_replace 
----------------
 fooXX
(1 row)

SELECT regexp_replace('foobarbaz', 'b(..)', E'X\\1Y', 'g');
 regexp_replace 
----------------
 fooXarYXazY
(1 row)

SELECT SUBSTRING('XY1234Z', 'Y*([0-9]{1,3})');
 substring 
-----------
 123
(1 row)

SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
 substring 
-----------
 1
(1 row)