How do you extract set elements in postgres?-CodePudding

Let's say I extract some matches from a regexp_matches call.

How do I actually WORK with sets to do something with whatever I matched?

I.e. I'm looking or something like this

WITH raw_matches AS(
    SELECT DISTINCT regexp_matches(f.bar, 'my-fancy-regex', 'g') AS match
    FROM foo f
)
SELECT <do something with raw.match>
FROM raw_matches raw
;

Can I somehow expand a set into one record per entry or something?

Can I convert a set to an array of strings?

CodePudding user response：

They are called "arrays", not "sets", which may be the reason why you didn't find the appropriate documentation.

You need to perform a lateral join with the table function unnest:

... FROM raw_matches
    CROSS JOIN LATERAL unnest(raw_matches.match) AS u(element)