Re: [GENERAL] Problem after installing triggering function

[Yan Cheng Cheok]

To reproduce the problem, here is some simple steps to follow :

(1) create database named "tutorial"

(2) perform the following SQL query :

CREATE TABLE impressions_by_day (
        advertiser_id SERIAL NOT NULL,
        day DATE NOT NULL DEFAULT CURRENT_DATE,
        impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, day)
);

CREATE OR REPLACE FUNCTION insert_table()
  RETURNS void AS
$BODY$DECLARE
    _impressions_by_day impressions_by_day;
BEGIN
    INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING  * INTO 
_impressions_by_day;

    RAISE NOTICE 'After insert, the returned advertiser_id is %', 
_impressions_by_day.advertiser_id;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;

(3) create database named "tutorial_partition"

(4)  perform the following SQL query :

CREATE TABLE impressions_by_day (
        advertiser_id SERIAL NOT NULL,
        day DATE NOT NULL DEFAULT CURRENT_DATE,
        impressions INTEGER NOT NULL,
        PRIMARY KEY (advertiser_id, day)
);

CREATE OR REPLACE FUNCTION insert_table()
  RETURNS void AS
$BODY$DECLARE
    _impressions_by_day impressions_by_day;
BEGIN
    INSERT INTO impressions_by_day(impressions ) VALUES(888) RETURNING  * INTO 
_impressions_by_day;

    RAISE NOTICE 'After insert, the returned advertiser_id is %', 
_impressions_by_day.advertiser_id;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION insert_table() OWNER TO postgres;

CREATE TABLE impressions_by_day_y2010m1ms2 (
        PRIMARY KEY (advertiser_id, day), 
        CHECK ( day >= DATE '2010-01-01' AND day < DATE '2010-03-01' )
) INHERITS (impressions_by_day);


CREATE INDEX impressions_by_day_y2010m1ms2_index ON 
impressions_by_day_y2010m1ms2 (day);


CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
        IF ( NEW.day >= DATE '2010-01-01' AND NEW.day < DATE '2010-03-01' ) 
THEN 
                INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*); 
        ELSE
                RAISE EXCEPTION 'Date out of range.  Something wrong with the 
impressions_by_day_insert_trigger() function!';
        END IF;
        RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_impressions_by_day_trigger 
        BEFORE INSERT ON impressions_by_day 
        FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();

(5) execute

SELECT * FROM insert_table() on tutorial

We get

NOTICE:  After insert, the returned advertiser_id is 1

(6) execute

SELECT * FROM insert_table() on tutorial_partition

We get

NOTICE:  After insert, the returned advertiser_id is <NULL>



How is it possible to get advertiser_id is 1 too, in tutorial_partition?

Thanks!
Cheok


      


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general