Re: Problem after installing triggering function

On 29 Jan 2010, at 2:06, Yan Cheng Cheok wrote:

> 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;

...

> 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 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();

...

> (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?

You didn't actually insert any data in the table you're querying as you return NULL in the BEFORE INSERT trigger, so of course you get NULL back for results.
If you want to get the row back that got inserted into the child table as a side effect then you will have to query the child table.

The case you're showing here obviously doesn't have any purpose other then to show what's going on, so it's hard to advise how to work around this problem. You could probably solve your situation by creating a trigger on each child table, it depends on what needs to be done.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4b62cac310751585411885!