Thread: [GENERAL] Function
Good morning for all
I´m beginer in PL/pgSQL functions and I have one difficulty. The function bellow should return many records but return just one. The loop just do the first INSERT INTO and get out.
CREATE OR REPLACE FUNCTION geo_output.funcao30(num integer)
RETURNS TABLE (quadro_id integer, cod_mun integer, item_id integer, item text, secao varchar, quadro varchar, populacaoalvo text, valorpopulacaoalvo text, parametroproposto text, indice text, parametrocalculado numeric) AS $$
DECLARE
fcod_mun int;
BEGIN
FOR fcod_mun IN
SELECT substring(cd_geocmu,1,6)::int AS cod_mun FROM geo_politico.g_br_municipios
WHERE substring(cd_geocmu,1,2)::int = num
loop
INSERT INTO geo_output.tmp_parametros_todos_campos
SELECT
t.quadro_id
,t.cod_mun
,t.item_id
,t.item
,substring(t.secao,1,47)::varchar AS secao
,t.quadro
,substring(t.populacaoalvo::varchar,16,30) AS populacaoalvo
,substring(t.valorpopulacaoalvo::varchar,12,5) AS valorpopulacaoalvo
,substring(t.parametroproposto,2,(length(t.parametroproposto)-2)) AS parametroproposto
,substring(t.indice,2,(length(t.indice)-2)) AS indice
,t.parametrocalculado
FROM parametros.calcula_parametros(fcod_mun) t
WHERE t.item_id = 2;
END loop;
RETURN query SELECT * FROM geo_output.tmp_parametros_todos_campos;
END;
$$ LANGUAGE plpgsql;
On 26/10/17 15:00, Marcio Farah wrote: > Good morning for all > > I´m beginer in PL/pgSQL functions and I have one difficulty. The > function bellow should return many records but return just one. The loop > just do the first INSERT INTO and get out. If you run SELECT substring(cd_geocmu,1,6)::int AS cod_mun FROM geo_politico.g_br_municipios WHERE substring(cd_geocmu,1,2)::int= num by itself (with a suitable value of "num"), do you get more than one row? Also, what is the definition of the function parametros.calcula_parametros()? Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general