Re: [PATCHES] reqd patch

--- ramesh phule <ramesh_success(at)yahoo(dot)com> escribió:

> Dear Sir ,
>
> Can u send me one example of Functions which
> returns more than one row.
>
> Also , Wish you Happy New Year.
>

Remember this is valid code only in 7.4 and later. you
have to change to a newer version to do this. and i
think is better because if you are teaching te best
thing you can do is teach the better.

we have to use the SETOF with a datatype or tablename.

CREATE FUNCTION
rec_f_consultar_planificacion_cursos(smallint,
smallint, character, smallint, smallint, smallint,
character) RETURNS SETOF rec_m_cuadropagosparalelo
AS '
declare
result rec_m_cuadropagosparalelo;

rs record;
rs_par record;
val1 smallint;
begin

if $1 is null or $2 is null or $3 is null or $4
is null or $5 is null
then
raise exception ''Valores nulos en campos no
nulos al consultar planificación por cursos.'';
end if;

if $6 is not null then
-- you can use for in select ... loop ..... end loop
-- for move from records from a select
-- and return next says this record will be returned
-- but the function is not finished continue execution
for rs in select *
from
rec_f_consultar_planificacion_paralelos($1, $2, $3,
$4, $5, $6, $7)
where ent_codigo = $1
and sec_codigo = $2
and ani_codigo = $3
and cic_codigo = $4
and esp_codigo = $5
and cur_codigo = $6
loop
result.ent_codigo := rs.ent_codigo;
result.sec_codigo := rs.sec_codigo;
result.ani_codigo := rs.ani_codigo;
result.cic_codigo := rs.cic_codigo;
result.esp_codigo := rs.esp_codigo;
result.cur_codigo := rs.cur_codigo;
result.cur_paralelo := rs.cur_paralelo;
result.rub_codigo := rs.rub_codigo;
result.cpa_valor := rs.cpa_valor;
result.cpa_fechavencimiento :=
rs.cpa_fechavencimiento;
result.cpa_aplicahermano :=
rs.cpa_aplicahermano;
result.cpa_afectabeca :=
rs.cpa_afectabeca;

return next result;
end loop;
else
for rs in select rub_codigo, cpa_valor,
cpa_fechavencimiento,
count(distinct cur_codigo)
as numpar
from rec_m_cuadropagosparalelo
where ent_codigo = $1
and sec_codigo = $2
and ani_codigo = $3
and cic_codigo = $4
and esp_codigo = $5
and (cur_codigo, rub_codigo) IN
(select cur_codigo, rub_codigo from
rec_f_consultar_planificacion_paralelos(ent_codigo,
sec_codigo, ani_codigo, cic_codigo, esp_codigo,
cur_codigo, null))
group by rub_codigo, cpa_valor,
cpa_fechavencimiento
loop
select into val1 count(distinct
cur_codigo) from aca_t_curso
where ent_codigo = $1
and sec_codigo = $2
and ani_codigo = $3
and cic_codigo = $4
and esp_codigo = $5;

if val1 = rs.numpar then
result.ent_codigo := $1;
result.sec_codigo := $2;
result.ani_codigo := $3;
result.cic_codigo := $4;
result.esp_codigo := $5;

result.rub_codigo := rs.rub_codigo;
result.cpa_valor := rs.cpa_valor;
result.cpa_fechavencimiento :=
rs.cpa_fechavencimiento;
result.cpa_aplicahermano :=
rs.cpa_aplicahermano;
result.cpa_afectabeca :=
rs.cpa_afectabeca;

result.cur_codigo := null;
result.cur_paralelo := null;

return next result;

end if;
end loop;
end if;

return;
end; '
LANGUAGE plpgsql;

regards,
Jaime Casanova

Pd: use the mailing list, i will to post it there
because if i don't know the answer or cannot answer
for any reason always will be some one that answer you

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com