Re: Re: Re: Allow Reg Expressions in Position function

On Mon, 20 Aug 2018 at 14:46, Nick Dro <postgresql(at)walla(dot)co(dot)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