This article will explain the difference between stored functions and procedures in PostgreSQL databases. Both stored functions and procedures serve similar purposes – containing code that you want to call repeatedly – but they are slightly different, and that difference can be important depending on what you are trying to achieve.
What are Stored Functions in PostgreSQL?
Functions are containers for a repeatable set of code or SQL statements that accept parameters and return a value. Functions can be called from SQL queries, and from within other functions. Functions can contain code from any language supported by PostgreSQL, including SQL statements, Python, and JavaScript.
Functions are useful for manipulating data before it is stored or after it is retrieved, as the return value can be assigned and used in queries.
What are Stored Procedures in PostgreSQL and How is it Different to A Function?
Procedures are similar to functions, but differ in two key ways: They do not return values, and they cannot be called directly from SQL statements.
Procedures are intended to perform repeatable tasks that do not need a return value, and are useful for performing scheduled or routine tasks like populating tables, copying data, checking data, or running VACUUM operations.
Procedures handle transactions differently to functions. Functions are executed within a transaction, so that any changes made are rolled back if any part of that transaction fails. Procedures do not execute within a transaction – any commands executed up to the point of failure within a procedure will have taken effect on the database and will not be rolled back on the failure of a following step.
