Re: Function and trigger

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