Home

parece que no README onde temos term_lib seria tlib.

Montagem dos prefixos e abreviações de prefixo

Temos as seguintes definições padronizadas de namespaces:

nscount	nsid	label	description	lang	is_base	fk_partof	kx_regconf	jinfo	created
1	1	vianameprefix	Prefixo de nome de via (tipo de logradouro)	pt	t		portuguese		2020-09-30
2	2	vianame	Nome de via sem prefixo e por extenso	pt	t		portuguese		2020-09-30
3	4	vianameprefix-abv	Abreviação de prefixo de nome de via	pt	f		portuguese		2020-09-30

insert into tstore.source (name) values('addressforall'); -- supposing id=1
create table lixo (viaNamePrefix text,"viaNamePrefix-abv" text, is_pref boolean);
COPY lixo FROM '/tmp/pg_io/nsPair-viaNamePrefix.csv' CSV HEADER;
SELECT tstore.upsert(vianameprefix,1,null,true,null,false,true,1) as is_ok FROM ( SELECT distinct vianameprefix  FROM LIXO order by 1) t;
SELECT tstore.upsert("viaNamePrefix-abv",4,null,is_pref, (select id from tlib.n2c_tab(lixo.vianameprefix,1)) ,false,true,1) as is_ok FROM lixo;
drop table lixo;

Vianame

CREATE TABLE optim.vianame(
   id serial           NOT NULL PRIMARY KEY,
   tprefix_id int      NOT NULL REFERENCES optim.term(id), -- rua, avenida, etc.
   tname_id int        NOT NULL REFERENCES optim.term(id), -- nome mesmo
   is_current boolean  NOT NULL DEFAULT true
   ,UNIQUE(tprefix_id,tname_id)
);
CREATE VIEW optim.vw01_vianame AS
  select v.*, t1.term||' '||t2.term as via_name
  from optim.vianame v
     INNER JOIN tstore.term t1 ON v.tprefix_id=t1.id
     INNER JOIN tstore.term t2 ON v.tname_id=t2.id
;
---
CREATE or replace FUNCTION tlib.n2c_check(p_term text, p_ns int) RETURNS int AS $f$
  select id from tlib.n2c_tab(p_term,p_ns)
$f$ language SQL immutable;

CREATE or replace FUNCTION optim.vianame_insert(
  -- falta inser-array para lotes
  p_name text, -- nome completo da via
  p_is_current boolean DEFAULT true
) RETURNS int AS $f$
  WITH afixes AS (
    SELECT aa[2] as prefix_id,  CASE WHEN aa[2] is not null THEN 
        tstore.upsert( array_to_string(p[aa[1]+1:],' '), 2 ) 
        ELSE NULL END as sufix_id
    FROM (
     SELECT p, CASE
       -- not elegant code but supposing compiler optimization (otherwise better is pgPLSQL)
       WHEN tlib.n2c_check(array_to_string(p[1:3],' '),1) is not null THEN array[3, tlib.n2c_check(array_to_string(p[1:3],' '),1) ]
       WHEN tlib.n2c_check(p[1]||' '||p[2],1)  is not null THEN array[2, tlib.n2c_check(p[1]||' '||p[2],1)]
       WHEN tlib.n2c_check(p[1],1)  is not null THEN array[1, tlib.n2c_check(p[1],1)]
       WHEN tlib.n2c_check(p[1],3)   is not null THEN array[1, tlib.n2c_check(p[1],3)]
       ELSE null
       END AS aa
     FROM (SELECT regexp_split_to_array(tlib.normalizeterm(p_name),'\s+')) t1(p) -- or lower(unaccent(trim(p_name)))
    ) t2
  )  -- ,ins1 AS (
     INSERT INTO optim.vianame (tprefix_id, tname_id, is_current) 
        SELECT prefix_id, sufix_id, p_is_current
        FROM afixes
        WHERE prefix_id IS NOT NULL AND sufix_id IS NOT NULL
     ON CONFLICT DO NOTHING
     RETURNING tprefix_id
$f$ language SQL;
-- ex. select optim.vianame_insert(jtags->>'name') from osm_road where jtags?'name';