re2

re2 : ClickHouse-compatible regex functions using RE2

Overview

Packages

Source

pig build pkg re2;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

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

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

Create this extension with:

CREATE EXTENSION re2;

Usage

Sources: official README, official reference doc, v0.1.1 release

re2 provides ClickHouse-compatible regular expression functions backed by Google’s RE2 engine. It exposes both text and bytea overloads, so binary data with \\0 bytes can be searched too.

CREATE EXTENSION re2;

SELECT re2match('hello world', 'h.*o');
SELECT re2extract('Order #123', '(\\d+)');
SELECT re2countmatches('a1 b2 c3', '\\d');

Core Functions

• re2match(haystack, pattern) -> boolean
• re2extract(haystack, pattern) -> text|bytea
• re2extractall(haystack, pattern) -> text[]|bytea[]
• re2regexpextract(haystack, pattern, index default 1) -> text|bytea
• re2extractgroups(haystack, pattern) -> text[]|bytea[]
• re2replaceregexpone(haystack, pattern, replacement) -> text|bytea
• re2replaceregexpall(haystack, pattern, replacement) -> text|bytea
• re2countmatches(...) and re2countmatchescaseinsensitive(...)

Multi-Pattern Matching

The re2multimatch* family accepts either multiple pattern arguments or a VARIADIC array:

SELECT re2multimatchany('error: timeout', 'timeout', 'denied');
SELECT re2multimatchanyindex('error: timeout', VARIADIC ARRAY['timeout', 'denied']);
SELECT re2multimatchallindices('error: timeout', 'error', 'timeout', 'panic');

Matching Semantics

• To match ClickHouse behavior, . matches line breaks by default.
• Prefix the pattern with (?-s) if you want . not to cross line breaks.
• Replacement strings support \\0 through \\9 backreferences.

Caveats

• Upstream requires the system re2 library at build/install time.
• The v0.1.1 release is binary-only: it adds PostgreSQL 13+ support and documents VARIADIC use for multi-pattern functions, but existing v0.1 SQL installations do not need ALTER EXTENSION UPDATE.