Easy way to replace an YAML array of IDs with strings from a joined table

Colin 't Hart's comment is 100% correct - your data model fails on the first letter of ACID. YAML is fine for serialization of objects for application consumption, but since your data layer is a RDBMS, you should stick to the RDBMS paradigms. Assuming you don't have the permission to migrate to a better structure and probably force a re-code of the application, the good news is that you're halfway there with your original query already: Instead of going straight to array ( regexp_split_to_array ), split to table ( regexp_split_to_table ) instead, then perform a join on camp_weeks for the names. You could then group that for the stuffed string listing ( using string_agg if it's available on your version of postgres, like shown below ), or pass the results as-is, letting the application worry about the presentation layer.

SELECT  a.name,
        a.id,
        a.title,
        string_agg( cw.week_name, ', ' )
             AS week_names
FROM (  SELECT  cs.id
                    AS camper_id,
                cs.name,
                c.id,
                c.title,
                regexp_split_to_table( trim( 
                    regexp_replace( 
                        cs.camp_weeks, 
                        E'[\\n\\r''-]+',
                        ' ', 'g' ) 
                    ), E'\\D+' ) 
                    AS camp_week_id
        FROM    public.campers cs
        LEFT JOIN public.camps c
            ON  cs.camp_id = c.id ) a
LEFT JOIN public.camp_weeks cw
    ON  CAST( a.camp_week_id AS INTEGER ) = cw.id
GROUP BY a.name, a.id, a.camper_id, a.title
ORDER BY a.id, a.camper_id;