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
Last activity: Feb 18, 2018, 02:18 AM