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

Search Discussions

  • Andreas Kretschmer at Dec 5, 2008 at 10:22 am

    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 at Dec 8, 2008 at 9:07 am
    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
  • Tovo Rabemanantsoa at Dec 5, 2008 at 1:44 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedDec 5, '08 at 10:10a
activeDec 8, '08 at 9:07a
posts4
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase