Re: [PATCHES] reqd patch

 --- ramesh phule <ramesh_success@yahoo.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