lib SQL extra - datasets-br/city-codes GitHub Wiki

CREATE or replace FUNCTION public.name2lex(
  p_name text,
  p_cut boolean DEFAULT true
) RETURNS text AS $f$
   SELECT trim(replace(
	   regexp_replace(
	     $1,
	     E' d[aeo] | d[oa]s | com | para |^d[aeo] | / .+| [aeo]s | [aeo] |[\-\' ]',
	     '.',
	     'g'
	   ),
	   '..',
           '.'
       ),'.')
$f$ LANGUAGE SQL IMMUTABLE;

CREATE VIEW dataset.vw8_anatel_ddd AS
  SELECT *, name2lex(unaccent(lower(municipio))) as namelex 
  FROM dataset.vw8_anatel_res263de2001_pgcn_compilado2017
;
COPY (
  SELECT c.name, c.state, c.wdid, c.idibge, c.lexlabel, c.creation, c.extinction, 
       c.postalcode_ranges, ddd.ddd, c.notes
  FROM dataset.vw8_anatel_ddd as ddd RIGHT JOIN dataset.vw2_br_city_codes c
     ON c.state=ddd.uf AND c.lexlabel=ddd.namelex  -- or synonym
) TO '/tmp/lixo.csv' CSV HEADER;

Para gerar o novo arquivo, todavia, verificar como estava sendo realizada a ordenação,

COPY (
  SELECT c.name, c.state, c.wdid, c.idibge, c.lexlabel, c.creation, c.extinction, 
       c.postalcode_ranges, c.notes
  FROM dataset.vw2_br_city_codes c
  ORDER BY 1,2  -- ou seria 1,5?
) TO '/tmp/lixo_cit.csv' CSV HEADER;