PostgreSQL Triggers and Views 🐘

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)

Today I Learned

A Hashrocket project Follow on Twitter

PostgreSQL Triggers and Views 🐘