Convertir Funcion para consultar todos los usuarios!!!

[mourtblack]

Buenos dias, tardes y noches.

El problema que tengo es QUE TENGO LA SIGUIENTE FUNCION:

Código:

CREATE OR REPLACE FUNCTION f_consulta_fondo_solidario_todos(v_ano_fondo smallint, v_id_area integer)
  RETURNS SETOF consulta_fondo_solidario AS
$BODY$
	DECLARE
		BENEFI RECORD;
		REGISTRO consulta_fondo_solidario;
		V_TOTALES_FONDO VARCHAR[];
		V_TOTALES_INDIV numeric;
	BEGIN
		FOR BENEFI IN SELECT 
				be.id,
				CASE
				
					WHEN be.consecutivo = 0 AND be.id_status_laboral  <> 'TJ'::text THEN 'Tit-'::text || f_smident(be.id)
					WHEN be.id_status_laboral= 'TJ'::text THEN 'Jub-'::text || f_smident(be.id) 
					ELSE 'Fam-'::text || f_smident(be.id) 
				END as identific,
				''::varchar as numero_sap,  
				(select nombre from "Beneficiario" where consecutivo=0) as nombre_titular, 
				date_part('year'::text,age(f_dato_benefi(id, 'fecha_nacimiento'::character varying, true)::timestamp without time zone)) AS edad_titular, 
				CASE WHEN F_DATO_BENEFI(id,'sexo',true)=1 THEN 'M' ELSE 'F' END as sexo_titular, 
				(select nombre from "Tipo_nomina" where id=be.id_tipo_nomina) as nomina, 
				''::varchar as grupo_personal, 
				(select nombre from "Area" where id = ANY (select id_area from "Beneficiario" where consecutivo=0)) as area_titular, 
				(select nombre from "Filial" where id =be.id_filial) as filial, 
				''::varchar as division, 
				(select localidad from "Localidad" where id=be.id_localidad) as localidad, 
				(select nombre from "Status_laboral" where id=be.id_status_laboral) as estatus_laboral, 
				cedula as cedula_participante, 
				nombre as participante, 
				(select nombre from "Parentesco" where id=be.id_parentesco) as parentesco, 
				date_part('year'::text,age(fecha_nacimiento)) AS edad_familiar, 
				CASE WHEN sexo=1 THEN 'M' ELSE 'F' END as sexo_familiar
				FROM "Beneficiario" AS be
				WHERE
					EXISTS (select id from "Status_laboral" where "Status_laboral".id = BE.ID_STATUS_LABORAL AND aplica_planes=true) 
					AND EXISTS (select id from "Parentesco" where "Parentesco".id = BE.id_parentesco AND aplica_fondo_solidario=true)
					AND EXISTS (select id from "Filial" where "Filial".id = BE.id_filial AND aplica_fondo_solidario=true)
					AND EXISTS(SELECT ID FROM "Ajuste_y_transferencia_de_fondo" aj WHERE  ANO_FONDO=V_ANO_FONDO and  (be.cedula_titular= substr(id_beneficiario_desde,1,9)::int OR be.cedula_titular= substr(id_beneficiario_hasta,1,9)::int))
					AND CASE WHEN V_ID_AREA=0 THEN 0=0 ELSE be.id_area= V_ID_AREA END
		LOOP
			IF (V_ANO_FONDO=TO_CHAR(CURRENT_DATE,'YYYY')) THEN
				V_TOTALES_FONDO= (SELECT ARRAY[p,i,f] FROM (select nombre as p,sum(pote)::varchar as i,sum(disponible)::varchar as f from f_suma_pote_familia_por_beneficiario(BENEFI.id) where aplica_fondo_solidario= true and aplica_planes=true and id_plan='plan1' group by 1) as f);
				V_TOTALES_INDIV= (select pote from f_suma_pote_por_beneficiario(BENEFI.ID) where aplica_fondo_solidario= true and aplica_planes=true and id_plan='plan1');
			ELSE
				V_TOTALES_FONDO= (SELECT ARRAY[p,i,f] FROM (select nombre as p,sum(pote)::varchar as i,sum(disponible)::varchar as f from f_suma_pote_familia_por_beneficiario_anos_anteriores(BENEFI.id,V_ANO_FONDO) where aplica_fondo_solidario= true and aplica_planes=true and id_plan='plan1' group by 1) as f);
				V_TOTALES_INDIV= (select pote from f_suma_pote_por_beneficiario_anos_anteriores(BENEFI.ID,V_ANO_FONDO) where aplica_fondo_solidario= true and aplica_planes=true and id_plan='plan1');
			END IF;
			FOR REGISTRO IN SELECT 
				V_TOTALES_FONDO[1],BENEFI.identific,BENEFI.NUMERO_SAP,BENEFI.CEDULA_TITULAR,BENEFI.NOMBRE_TITULAR,BENEFI.EDAD_TITULAR,BENEFI.SEXO_TITULAR,BENEFI.NOMINA,BENEFI.GRUPO_PERSONAL,BENEFI.AREA_TITULAR,BENEFI.FILIAL,BENEFI.DIVISION,BENEFI.LOCALIDAD,BENEFI.ESTATUS_LABORAL, 
				BENEFI.CEDULA_PARTICIPANTE,BENEFI.PARTICIPANTE,BENEFI.PARENTESCO,BENEFI.EDAD_FAMILIAR,BENEFI.SEXO_FAMILIAR,V_TOTALES_INDIV,V_TOTALES_FONDO[2]::numeric - V_TOTALES_INDIV,V_TOTALES_FONDO[2]::numeric,V_TOTALES_FONDO[3]::numeric
			LOOP 
				RETURN NEXT REGISTRO;
			END LOOP;
		END LOOP;
	END;
	
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION f_consulta_fondo_solidario_todos(smallint, integer) OWNER TO sigplan;
GRANT EXECUTE ON FUNCTION f_consulta_fondo_solidario_todos(smallint, integer) TO public;
GRANT EXECUTE ON FUNCTION f_consulta_fondo_solidario_todos(smallint, integer) TO sigplan;
GRANT EXECUTE ON FUNCTION f_consulta_fondo_solidario_todos(smallint, integer) TO sigplan_app_grupo;
GRANT EXECUTE ON FUNCTION f_consulta_fondo_solidario_todos(smallint, integer) TO sigplan_auditoria_grupo;

EL PROBLEMA ESTA EN QUE ME ARROGA REGISTROS MULTIPLES Y CREO QUE HAY QUE PONERLE UN CONTROLADOR (IF O ALGO ASI)

CUANDO EJECUTO LA SIGUIENTE CONSULTA EN LA FUNCION " f_consulta_fondo_solidario_todos"

Código:

select plan,identific,numero_sap as "Nº de Sap",cedula_titular as "Cédula titular",nombre_titular as "Nombre titular", edad_titular as "Edad titular", 
sexo_titular as "Sexo titular", nomina as nómina,grupo_personal as "Grupo de personal",area_titular as "Area titular",filial, division as división,
localidad,estatus_laboral as "Estatus del trabajador",cedula_participante  as "Cédula participante",participante, parentesco,edad_familiar as "Edad familiar", 
sexo_familiar as "Sexo familiar",monto_otorgado_individual as "Monto otorgado individual",monto_otorgado_fondo_sol as "Monto otorgado fondo solidario", 
monto_total_otorgado as "Monto total otorgado", saldo as "Saldo fondo solidario" 
from F_CONSULTA_FONDO_SOLIDARIO_TODOS(smallint '2010', integer '7')

ME RETORNA EL SIGUIENTE RROR:


[/CODE]ERROR: una subconsulta utilizada como expresión retornó más de un registro
CONTEXT: PL/pgSQL function "f_consulta_fondo_solidario_todos" line 7 at for over select rows

********** Error **********

ERROR: una subconsulta utilizada como expresión retornó más de un registro
SQL state: 21000
Context: PL/pgSQL function "f_consulta_fondo_solidario_todos" line 7 at for over select rows[/CODE]