2 391
edições
(→Geração da grade: procedure) |
|||
Linha 110: | Linha 110: | ||
</syntaxhighlight> | </syntaxhighlight> | ||
<syntaxhighlight lang="sql"> | Ainda não funciona pois a nossa demanda requer subquery, e isso gera erro "cannot use subquery in CALL argument". | ||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
CALL osmc.grid_generate_all_levels_logistic( | |||
2.5, | |||
'BR', | |||
(select array_agg(cod_b16h) from grid_br2.poc_caruaru_vw01logistic where length(gid_vbit)=37), | |||
18 | |||
); | |||
</syntaxhighlight> | |||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
DROP TABLE if exists grid_br2.tmp_cells CASCADE; | DROP TABLE if exists grid_br2.tmp_cells CASCADE; | ||
CREATE TABLE grid_br2.tmp_cells (gid_vbit bit varying, geom public.geometry); | CREATE TABLE grid_br2.tmp_cells (gid_vbit bit varying, geom public.geometry); | ||
DROP PROCEDURE if exists osmc.grid_generate_all_levels_logistic | |||
; | |||
CREATE or replace PROCEDURE osmc.grid_generate_all_levels_logistic( | CREATE or replace PROCEDURE osmc.grid_generate_all_levels_logistic( | ||
p_lev_max numeric, p_contry text | p_lev_max numeric, p_contry text, | ||
p_contry_base integer, | p_contry_base integer, | ||
p_id_bitlen integer, -- ex. 37 | |||
p_lev0 numeric default 2.5 | p_lev0 numeric default 2.5 | ||
) LANGUAGE plpgsql AS $f$ | ) LANGUAGE plpgsql AS $f$ | ||
DECLARE | DECLARE | ||
tpl text; | tpl text; | ||
cover_frag text | cover_frag text; | ||
lev numeric; | lev numeric; | ||
s text :=''; | s text :=''; | ||
Linha 139: | Linha 149: | ||
) t | ) t | ||
$$; | $$; | ||
RAISE NOTICE 'entrei...'; | |||
FOR cover_frag IN ( | FOR cover_frag IN ( | ||
SELECT array_agg(cod_b16h)::text | |||
FROM ( | |||
select cod_b16h, row_number() over() ord | |||
from grid_br2.poc_caruaru_vw01logistic | |||
where length(gid_vbit)=p_id_bitlen -- ! control here | |||
) t | |||
GROUP BY ord/1000 | |||
) LOOP | ) LOOP | ||
s:=''; | s:=''; | ||
RAISE NOTICE 'Loop cover_frag... %s', cover_frag; | |||
FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) LOOP | FOR lev IN (select x from generate_series(p_lev0, p_lev_max, 2.5) t(x)) LOOP | ||
IF lev>p_lev0 THEN s := s || E'\n UNION ALL \n'; END IF; | IF lev>p_lev0 THEN s := s || E'\n UNION ALL \n'; END IF; | ||
s := s || format(tpl, lev::text, p_contry, cover_frag | s := s || format(tpl, lev::text, p_contry, cover_frag, p_contry_base::text); | ||
END LOOP; -- lev | END LOOP; -- lev | ||
s := s|| E'\n ORDER BY 1'; | s := s|| E'\n ORDER BY 1'; | ||
s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;'; | s := E'INSERT INTO grid_b2.tmp_cells \n' || s || E';\n COMMIT;'; | ||
RAISE NOTICE 'RODANDO %', s; | |||
PERFORM s; | PERFORM s; | ||
END LOOP; -- cover_frag | END LOOP; -- cover_frag | ||
RAISE NOTICE 'sai...'; | |||
END; | END; | ||
$f$; | $f$; | ||
CALL osmc.grid_generate_all_levels_logistic( 2.5, 'BR', 27, 18 ) | |||
; -- NADA DE MENSAGEM! | |||
-- nao roda o loop .. devolver a função e gerar texto e rodar na mão. | |||
</syntaxhighlight> | </syntaxhighlight> |
edições