pgpcre

pig build pkg pgpcre         # build RPM packages

pig repo add pgsql -u          # Add repo and update cache

CREATE EXTENSION pgpcre;

SELECT 'foo' ~ pcre 'fo+';        -- true
SELECT 'bar' !~ pcre 'fo+';       -- true
SELECT 'foo' =~ pcre 'fo+';       -- true (Perl-style operator)

SELECT pcre 'fo+' ~ 'foo';        -- true
SELECT pcre 'fo+' ~ ANY(ARRAY['foo', 'bar']);

SELECT 'FOO' ~ pcre '(?i)fo+';    -- true

SELECT pcre_match('fo+', 'foobar');    -- 'foo'
SELECT pcre_match('fo+', 'barbar');    -- NULL

SELECT pcre_captured_substrings('(fo+)(b..)', 'foobar');
-- ARRAY['foo','bar']

SELECT pcre_captured_substrings('(a|(z))(bc)', 'abc');
-- ARRAY['a',NULL,'bc']

UPDATE my_table SET pcre_col = pcre_col::text::pcre;