Convertir Funcion para consultar todos los usuarios!!! El problema que tengo es QUE TENGO LA SIGUIENTE FUNCION:
Código:
EL PROBLEMA ESTA EN QUE ME ARROGA REGISTROS MULTIPLES Y CREO QUE HAY QUE PONERLE UN CONTROLADOR (IF O ALGO ASI) 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;
CUANDO EJECUTO LA SIGUIENTE CONSULTA EN LA FUNCION " f_consulta_fondo_solidario_todos"
Código:
ME RETORNA EL SIGUIENTE RROR: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')
[/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]
