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.

No video of the event yet, sorry!

Stored procedures were added to PostgreSQL in version 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 but without a return value and with some subtle differences and benefits.

The main benefits of Stored procedures are the following :

  • Transaction control for allowing commit and rollback inside procedures
  • Making Oracle to PostgreSQL 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 Procedures, how to create procedure, how to use procedures and how to execute procedures using different connectors and languages. While stored procedures aren't supposed 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 procedures and how to get return values from stored procedures using Java and C programs. 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 examples of doing transaction control and using commit and rollback.

Date:
Duration:
20 min
Room:
Conference:
Postgres Conference 2020
Language:
Track:
Migration
Difficulty:
Easy