Add select * from regex_captures

[asg017]

Using .captures_iter

[greatvovan]

As I understand, you are proposing a table function. Are you planing to expose capturing groups as rows or columns? How do you envision it's usage in cases like

... ID123Y2023ABC ...

Say, I need to select ID and year as columns. If you return groups as columns, I could write something like

SELECT (SELECT g1 from regex_captures(my_field, 'ID(d+)Y(d+)ABC') as ID,
  (SELECT g2 from regex_captures(my_field, 'ID(d+)Y(d+)ABC') as year
FROM my_table

It looks like we are going to call the function twice with the same arguments. Is that what you going to do or you have a simpler solution?

Also I am thinking if it makes sense to have a scalar version of the function, like regex_capture(input, regex, group_num)? It is still unclear though how to select multiple groups into different columns from a single source.