Problem after installing triggering function

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

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


      


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