How To Dev

Introduction When using older versions of Postgres, only Functions were available. However, from version 11 onwards, Postgres has officially added support for Procedures to address the limitations that Functions could not overcome. While their implementations are quite similar, they possess fundamental core differences: Transaction Control The biggest difference is that a Procedure allows you to have full control over transactions. You can use COMMIT or ROLLBACK directly inside the body of the procedure. This is extremely useful when you need to process large amounts of data in batches without worrying about memory overflow or locking tables for too long. Conversely, a Function cannot contain COMMIT or ROLLBACK. The entire Function must execute within a single transaction. If a command at the end of the function fails, all previous commands are completely rolled back. Return Value Using a Function requires returning a certain value, even if it is just void, a single value, or a TABLE d...

Introduction Functions are an extremely important concept in database-side programming, which are a collection of SQL or PL/pgSQL statements encapsulated into a single block of code that can be reused multiple times It acts like a function in other programming languages such as JavaScript and Python and it only runs when explicitly invoked using a SELECT or CALL statement or when nested inside another function. Use cases Functions in PostgreSQL are highly flexible and can be applied to many real-world problems in programming, typically serving the following main purposes: Encapsulation Instead of forcing the backend application to send a long SQL query spanning hundreds of lines with multiple JOIN and GROUP BY clauses, you can hide all that complexity inside a Function. Examples include revenue reports and performance statistics. Data Transformation This is ideal when you need to perform mathematical calculations, process strings, or execute complex IF/ELSE logic on data before returni...