描述
Problem Statement
CrateDB contains a regexp_matches table function, but for some use cases a regular scalar would be the better fit. One use-case is using them in generated columns, where table functions cannot be used.
From https://github.com/crate/crate/issues/15077:
CREATE TABLE hernan.test (
a TEXT,
b ARRAY(TEXT) GENERATED ALWAYS AS regexp_match(a, '(a(.+)z)')
);
INSERT INTO hernan.test (a)
SELECT 'alcatraz';
Possible Solutions
PostgreSQL provides a regexp_match scalar. See https://www.postgresql.org/docs/16/functions-string.html
regexp_match ( string text, pattern text [, flags text ] ) → text[]
Returns substrings within the first match of the POSIX regular expression pattern to the string; see Section 9.7.3.
regexp_match('foobarbequebaz', '(bar)(beque)') → {bar,beque}
See: https://github.com/crate/crate/blob/ff1e7f7b882f1048be6d7806023674703cc2720a/server/src/main/java/io/crate/expression/tablefunctions/MatchesFunction.java#L54 for the current regexp_matches implementation. The main difference is the support for the global (g) flag, that can result in more than one result.