Function problem

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. Could we see the whole example
not just a fragment? Usually, when you can't understand the problem,
it's because you're looking in the wrong place --- so a partial
example is tough to debug.

regards, tom lane

Enrico wrote:

Hi all,
I have this piece of code

DECLARE
minv my_inv;
r record;
totale numeric(20,5);
valore numeric(20,5);
BEGIN

[.........]

totale := 0;
for r in select * from tminv loop
[.........]
valore := r.prezzo*r.giacenza;
totale := totale+valore;
minv.totale = totale;
return next minv;
end loop;
return;
[......]

So
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.

minv.totale := totale;

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

I solved my problem :)

the problem is in the query that calls my function:

select * from calcolo_inventario('26','2008-02-22','05')
where giacenza > 0

because the resulset has a negative row that doesn't appear on screen

Thank you very much to all of you
Enrico