Conference Schedule - PGConf NYC 2021

Power of Stored Procedures in PostgreSQL

Level: Intermediate

The support for stored procedures was added in PostgreSQL 11, better late than never. Stored procedures are widely used for underpinning business logic in the database and creating reusable programs for business operations.

PostgreSQL versions prior to 11 only supported functions, the support for stored procedures was added in PostgreSQL 11. It is added as a new schema object which is similar to functions and but without a return value and with some subtle differences and benefits.

The main benefits of Stored procedure are the following :

The talk will provide a detailed insight into using Stored Procedure with PostgreSQL, how to create procedure, how to use procedures and how to execute procedures using different connectors and languages. While stored procedure aren't suppose to have a return value, we will get into how we can use them to get return values. In this talk you will learn the usage of IN and INOUT parameters with stored procedure and how to get return values from stored procedures using Java, C and other languages.

We will also go through some examples of how to migrate stored procedures from Oracle into Stored procedures in PostgreSQL. One key benefit of stored procedure is transaction control, we will run through use cases and example of doing transaction control and using commit and rollback.


Ahsan Hadi