pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedProblem with function & trigger
6+ messages / 4 participants
[nested] [flat]
* Problem with function & trigger
@ 2001-07-07 11:05 Carlo Vitolo <carlino@ilrspa.com>
2001-07-07 19:28 ` Re: Problem with function & trigger Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Carlo Vitolo @ 2001-07-07 11:05 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with function & trigger
2001-07-07 11:05 Problem with function & trigger Carlo Vitolo <carlino@ilrspa.com>
@ 2001-07-07 19:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-09 21:05 ` Re: Problem with function & trigger Carlo Vitolo <carlino@ilrspa.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2001-07-07 19:28 UTC (permalink / raw)
To: carlino@ilrspa.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Problem with function & trigger
2001-07-07 11:05 Problem with function & trigger Carlo Vitolo <carlino@ilrspa.com>
2001-07-07 19:28 ` Re: Problem with function & trigger Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-07-09 21:05 ` Carlo Vitolo <carlino@ilrspa.com>
0 siblings, 0 replies; 6+ messages in thread
From: Carlo Vitolo @ 2001-07-09 21:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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!
^ permalink raw reply [nested|flat] 6+ messages in thread
* problem with function trigger
@ 2004-01-13 11:00 jclaudio@capitol.fr
0 siblings, 0 replies; 6+ messages in thread
From: jclaudio@capitol.fr @ 2004-01-13 11:00 UTC (permalink / raw)
To: pgsql-sql
Hi I'm trying to update a table column with a pl/pgsql function and a
trigger.
But I didn't managed to make it work so far.
Here's my function code :
CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '
DECLARE
id_line integer;
quantity integer;
single_price real;
total_cost real;
amort integer;
month integer;
impact real;
BEGIN
SELECT INTO id_line id_line_table FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO single_price single_price_previ FROM table WHERE id_line_table
= NEW.id_line;
SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO quantity quantity_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO month month_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO impact impact_previ FROM table WHERE id_line_table =
NEW.id_line;
IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0;
ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort);
ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:=
total_cost;
ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12
- month)*(total_cost/(amort*12)));
END IF;
END IF;
END IF;
END IF;
IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN
UPDATE table SET impact_previ = impact WHERE id_line_table =
NEW.id_line;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget
FOR EACH ROW EXECUTE PROCEDURE calcul_impact();
I always get the error :
Error SQL :
ERROR: record "new" has no field named "id_ligne"
Has anyone an idea about what's wrong ?
thanks for answering me
^ permalink raw reply [nested|flat] 6+ messages in thread
* problem with function trigger
@ 2004-01-13 13:42 jclaudio@capitol.fr
2004-01-16 11:34 ` Re: problem with function trigger Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 6+ messages in thread
From: jclaudio@capitol.fr @ 2004-01-13 13:42 UTC (permalink / raw)
To: pgsql-sql
Hi I'm trying to update a table column with a pl/pgsql function and a
trigger.
But I didn't managed to make it work so far.
Here's my function code :
CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '
DECLARE
id_line integer;
quantity integer;
single_price real;
total_cost real;
amort integer;
month integer;
impact real;
BEGIN
SELECT INTO id_line id_line_table FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO single_price single_price_previ FROM table WHERE id_line_table
= NEW.id_line;
SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO quantity quantity_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
SELECT INTO month month_previ FROM table WHERE id_line_table =
NEW.id_line;
SELECT INTO impact impact_previ FROM table WHERE id_line_table =
NEW.id_line;
IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0;
ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort);
ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:=
total_cost;
ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12
- month)*(total_cost/(amort*12)));
END IF;
END IF;
END IF;
END IF;
IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN
UPDATE table SET impact_previ = impact WHERE id_line_table =
NEW.id_line;
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget
FOR EACH ROW EXECUTE PROCEDURE calcul_impact();
I always get the error :
Error SQL :
ERROR: record "new" has no field named "id_ligne"
Has anyone an idea about what's wrong ?
thanks for answering me
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: problem with function trigger
2004-01-13 13:42 problem with function trigger jclaudio@capitol.fr
@ 2004-01-16 11:34 ` Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 6+ messages in thread
From: Christoph Haller @ 2004-01-16 11:34 UTC (permalink / raw)
To: jclaudio@capitol.fr; +Cc: pgsql-sql
>
> Hi I'm trying to update a table column with a pl/pgsql function and a
> trigger.
> But I didn't managed to make it work so far.
>
> Here's my function code :
>
> CREATE FUNCTION public.calcul_impact() RETURNS opaque AS '
>
> DECLARE
> id_line integer;
> quantity integer;
> single_price real;
> total_cost real;
> amort integer;
> month integer;
> impact real;
>
> BEGIN
>
> SELECT INTO id_line id_line_table FROM table WHERE id_line_table =
> NEW.id_line;
> SELECT INTO single_price single_price_previ FROM table WHERE id_line_table
> = NEW.id_line;
> SELECT INTO total_cost total_cost_previ FROM table WHERE id_line_table =
> NEW.id_line;
> SELECT INTO quantity quantity_previ FROM table WHERE id_line_table =
> NEW.id_line;
> SELECT INTO amort amortis FROM table WHERE id_line_table = NEW.id_line;
> SELECT INTO month month_previ FROM table WHERE id_line_table =
> NEW.id_line;
> SELECT INTO impact impact_previ FROM table WHERE id_line_table =
> NEW.id_line;
>
> IF(quantity IS NULL OR single_price IS NULL) THEN impact:= 0;
> ELSE IF(quantity >= 12) THEN impact:= (total_cost / amort);
> ELSE IF(quantity < 12 AND single_price <= 500) THEN impact:=
> total_cost;
> ELSE IF(quantity < 12 AND single_price > 500) THEN impact:= ((12
> - month)*(total_cost/(amort*12)));
> END IF;
> END IF;
> END IF;
> END IF;
>
> IF (TG_OP =''INSERT'' OR TG_OP=''UPDATE'') THEN
> UPDATE table SET impact_previ = impact WHERE id_line_table =
> NEW.id_line;
> END IF;
>
> RETURN NEW;
>
> END;
>
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER add_impact_previ BEFORE INSERT OR UPDATE ON public.budget
> FOR EACH ROW EXECUTE PROCEDURE calcul_impact();
>
> I always get the error :
>
> Error SQL :
> ERROR: record "new" has no field named "id_ligne"
>
> Has anyone an idea about what's wrong ?
>
> thanks for answering me
Could you please show the definition of table "budget".
Not sure what you're trying to achieve by these SELECT INTO ...
statements.
Regards, Christoph
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-01-16 11:34 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-07-07 11:05 Problem with function & trigger Carlo Vitolo <carlino@ilrspa.com>
2001-07-07 19:28 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-07-09 21:05 ` Carlo Vitolo <carlino@ilrspa.com>
2004-01-13 11:00 problem with function trigger jclaudio@capitol.fr
2004-01-13 13:42 problem with function trigger jclaudio@capitol.fr
2004-01-16 11:34 ` Re: problem with function trigger Christoph Haller <ch@rodos.fzk.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox