Using postalCode_ranges with SQL

Internal SQL representation is normalized. Below will show convertions, here a typical query to list postal codes in the integer-range representation:

 SELECT subdivision, int4ranges_to_csvranges( csvranges_to_int4ranges(postalcode_ranges) ) as postalcode_ranges
 FROM tmpcsv_br_state_codes;

subdivision	postalcode_ranges
AC	[69900000 70000000)
AL	[57000000 58000000)
AM	[69000000 69300000) [69400000 69900000)
AP	[68900000 69000000)
BA	[40000000 49000000)
CE	[60000000 64000000)
DF	[73000000 73700000) [70000000 72800000)
ES	[29000000 30000000)
GO	[72800000 73000000) [73700000 76800000)
MA	[65000000 66000000)
MG	[30000000 40000000)
MS	[79000000 80000000)
MT	[78000000 78900000)
PA	[66000000 68900000)
PB	[58000000 59000000)
PE	[50000000 57000000)
PI	[64000000 65000000)
PR	[80000000 88000000)
RJ	[20000000 29000000)
RN	[59000000 60000000)
RO	[76800000 77000000)
RR	[69300000 69400000)
RS	[90000000 100000000)
SC	[88000000 90000000)
SE	[49000000 50000000)
SP	[1000000 10000000) [11000000 20000000)
TO	[77000000 78000000)

Normalize and convert postalCode_ranges to integer-ranges:

CREATE or replace FUNCTION csvranges_to_int4ranges(
  p_range text
) RETURNS int4range[] AS $f$
   SELECT ('{'|| 
      regexp_replace( translate($1,' -',',') , '\[(\d+),(\d+)\]', '"[\1,\2]"', 'g') 
   || '}')::int4range[];
$f$ LANGUAGE SQL IMMUTABLE;

CREATE or replace FUNCTION int4ranges_to_csvranges(
  p_range int4range[]
) RETURNS text AS $f$
   SELECT translate($1::text,',{}"',' ');
$f$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION row_of_cep(p_cep int4) RETURNS tmpcsv_br_state_codes AS $f$
       SELECT  v.*   
       FROM tmpcsv_br_state_codes v, 
            unnest(csvranges_to_int4ranges(postalcode_ranges)) t(r) 
       WHERE $1 <@ r
$f$ LANGUAGE SQL IMMUTABLE;

Checking state of the CEP 04569-010: SELECT subdivision FROM row_of_cep(04569010) (SP).