Problem after installing triggering function

I have a stored procedure execute the following code :

INSERT INTO unit(fk_lot_id, cycle)
VALUES(_lotID, _cycle) RETURNING * INTO _unit;
raise notice 'AFTER INSERT INTO UNIT, _unit.unit_id = %', _unit.unit_id ;

unit_id column, is an auto generated primary key. I will always get a non-null value.

However, after I install a trigger function, and create a table named unit_0 inherit from table unit,

NOTICE: AFTER INSERT INTO UNIT, _unit.unit_id = <NULL>

will be printed.

The following is the trigger function :

within trigger function, it able to detect unit table (represented by NEW) is having unit_id 28.

why outside trigger function, I will get null?

Thanks!

DECLARE
unit_table_index bigint;
low bigint;
high bigint;
unit_table_name text;
BEGIN
unit_table_index = NEW.unit_id >> 20;
-- 2^20 = 1048576
low = unit_table_index * 1048576;
high = low + 1048575;
unit_table_name = 'unit_' || unit_table_index;

-- code to dynamically create unit_0, unit_1, unit_2 ...
IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = unit_table_name) THEN
EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
(
PRIMARY KEY (unit_id),
CHECK (unit_id between ' || low || ' and ' || high || '),

CONSTRAINT fk_lot_id_' || unit_table_index || ' FOREIGN KEY (fk_lot_id)
REFERENCES lot (lot_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
) INHERITS (unit);';

EXECUTE 'CREATE INDEX idx_unit_id_' || unit_table_index ||
' ON ' || quote_ident(unit_table_name) ||
' USING btree
(unit_id);';

EXECUTE 'CREATE INDEX idx_fk_lot_id_' || unit_table_index ||
' ON ' || quote_ident(unit_table_name) ||
' USING btree
(fk_lot_id);';
END IF;

-- NOTICE: IN unit_insert_trigger, table is unit_0
-- NOTICE: IN unit_insert_trigger, NEW.unit_id is 28
raise notice 'IN unit_insert_trigger, table is %', unit_table_name;
raise notice 'IN unit_insert_trigger, NEW.unit_id is %', NEW.unit_id;

EXECUTE 'INSERT INTO ' || quote_ident(unit_table_name) ||
'(unit_id, fk_lot_id, cycle) VALUES (' ||
NEW.unit_id || ',' || NEW.fk_lot_id || ',' || NEW.cycle || ')';

RETURN NULL;
END;

Thanks and Regards
Yan Cheng CHEOK

Yan Cheng Cheok <yccheok(at)yahoo(dot)com> writes:
> I have a stored procedure execute the following code :
> INSERT INTO unit(fk_lot_id, cycle)
> VALUES(_lotID, _cycle) RETURNING * INTO _unit;
> raise notice 'AFTER INSERT INTO UNIT, _unit.unit_id = %', _unit.unit_id ;

> unit_id column, is an auto generated primary key. I will always get a non-null value.

> However, after I install a trigger function, and create a table named unit_0 inherit from table unit,

> NOTICE: AFTER INSERT INTO UNIT, _unit.unit_id = <NULL>

> will be printed.

If you installed it as a BEFORE trigger, the problem is here:

> RETURN NULL;

That's suppressing the INSERT action.

regards, tom lane

Thanks and Regards
Yan Cheng CHEOK

--- On Thu, 1/28/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> Subject: Re: [GENERAL] Problem after installing triggering function
> To: "Yan Cheng Cheok" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> Date: Thursday, January 28, 2010, 12:34 AM
> Yan Cheng Cheok <yccheok(at)yahoo(dot)com>
> writes:
> > I have a stored procedure execute the following code
> :
> >     INSERT INTO unit(fk_lot_id,
> cycle)
> >     VALUES(_lotID, _cycle)
> RETURNING  * INTO _unit;
> >     raise notice 'AFTER INSERT
> INTO UNIT,  _unit.unit_id = %', _unit.unit_id ;
>
> > unit_id column, is an auto generated primary key. I
> will always get a non-null value.
>
> > However, after I install a trigger function, and
> create a table named unit_0 inherit from table unit,
>
> > NOTICE:  AFTER INSERT INTO UNIT, 
> _unit.unit_id = <NULL>
>
> > will be printed.
>
> If you installed it as a BEFORE trigger, the problem is
> here:
>    

You are right. I am inserting BEFORE trigger.

CREATE TRIGGER insert_unit_trigger
BEFORE INSERT ON unit
FOR EACH ROW EXECUTE PROCEDURE unit_insert_trigger();

> >     RETURN NULL;
>
> That's suppressing the INSERT action.
>
>

But... I am not implementing table partition. I want to ensure my parent table "unit" is empty, and "unit_0" is being filled. But, all my query can be performed through parent table "unit".

I am referring to http://www.if-not-true-then-false.com/2009/11/howto-create-postgresql-table-partitioning-part-1/

       
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>