Re: Function problem - Mailing list pgsql-general

On Fri, 22 Feb 2008 11:51:01 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Enrico <scotty@linuxtime.it> writes:
> > the first record has valore=98 and totale=0 for the first time of
> > the loop, but my first result of totale is -298 instead of +98.
>
> Hmm, that's a bit hard to believe.

Yes you right, it's hard to believe for me too :)

> Could we see the whole example
> not just a fragment?

This is my code :
-------------------------------------------------------------------------------------------------------------
CREATE TYPE my_inv AS
   (codice_art character(15),
    codice_agg character(20),
    descr character varying(60),
    gruppo smallint,
    giacenza numeric(20,5),
    prezzo_lis numeric(20,5),
    prezzo numeric(20,5),
    valore numeric(20,5),
    totale numeric (20,5));

ALTER TYPE my_inv OWNER TO postgres;


CREATE OR REPLACE FUNCTION calcolo_inventario(bpchar, date, bpchar)
  RETURNS SETOF my_inv AS
$BODY$
DECLARE
  minv my_inv;
  r record;
  flag int4;
  tot numeric(20,5);
  valore numeric(20,5);

BEGIN

  create temp table tminv (
     codice_art char(13),
     codice_agg char(20),
     descr char(60),
     gruppo int4,
     giacenza numeric(20,5),
     prezzo_lis numeric(20,5),
     prezzo numeric(20,5)
  );


  insert into tminv (codice_art,descr,gruppo,giacenza,prezzo_lis,prezzo)
  select distinct S.codice_art, A.descr,A.gruppo,A.giacenza,L.prezzo as
prezzo_lis,avg(S.importo) as prezzo from scrittura_magazzino S inner
join anag_art A on (S.codice_art=A.codice_art) left join ean13 E on
(S.codice_art=E.codice_art) inner join listini L on
(s.codice_art=L.codice_art) where S.c_s='C'
       and L.listino = $1
       and data_doc <= $2
       group by 1,2,3,4,5;


  update tminv
  set codice_agg = CA.codice_agg
  from codici_aggiuntivi CA
  where tminv.codice_art = CA.codice_art
  and CA.tipo_cod = $3;


  tot := 0;
  for r in select * from tminv loop
    minv.codice_art := r.codice_art;
    minv.codice_agg := r.codice_agg;
    minv.descr := r.descr;
    minv.gruppo := r.gruppo;
    minv.giacenza := r.giacenza;
    minv.prezzo_lis := r.prezzo_lis;
    minv.prezzo := r.prezzo;
    valore := r.prezzo*r.giacenza;
    minv.valore := valore;

    tot := tot+valore;
    minv.totale := tot;
    return next minv;
  end loop;
  return;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;


-----------------------------------------------------------------------
There is then another question if I write

[....]

  for r in select * from tminv loop
      tot := 0;
[....]

the result is correct, but if I want to sum the results with

[........]
  tot := 0;
  for r in select * from tminv loop
[........]
the result is not correct

Thanks for your time :)

Enrico





--
One small feel for man, one giant ass for mankind (Dr. Gregory House)

Enrico Pirozzi
Web: http://www.enricopirozzi.info
E-Mail: info@enricopirozzi.info
Skype: sscotty71