Today I learned a lot about PostgreSQL.
- Views can have triggers!
- Deleting such a view also deletes the trigger!
To demonstrate, let’s create a table and record, and a view that looks at our table.
create table some_table (name varchar);
insert into some_table values ('some name');
create view some_view as select name from some_table;
Now, we need a test function:
create or replace function always_null() returns trigger as $always_null$
begin
return null;
end;
$always_null$ language plpgsql;
Let create a trigger for our function:
create trigger some_table_trigger
instead of delete on some_view
for each row execute procedure always_null();
Here’s our view… with a trigger attached!
backup=# \d some_view
View "public.some_view"
Column | Type | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
name | character varying | | |
Triggers:
some_table_trigger INSTEAD OF DELETE ON some_view FOR EACH ROW EXECUTE PROCEDURE always_null()
Our trigger can be inspected (lots of interesting info omitted):
backup=# select * from information_schema.triggers;
-[ RECORD 1 ]--------------+--------------------------------
trigger_name | some_table_trigger
Drop the view, and lose the trigger:
backup=# drop view some_view;
DROP VIEW
backup=# select * from information_schema.triggers;
(0 rows)
