Обсуждение: Problem with function & trigger

This does not work. The error is ERROR: pg_atoi: error in "12.00": can't
parse ".00"

CREATE FUNCTION "togliscar" () RETURNS opaque AS 'BEGIN
UPDATE magazzino SET quantita = (SELECT quantita FROM magazzino WHERE
descrizione = NEW.descrizione )
- NEW.quantita WHERE descrizione = NEW.descrizione;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';



CREATE TRIGGER "togliscarico" BEFORE INSERT ON "scarico"  FOR EACH ROW
EXECUTE PROCEDURE
"togliscar" ();

This works.


CREATE FUNCTION "delscar" () RETURNS opaque AS 'BEGIN
UPDATE magazzino SET quantita = (SELECT quantita FROM magazzino WHERE
descrizione = OLD.descrizione )
+ OLD.quantita WHERE descrizione = OLD.descrizione;
RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER "delscarico" BEFORE DELETE ON "scarico"  FOR EACH ROW
EXECUTE PROCEDURE
"delscar" ();

The field quantita is numeric(10,2).

Both functions works if quantita is integer.

Any hint?

Bye!

Carlo Vitolo <carlino@ilrspa.com> writes:
> This does not work. The error is ERROR: pg_atoi: error in "12.00": can't
> parse ".00"

What PG version are you running?  It seems to work fine for me in
current sources:

regression=# create table magazzino (quantita numeric(10,2),
regression(# descrizione text);
CREATE
regression=# create table scarico (quantita numeric(10,2),
regression(# descrizione text);
CREATE
regression=# insert into magazzino values(100, 'test1');
INSERT 400688 1
regression=# insert into magazzino values(200, 'test2');
INSERT 400689 1

<< create functions and triggers copied from your mail >>

regression=# insert into scarico values(10.4, 'test1');
INSERT 400694 1
regression=# select * from  magazzino ;quantita | descrizione
----------+-------------  200.00 | test2   89.60 | test1
(2 rows)

regression=# delete from scarico;
DELETE 1
regression=# select * from  magazzino ;quantita | descrizione
----------+-------------  200.00 | test2  100.00 | test1
(2 rows)



BTW, the way you are writing the functions seems bizarrely inefficient.
Why not just:

CREATE FUNCTION "togliscar" () RETURNS opaque AS 'BEGIN
UPDATE magazzino SET quantita = quantita - NEW.quantita WHERE descrizione = NEW.descrizione;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

        regards, tom lane

Tom Lane wrote:

> Carlo Vitolo <carlino@ilrspa.com> writes:
> > This does not work. The error is ERROR: pg_atoi: error in "12.00": can't
> > parse ".00"
>
> What PG version are you running?  It seems to work fine for me in
> current sources:
>
>

Ver. 7.1.2

>
> BTW, the way you are writing the functions seems bizarrely inefficient.
> Why not just:
>
> CREATE FUNCTION "togliscar" () RETURNS opaque AS 'BEGIN
> UPDATE magazzino
>   SET quantita = quantita - NEW.quantita
>   WHERE descrizione = NEW.descrizione;
> RETURN NEW;
> END;
> ' LANGUAGE 'plpgsql';
>
>                         regards, tom lane

Yes is better thanks!

You are right it works perfectly. It was my fault. I had another trigger on
insert and this function used an incorrect type of variable.

By the way. Is there a way to have an error message like "ERROR xxx in
Function xxx "?.

Bye!