pgpcre

pig build pkg pgpcre;		# build rpm

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

pig install pgpcre;		# install via package name, for the active PG version

pig install pgpcre -v 18;   # install for PG 18
pig install pgpcre -v 17;   # install for PG 17
pig install pgpcre -v 16;   # install for PG 16
pig install pgpcre -v 15;   # install for PG 15
pig install pgpcre -v 14;   # install for PG 14

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;