On Mon, 20 Aug 2018 at 14:46, Nick Dro <postgre...@walla.co.il> wrote:
> My specific issue is alrady solved.
> For the greater good I sent the email requesting to allow reg exp in the
> position functions.
> Not sure if you will implement it... Just wanted to let you know that the
> limited capabilities of this function create overhead.
FWIW, you don't really appear to want position() to handle regexps at
all, rather a completely new function that returns any and all
matching positions of your regexp.
You can do a generalised regexp match with (say)
CREATE OR REPLACE FUNCTION mypositions(s varchar, r varchar) RETURNS
TABLE (c1 BIGINT) LANGUAGE SQL IMMUTABLE AS $$
WITH v AS (
SELECT unnest(arr[1:array_length(arr,1)-1]) AS res FROM
regexp_split_to_array(s, CONCAT('(?=', r, ')')) AS arr
)
SELECT sum(LENGTH(res)) OVER (rows between unbounded preceding and
current row) FROM v;
$$;
Example:
SELECT mypositions ('http://www.wibble.com/s/blah/b/blah', '/(s|b|t)/');
mypositions
-------------
21
28
(2 rows)
I'm not sure that suggesting a new builtin to provide what is a fairly
esoteric requirement and which can be done efficiently with a small
function (as above) is likely to gain much traction.
Geoff
