Re: Function and trigger

From: Tovo Rabemanantsoa <tovo(at)bordeaux(dot)inra(dot)fr>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Function and trigger
Date: 2008-12-05 13:31:08
Message-ID: 49392D1C.2090100@bordeaux.inra.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2008-12-07 21:38:19 Re: pg_dump usage of /tmp
Previous Message A. Kretschmer 2008-12-05 10:21:59 Re: Function and trigger