Re: Problem after installing triggering function - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Problem after installing triggering function
Date
Msg-id 66FA36FE-146F-487D-9C94-C283B0F84301@solfertje.student.utwente.nl
Whole thread Raw
In response to Re: Problem after installing triggering function  (Yan Cheng Cheok <yccheok@yahoo.com>)
List pgsql-general
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
courseyou 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
childtable. 

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
advisehow 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!



pgsql-general by date:

Previous
From: Adrian von Bidder
Date:
Subject: Re: How to generate unique hash-type id?
Next
From: hubert depesz lubaczewski
Date:
Subject: Re: How to generate unique hash-type id?