Today I Learned

A Hashrocket project

PostgreSQL Triggers and Views ๐Ÿ˜

Today I learned a lot about PostgreSQL.

  1. Views can have triggers!
  2. 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)
Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.