Re: Function and trigger

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