In this short Postgres tutorial you’ll learn the differences between choosing a view or stored procedure to save a SELECT query in PostgreSQL.
Both PostgreSQL views and stored procedures provide a way of saving your SELECT queries in the database for future reference.
What’s the difference between them?
Stored procedures
- Can be more flexible as you can pass arguments into them
- Can contain multiple queries, loops, or IF/ELSE statements
- Can perform modifications to existing tables
- Can’t be used as building blocks for larger queries
- Can’t be used as a target of INSERT, UPDATE or DELETE
Views
- Don’t accept parameters
- Can’t be used to perform modifications to existing tables
- Can’t contain more than a single SELECT query
- Can be used as a part of a larger query
- Can be used to alter the data of the underlying table (INSERT, UPDATE or DELETE)