Re: Function and trigger

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
>