Better late then never - Stored Procedures in PG 11
Ahsan Hadi is a VP of Development with HighGo Software Inc. Prior to HighGo Software, Ahsan had worked at EnterpriseDB as a Senior Director of Product Development, Ahsan worked with EDB for 15 years. Ahsan has vast experience with Postgres and has lead the development team at EDB for building Oracle compatible layer for EDB's Postgres Advanced server. Ahsan has also spent number of years working with development team for adding Horizontal scalability and sharding to Postgres.
Prior to EDB, Ahsan worked for Fusion Tech as a Senior Project Manager. Ahsan lead the team that developed java based job factory responsible for placing items on shelfs at big stores. Prior to Fusion, Ahsan worked at British Telecom as a Analyst/Programmer and developed web based database application.
Ahsan joined HighGo Software Inc in April 2019 and is leading the development teams based in multiple Geo’s, the primary responsibility is community based Postgres development and also developing HighGo Postgres server.
No video of the event yet, sorry!
Finally we have stroed procedures in PostgreSQL 11, better late then never.
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 differneces and benefits.
The main benefits of Stored procedure are the following :
- Transaction control for allowing commit and rollback inside procedures
- Making Oracle to PostgrSQL migration easier, the stored procedure usuage is very popular in Oracle
- Stored functions and procedure are mostly similar so it is easy for end-users
The talk will provide a detailed insight into using Stored Procedure with PG 11, 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 usuage of in and inout parameters with stored procedure and how to get return values from stored procedures using Java and C program. 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.
- 20 min
- Postgres Conference 2020