2 440
edições
m (→tentativa 3: fez o esperado) |
|||
(3 revisões intermediárias pelo mesmo usuário não estão sendo mostradas) | |||
Linha 177: | Linha 177: | ||
; -- NADA DE MENSAGEM! | ; -- NADA DE MENSAGEM! | ||
-- nao roda o loop .. devolver a função e gerar texto e rodar na mão. | -- nao roda o loop .. devolver a função e gerar texto e rodar na mão. | ||
</syntaxhighlight> | |||
=== tentativa 3 === | |||
Tentando simplesmente devolver a string. Parece que agora funciona: | |||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
DROP FUNCTION if exists osmc.grid_generate_all_levels_logistic2 | |||
; | |||
CREATE or replace FUNCTION osmc.grid_generate_all_levels_logistic2( | |||
p_lev_max numeric, p_contry text, | |||
p_contry_base integer, | |||
p_id_bitlen integer, -- ex. 37 | |||
p_lev0 numeric default 2.5 | |||
) RETURNS text LANGUAGE plpgsql AS $f$ | |||
DECLARE | |||
tpl text; | |||
cover_frag text; | |||
oid_list text[]; | |||
lev numeric; | |||
s text :=''; | |||
ret text :=''; | |||
gg text :='geom4326'; | |||
BEGIN | |||
tpl := $$ | |||
SELECT cbits as gid_vbit, geom | |||
FROM osmc.decode_scientific_absolute_geoms_lixo1( | |||
natcod.parents_to_children_baseh(%1$s::real, %3$L::text[], 16, true, true)::text, | |||
%2$L, | |||
%4$s::int | |||
) t | |||
$$; | |||
SELECT array_agg(x::text) INTO oid_list | |||
FROM ( | |||
SELECT array_agg(cod_b16h)::text as x | |||
FROM ( | |||
select cod_b16h, row_number() over() ord | |||
from grid_br2.poc_caruaru_vw01logistic | |||
where length(gid_vbit)=p_id_bitlen | |||
) t1 | |||
GROUP BY ord/100 | |||
) t2; | |||
FOREACH cover_frag IN ARRAY oid_list LOOP | |||
s:=''; | |||
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; | |||
s := s || format(tpl, lev::text, p_contry, cover_frag, p_contry_base::text); | |||
END LOOP; -- lev | |||
s := s|| E'\n ORDER BY 1'; | |||
ret := ret || E'INSERT INTO grid_br2.tmp_cells \n' || s || E';\n COMMIT; \n\n\n'; | |||
END LOOP; -- cover_frag | |||
RETURN ret; | |||
END; | |||
$f$; | |||
SELECT volat_file_write( | |||
'/tmp/inserts_logistic2.sql' | |||
, osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 27, 18 ) | |||
); | |||
</syntaxhighlight> | |||
=== tentativa 4 === | |||
Finalmente reescrevendo como procedure: | |||
<syntaxhighlight lang="sql" style="font-size: 80%;"> | |||
DROP FUNCTION if exists osmc.grid_generate_all_levels_logistic2 | DROP FUNCTION if exists osmc.grid_generate_all_levels_logistic2 | ||
; | ; | ||
CREATE | DROP PROCEDURE if exists osmc.grid_generate_all_levels_logistic2 | ||
; | |||
CREATE PROCEDURE osmc.grid_generate_all_levels_logistic2( | |||
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_id_bitlen integer, -- ex. 37 | ||
p_lev0 numeric default 2.5 | p_lev0 numeric default 2.5 | ||
) | ) LANGUAGE plpgsql AS $f$ | ||
DECLARE | DECLARE | ||
tpl text; | tpl text; | ||
Linha 206: | Linha 268: | ||
$$; | $$; | ||
RAISE NOTICE 'entrei...'; | RAISE NOTICE 'entrei...'; | ||
select array_agg(x) FROM ( | select array_agg(x::text) INTO oid_list | ||
FROM ( | |||
SELECT array_agg(cod_b16h)::text as x | |||
FROM ( | |||
select cod_b16h, row_number() over() ord | |||
from grid_br2.poc_caruaru_vw01logistic | |||
where length(gid_vbit)=27 | |||
) t1 | |||
) t2 | GROUP BY ord/100 | ||
) t2; | |||
FOREACH cover_frag IN ARRAY oid_list LOOP | FOREACH cover_frag IN ARRAY oid_list LOOP | ||
s:=''; | s:=''; | ||
Linha 223: | Linha 286: | ||
END LOOP; -- lev | END LOOP; -- lev | ||
s := s|| E'\n ORDER BY 1'; | s := s|| E'\n ORDER BY 1'; | ||
s := E'INSERT INTO | s := E'INSERT INTO grid_br2.tmp_cells \n' || s || E';\n COMMIT;'; | ||
RAISE NOTICE 'RODANDO | RAISE NOTICE '.. RODANDO'; | ||
s := | PERFORM s; | ||
s := ''; | |||
END LOOP; -- cover_frag | END LOOP; -- cover_frag | ||
RAISE NOTICE 'sai...'; | RAISE NOTICE 'sai...'; | ||
END; | END; | ||
$f$; | $f$; | ||
CALL osmc.grid_generate_all_levels_logistic2( 2.5, 'BR', 27, 18 ); | |||
</syntaxhighlight> | </syntaxhighlight> |
edições