Retrieve results from regexp_matches as one row

Question

So, if have this string aassdd

This code:

regexp_matches('aassdd', 'a', 'g')

returns 2 different rows.

It is possible to retrieve all matches as one row? for example as array type as one row, that is from code above, needed result is: {a,a}

Answer 1

The fact that regexp_matches() returns a set rather than a scalar is understandable but still somewhat annoying.

The only workaround I found is this somewhat ugly query:

select array_agg(i)
from (
   select (regexp_matches('aassdd', 'a', 'g'))[1] i 
)  t

Answer 2

SELECT ARRAY(select array_to_string(regexp_matches('aassdd', 'a', 'g'),''));

Answer 3

If regexp_matches() takes as parameter a column of an indexed table, there is one more way:

SELECT rid, array_agg(number) 
FROM 
    (SELECT 
       rid, 
       (regexp_matches(column,'[0-9]+','g'))[1] as number 
    FROM table) t
GROUP BY rid