Better late than never - Stored Procedures support in PostgreSQL

Presented by:

Ahsan headshot

Ahsan Hadi

pgEdge Inc

Ahsan is a database evangelist with over 20 years of development and management experience, he is very passionate about databases and has worked with has worked with Postgres & Oracle extensively through out his career. With over 15 years of working with PostgreSQL, he has worked with companies like EDB as a Senior Director of Product Development , HighGo Software as VP of Development and Analyst Programmer with British Telecom. Ahsan is currently working as Director Support & Services with pgEdge, pgEdge is fully distributed PostgreSQL DB, optimized for the network edge and deployable across multiple cloud regions or data centers.

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)