Better late than never - Stored Procedures support in PostgreSQL

Presented by:

096f193be016da51f083a593109491cc

Ahsan Hadi

HighGo Software Inc

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.

Finally we have stored procedures in PostgreSQL 11, better late than 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 differences 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 usage 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, 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.

Date:
2021 April 28 13:00 EDT
Duration:
1 h
Room:
Online
Conference:
2021 Postgres Conference Webinars
Language:
Track:
Dev
Difficulty:
Easy
Requires Registration:
Yes (Registered: 110)