3 ms·

short answer: no, not slow. longer answer: you can do some really cool things with triggers in postgres, my favorite is what I like to refer to as "writeable v

short answer: no, not slow. longer answer: you can do some really cool things with triggers in postgres, my favorite is what I like to refer to as "writeable views" - https://legitimatesounding.com/blog/stupid_postgresql_tricks:_writable_views.html https://legitimatesounding.com/blog/stupid_postgresql_tricks... (2010)

mustardo 8y ago
Interesting... I can imagine uttering some four letter words while trying to figure out what was going on if I was to take over maintaining a system that used this (principal of least surprise)
- jerrysievert 8y ago
  probably the same four letter words that were uttered when it was determined that something wild and crazy was needed in order to support the requirements. documentation is key.
- gaius 8y ago
  Writing through a view is totally unremarkable and has been for a long time. It’s quite common in Oracle and SQL Server lands. You can update via a CTE too, very easy, very elegant.
- Jweb_Guru 8y ago
  Counterpoint: using writable views was one of the best decisions my company ever made and no one regretted it. It is much easier to comprehend than ordinary triggers (specifically, the automated version).
jasonmp85 8y ago
FWIW since 9.6 this behavior is automatic for views with simple column references against a single relation
- aeontech 8y ago
  What?! That is amazing!
jlmorton 8y ago
Fantastic feature that makes it trivial to write several different types of backwards compatible DDL changes. For instance, you want to rename a column? Add the new column, rename the existing table, add a view in its place with both the old column name and the new column name, with an INSTEADOF trigger to update the base table. Postgres lets you do this all in a transaction, so the entire operation is atomic.
michelpp 8y ago
Writable views also work perfectly with PostgREST, if you're view is auto-updateable or has an INSTEAD OF trigger than you can POST/PATCH/DELETE via the view to mutate.
aeontech 8y ago
This exactly what I need this week. Thank you!

mustardo 8y ago

Interesting... I can imagine uttering some four letter words while trying to figure out what was going on if I was to take over maintaining a system that used this (principal of least surprise)

jerrysievert 8y ago
probably the same four letter words that were uttered when it was determined that something wild and crazy was needed in order to support the requirements. documentation is key.
gaius 8y ago
Writing through a view is totally unremarkable and has been for a long time. It’s quite common in Oracle and SQL Server lands. You can update via a CTE too, very easy, very elegant.
Jweb_Guru 8y ago
Counterpoint: using writable views was one of the best decisions my company ever made and no one regretted it. It is much easier to comprehend than ordinary triggers (specifically, the automated version).

jasonmp85 8y ago

FWIW since 9.6 this behavior is automatic for views with simple column references against a single relation

aeontech 8y ago
What?! That is amazing!

jlmorton 8y ago

Fantastic feature that makes it trivial to write several different types of backwards compatible DDL changes. For instance, you want to rename a column? Add the new column, rename the existing table, add a view in its place with both the old column name and the new column name, with an INSTEADOF trigger to update the base table. Postgres lets you do this all in a transaction, so the entire operation is atomic.

michelpp 8y ago

Writable views also work perfectly with PostgREST, if you're view is auto-updateable or has an INSTEAD OF trigger than you can POST/PATCH/DELETE via the view to mutate.

aeontech 8y ago

This exactly what I need this week. Thank you!