Re: Function problem

[Enrico <scotty@xxxxxxxxxxxx>]

On Fri, 22 Feb 2008 11:51:01 -0500
Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

> Enrico <scotty@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxxx
Skype: sscotty71

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster