Functions are a fundamental building block in programming. They encapsulate complex logic into reusable modules, leading to cleaner, more maintainable code.
With a database function, we can combine multiple database operations into one function and then execute that as a single transaction. Most of what functions do can still be done by individual queries. However, by combining them into a function, we can take advantage of reusability, atomicity, and access control over the whole function.
The most crucial among these is atomicity. That is, a function carrying out multiple operations will either execute all or none at all. Atomicity is essential in the context of database operations since failed database operations — particularly write operations — may lead to data inconsistencies.
Moreover, database calls perform the best if done closest to the data. Database functions are executed on the database server — it is as close to data as possible.
Note that functions in a database do not necessarily need to be pure database operations. They can also be utility functions — for example, inbuilt functions in Postgres such as avg, random, count, etc.
In this lesson, we dive deep into Postgres functions and how to use Supabase to create, maintain, and execute database functions in Postgres.
