Hi,
It is been observed that when we define the generated columns in WHEN
condition for BEFORE EACH ROW trigger then server throw an error from
CreateTrigger().
e.g:
create table bar(a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2)
STORED);
CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $$
BEGIN
NEW.b = 10;
raise notice 'Before row trigger';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
postgres@78049=#CREATE TRIGGER bar_trigger
BEFORE INSERT ON bar
FOR EACH ROW
*WHEN (NEW.b < 8)*
EXECUTE FUNCTION test();
2019-10-03 19:25:29.945 IST [78049] ERROR: *BEFORE trigger's WHEN
condition cannot reference NEW generated columns* at character 68
2019-10-03 19:25:29.945 IST [78049] DETAIL: Column "b" is a generated
column.
2019-10-03 19:25:29.945 IST [78049] STATEMENT: CREATE TRIGGER bar_trigger
BEFORE INSERT ON bar
FOR EACH ROW
WHEN (NEW.b < 8)
EXECUTE FUNCTION test();
ERROR: BEFORE trigger's WHEN condition cannot reference NEW generated
columns
LINE 4: WHEN (NEW.b < 8)
^
DETAIL: Column "b" is a generated column.
whereas, for identity columns, server allows us to create trigger for same
and trigger gets invoked as defined. Is this behavior expected? or we need
to restrict the identity columns in such scenario because anyone one
override the identity column value in trigger.
e.g:
create table foo(no int, id int generated always as identity);
CREATE OR REPLACE FUNCTION test() RETURNS trigger AS $$
BEGIN
NEW.id = 10;
raise notice 'Before row trigger';
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER foo_trigger
BEFORE INSERT ON foo
FOR EACH ROW
WHEN (NEW.id < 8)
EXECUTE FUNCTION test();
postgres@78049=#insert into foo values(1);
*NOTICE: Before row trigger*
INSERT 0 1
postgres@78049=#select * from foo;
no | id
----+----
1 | *10*
(1 row)
Thoughts?
--
--
Thanks & Regards,
Suraj kharage,
EnterpriseDB Corporation,
The Postgres Database Company.
