Thread: Function and trigger
Hi, I have e problem with a function (plpgsql) and a trigger in my database. Postgres and triggers/functions are new to me... Anyway, the database is filled with larg amounts of measurements, we are talking about a couple of hundres of thousands of posts per day. I want to create a function that checks for a specific type of measurement and then copies it's value to another table. The functions looked almost like this: BEGIN IF NEW.TYPE=100 THEN INSERT INTO newtable (type,quantity) VALUES (NEW.TYPE, NEW.QUANTITY); END IF; END; I tried to run a similar function (not created for use with a trigger) on a dummy table, and it inserted the values I told it to without any errors. I know that the function is correct and shouldnt give me any errors. But when I tried to run the followin function with a trigger on the "real" table with the real data it just didnt work! The "real" table is not filled with any more data and the "newtable" isnt filled with any data either! I must have missed something very important!? Best regards and thanks in advance! Ola
am Fri, dem 05.12.2008, um 11:02:52 +0100 mailte Ola Ekedahl folgendes:
> Hi,
>
> I have e problem with a function (plpgsql) and a trigger in my database.
> Postgres and triggers/functions are new to me...
>
> Anyway, the database is filled with larg amounts of measurements, we are
> talking about a couple of hundres of thousands of posts per day.
> I want to create a function that checks for a specific type of
> measurement and then copies it's value to another table. The functions
> looked almost like this:
>
> BEGIN
>
> IF NEW.TYPE=100 THEN
> INSERT INTO newtable (type,quantity) VALUES (NEW.TYPE, NEW.QUANTITY);
> END IF;
>
> END;
Thats not a complete function. Okay, see below:
test=# create table measurements(type int, quantity int);
CREATE TABLE
test=*# create table newtable(type int, quantity int);
CREATE TABLE
test=*# create function trg_newtable() returns trigger as $$begin IF NEW.TYPE=100 THEN INSERT INTO newtable
(type,quantity)VALUES (NEW.TYPE, NEW.QUANTITY); END IF; return new; END; $$language plpgsql;
CREATE FUNCTION
test=*# create trigger mytrigger before insert on measurements for each row execute procedure trg_newtable();
CREATE TRIGGER
test=*# insert into measurements values (1,1);
INSERT 0 1
test=*# insert into measurements values (100,100);
INSERT 0 1
test=*# select * from measurements;
type | quantity
------+----------
1 | 1
100 | 100
(2 rows)
test=*# select * from newtable;
type | quantity
------+----------
100 | 100
(1 row)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Ola Ekedahl wrote: > Hi, > > I have e problem with a function (plpgsql) and a trigger in my database. > Postgres and triggers/functions are new to me... > > Anyway, the database is filled with larg amounts of measurements, we are > talking about a couple of hundres of thousands of posts per day. > I want to create a function that checks for a specific type of > measurement and then copies it's value to another table. The functions > looked almost like this: > > BEGIN > > IF NEW.TYPE=100 THEN > INSERT INTO newtable (type,quantity) VALUES (NEW.TYPE, NEW.QUANTITY); > END IF; > > END; > > I tried to run a similar function (not created for use with a trigger) > on a dummy table, and it inserted the values I told it to without any > errors. I know that the function is correct and shouldnt give me any > errors. > > But when I tried to run the followin function with a trigger on the > "real" table with the real data it just didnt work! The "real" table is > not filled with any more data and the "newtable" isnt filled with any > data either! > > I must have missed something very important!? > > Best regards and thanks in advance! > Ola > Hi, did you tried something like this ? CREATE OR REPLACE FUNCTION trig_insert_flux() RETURNS "trigger" AS $BODY$DECLARE IF NEW.TYPE=100 THEN INSERT INTO newtable (type,quantity) VALUES (NEW.TYPE, NEW.QUANTITY); END IF; BEGIN END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; And after on your table : CREATE TRIGGER trig_insert_flux AFTER (or BEFORE, it depends) INSERT ON firstable FOR EACH ROW EXECUTE PROCEDURE trig_insert_flux(); -- Tovo J. RABEMANANTSOA INRA - UR1263 EPHYSE 71, Av. Edouard Bourlaux F-33140 Villenave d'Ornon - France Téléphone : +33 5 57 12 24 09
Hi, Thanks for the reply! I tried the exact same "code" you posted, and on the newly created tables it works! But when I make a trigger on the "real" table with the real incoming data it wont work! No data is filled in the either table... When I delete the trigger, the data is posted into the measutements table again. I cant figure out why. Could it be because it lags behind, its to much data for it to handle? On the other hand, if I do a notify instead of an insert, it seems to work ok. Any more ideas? Best regards Ola A. Kretschmer skrev: > am Fri, dem 05.12.2008, um 11:02:52 +0100 mailte Ola Ekedahl folgendes: > >> Hi, >> >> I have e problem with a function (plpgsql) and a trigger in my database. >> Postgres and triggers/functions are new to me... >> >> Anyway, the database is filled with larg amounts of measurements, we are >> talking about a couple of hundres of thousands of posts per day. >> I want to create a function that checks for a specific type of >> measurement and then copies it's value to another table. The functions >> looked almost like this: >> >> BEGIN >> >> IF NEW.TYPE=100 THEN >> INSERT INTO newtable (type,quantity) VALUES (NEW.TYPE, NEW.QUANTITY); >> END IF; >> >> END; >> > > > Thats not a complete function. Okay, see below: > > test=# create table measurements(type int, quantity int); > CREATE TABLE > test=*# create table newtable(type int, quantity int); > CREATE TABLE > test=*# create function trg_newtable() returns trigger as $$begin IF NEW.TYPE=100 THEN INSERT INTO newtable (type,quantity)VALUES (NEW.TYPE, NEW.QUANTITY); END IF; return new; END; $$language plpgsql; > CREATE FUNCTION > test=*# create trigger mytrigger before insert on measurements for each row execute procedure trg_newtable(); > CREATE TRIGGER > test=*# insert into measurements values (1,1); > INSERT 0 1 > test=*# insert into measurements values (100,100); > INSERT 0 1 > test=*# select * from measurements; > type | quantity > ------+---------- > 1 | 1 > 100 | 100 > (2 rows) > > test=*# select * from newtable; > type | quantity > ------+---------- > 100 | 100 > (1 row) > > > > Andreas >