Re: Function problem

On Fri, 22 Feb 2008 11:51:01 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Enrico <scotty(at)linuxtime(dot)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(at)enricopirozzi(dot)info
Skype: sscotty71