Sample Header Ad - 728x90

Best way to cast array (text[]) to record-type?

3 votes
0 answers
1572 views
PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? [Currently, I'm doing it quite explicitly,](https://github.com/houstondatavis/data-jam-february-2018/blob/master/sql/06_funcs.sql) SELECT (a,a,a,a,a,a)::census.series_id FROM census.alldata CROSS JOIN LATERAL regexp_matches(series_id, '^(.{2})(.)(.*?)(.{4})(.{2})(.)$') AS t(a); And, I'm wondering if there is a nicer way to do this than that. Ideally, I would just be able to cast text[] into any record type, and the coercion would take place under the hood. When I try this I get an error though, SELECT a::census.series_id FROM census.alldata CROSS JOIN LATERAL regexp_matches(series_id, '^(.{2})(.)(.*?)(.{4})(.{2})(.)$') AS t(a); ERROR: cannot cast type text[] to census.series_id LINE 1: SELECT a::census.series_id
Asked by Evan Carroll (65502 rep)
Feb 18, 2018, 12:12 AM
Last activity: Feb 18, 2018, 02:18 AM