hi.
CREATE FUNCTION dummy_trigger() RETURNS TRIGGER AS $$
BEGIN
RETURN NULL;
END
$$ language plpgsql;
create table main_table(a int);
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH ROW
WHEN (new.a > 0)
EXECUTE PROCEDURE dummy_trigger();
ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --error
ALTER TABLE main_table DROP COLUMN a; --error
Dropping a column or changing its data type will fail if the column is
referenced in a trigger’s WHEN clause, that's the current behavior.
I think we should expand that to a whole-row reference WHEN clause in trigger.
DROP TRIGGER before_ins_stmt_trig ON main_table;
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH ROW
WHEN (new is null)
EXECUTE PROCEDURE dummy_trigger();
ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8; --expect to error
ALTER TABLE main_table DROP COLUMN a; --expect to error
new summary:
For (constraints, indexes, policies, triggers) that contain whole-row
references:
ALTER TABLE DROP COLUMN [CASCADE] will drop these objects too.
ALTER COLUMN SET DATA TYPE will error out because whole-row–dependent objects
exist.
--
jian
https://www.enterprisedb.com/
