2-function_ par.get_responsaveis_termo.sql

-- DROP FUNCTION par.get_responsaveis_termo(varchar, date, date, date, varchar);

CREATE OR REPLACE FUNCTION par.get_responsaveis_termo(i_muncod character varying, i_datainiciovigencia date, i_datafimvigencia date, i_datafimpc date, i_tipoprograma character varying)
 RETURNS TABLE(_rrpid integer, _cpf character varying, _nomeprefeito character varying, _dtiniciomandato date, _dtfimmandato date, _naturezainteresse character varying)
 LANGUAGE plpgsql
AS $function$
	DECLARE
		responsavel record;

	BEGIN		    				

		FOR responsavel IN(
				SELECT  
					distinct
					rrpid, 
					rrpdtiniciomandato,
					rrpdtfimmandato,
					usucpf,
					rrpnomeprefeito,
					 trim(par.naturezainteresse_v2(
											rrpdtiniciomandato, 
											rrpdtfimmandato, 
											i_datainiciovigencia,
											i_datafimvigencia, 
											i_tipoprograma)
						) as naturezainteresse 
				FROM 
					par.rollresponsaveisprefeitos 
				WHERE
					muncod in(i_muncod)
					and  ( rrpdtfimmandato between  i_datainiciovigencia and i_datafimpc or rrpdtiniciomandato between i_datainiciovigencia and i_datafimpc )
				order by rrpid desc
		)
		LOOP
			_rrpid := responsavel.rrpid;
			_cpf := responsavel.usucpf;
			_nomeprefeito := responsavel.rrpnomeprefeito;
			_dtiniciomandato := responsavel.rrpdtiniciomandato;
			_dtfimmandato := responsavel.rrpdtfimmandato;
			_naturezainteresse := responsavel.naturezainteresse;
			return next;		
		END LOOP;

END; $function$;