I had a bug in the function, and I see I also accidentally renamed it to regexp_ranges().
Attached is a fixed version of the PoC. This function is e.g. useful when we're interested in patterns in meta-data, where we're not actually finding patterns in the data, but in a string where each character corresponds to an element in an array, containing the actual data. In such case, we need to know the positions of the matches, since they tell what corresponding array elements that matched. For instance, let's take the UNIX diff tool we all know as an example. Let's say you have all the raw diff lines stored in a text[] array, and we want to produce a unified diff, by finding hunks with up to 3 unchanged lines before/after each hunk containing changes. If we produce a text string containing one character per diff line, using "=" for unchanged, "+" for addition, "-" for deletion. Example: =====-=======+=====-+====== We could then find the hunks using this regex: (={0,3}[-+]+={0,3})+ using regexp_positions() to find the start and end positions for each hunk: SELECT * FROM regexp_positions('=====-=======+=====-+======','(={0,3}[-+]+={0,3})+'); start_pos | end_pos -----------+--------- 3 | 9 11 | 24 (2 rows) /Joel
regexp_positions.sql
Description: Binary data