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)