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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
