From: | Marcio Farah <marciofarah(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Function |
Date: | 2017-10-26 14:00:58 |
Message-ID: | CAMs6HJSaFZ_AYavz802o-88HJz0Gw3MqYrgRmq5vntqfLqr=0w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-10-26 15:01:27 | Re: query not scaling |
Previous Message | Rob Sargent | 2017-10-26 13:11:01 | Re: query not scaling |