The Developer Space

Developer's Cafe

  • Cloud
  • Database
  • Open Source
  • Programming
  • Web Dev
  • Mobile
  • Security
  • QuickRef
  • Home
  • Database
  • PostgreSQL finally gets Stored Procedures

PostgreSQL finally gets Stored Procedures

Shameel Ahmed - CodeProject, Database, PostgreSQL
September 12, 2019September 21, 2019 No Comments
0 0
0 0
Read Time3 Minute, 42 Second

Table of Contents

  • Introduction
    • The Problem
  • Stored Procedures
    • Overloaded Stored Procedures
    • Nested Stored Procedures
    • Stored Procedure vs Function
    • Why Stored Procedures?
    • Creating a new Stored Procedure
    • Execute a Stored Procedure
  • Conclusion
  • More Reading
  • Share

Introduction

PostgreSQL has traditionally supported User Defined Functions since a long time. Functions allow us to store and execute procedural code repeatedly. The downside with Functions is its lack of support for Transactions in the Function body, the entire Function body is executed within an implicit transaction.

The Problem

Lack of transaction support in Functions meant that if multiple sets of statements had to be executed, each within a transaction, you had to write one Function for each.

When migrating RDBMS like Oracle or SQL Server to PostgreSQL 10.x or older, Stored Procedures had to be re-written as PostgreSQL Functions. If the Stored Procedures used Transaction logic within them, they had to be rewritten as multiple PostgreSQL Functions, one for each transaction. This was a very difficult and error-prone task since it involved manual rewriting of code and thorough Unit testing covering all use-cases. It was difficult to ensure that the PostgreSQL Functions did exactly the same thing as the Stored Procedures they were rewritten from.

Stored Procedures

PostgreSQL 11.5. released on 10/18/2018. includes PROCEDURE as a new Schema object. A stored procedure is created using the CREATE PROCEDURE statement. You can also use the CREATE OR REPLACE syntax similar to Functions. You can start multiple new transactions, commit or rollback them within a Stored Procedure.

Overloaded Stored Procedures

Stored Procedures can be overloaded similar to Functions, i.e. you can create multiple Stored Procedures with different parameters. When a Stored Procedure call is made, procedure resolution is decided on best match of parameters, as with Functions.

Nested Stored Procedures

Stored Procedures can also be nested, i.e. you can call a Stored Procedure from another Stored Procedure, similar to Functions.

Stored Procedure vs Function

Though Stored Procedures and Functions look very similar, there are few fundamental differences:

FeatureStored ProcedureFunction
Use in an expressionNoYes
Return a valueNoYes
Return values as OUT parametersYesNo
Return a single result setYesYes (as a table function)
Return multiple result setsYesNo

Why Stored Procedures?

  1. Stored Procedures allows transaction control through multiple COMMIT and ROLLBACK statements.
  2. Makes migration of Oracle and SQL Server databases to PostgreSQL very easy since Stored Procedures can be rewritten as is without the need to rewrite them as multiple functions.
  3. The syntax of CREATE PROCEDURE is very similar to CREATE FUNCTION and the learning curve is very narrow.

Creating a new Stored Procedure

To create a new Stored Procedure, you use the CREATE PROCEDURE statement. It is very similar to CREATE FUNCTION statement except that it does not have the RETURNS clause. Here’s an example.

CREATE OR REPLACE PROCEDURE TestProcedure() 
 LANGUAGE plpgsql 
 AS $$
 DECLARE
 BEGIN
     CREATE TABLE table1 (id int, name text);
     INSERT INTO  table1 VALUES (1, "Andy");
     COMMIT;
     CREATE TABLE table2 (id int, name text);
     INSERT INTO  table2 VALUES (1, "Brian");
     ROLLBACK;
 END $$; 

For the complete syntax of CREATE PROCEDURE statement, check this documentation page.

Execute a Stored Procedure

You can execute a Stored Procedure using the CALL statement

CALL TestProcedure();

Conclusion

Stored Procedure support has been one of the most awaited features in PostgreSQL especially given the fact that other Database platforms have been having Stored Procedures for ages. The most important advantage of this new feature is that it will speed up migration of other databases to PostgreSQL.

More Reading

Migrating your SQL Server Workloads to PostgreSQL
PostgreSQL 12: New Features and Enhancements
Check PostgreSQL Edition

Share

Facebook
Twitter
LinkedIn
Email

Post navigation

Check PostgreSQL Edition
PostgreSQL 12: New Features and Enhancements

Related Articles

Migrate your SQL Server Workloads to PostgreSQL: Quick Reference: Second Edition

Book: Migrate your SQL Server Workloads to PostgreSQL: Quick Reference – Second Edition

Shameel Ahmed
October 18, 2022October 22, 2022 1 Comment

How to fix Redis Error “ERR DEBUG command not allowed.”

Shameel Ahmed
July 4, 2022July 5, 2022 No Comments

Migrate MySQL table data to MongoDB collections using Python

Shameel Ahmed
June 18, 2021June 25, 2021 1 Comment

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Contents

  • Introduction
    • The Problem
  • Stored Procedures
    • Overloaded Stored Procedures
    • Nested Stored Procedures
    • Stored Procedure vs Function
    • Why Stored Procedures?
    • Creating a new Stored Procedure
    • Execute a Stored Procedure
  • Conclusion
  • More Reading
  • Share

Categories

.NET Architecture Artificial Intelligence ASP.NET AWS Azure Books C# Career Cloud CodeProject Conversational Bots Database Data Security Facade IDEs Java Mobile MongoDB MySQL Open Source Patterns PostgreSQL Programming Python Redis Security SQL Server Tools Uncategorized Web Development Windows Phone

Recent Posts

  • How to build a REST API using Amazon API Gateway to invoke OpenAI APIs March 11, 2023
  • How to fix “‘StreamingBody’ object is not subscriptable” in AWS Lambda boto3 February 12, 2023
  • How to invoke OpenAI APIs from AWS Lambda functions February 5, 2023
  • Developer to Architect Series (Red Hat Enable Architect) January 16, 2023
  • Can ChatGPT replace Google Search? January 11, 2023

Archives

  • March 2023 (1)
  • February 2023 (2)
  • January 2023 (2)
  • December 2022 (1)
  • October 2022 (1)
  • July 2022 (2)
  • February 2022 (1)
  • November 2021 (1)
  • July 2021 (1)
  • June 2021 (1)
  • September 2020 (1)
  • May 2020 (2)
  • April 2020 (1)
  • October 2019 (1)
  • September 2019 (4)
  • July 2019 (2)
  • May 2018 (1)
  • September 2017 (1)
  • April 2017 (1)
  • April 2014 (1)
  • August 2011 (1)
  • June 2009 (1)
Copyright 2022. The Developer Space | Theme: OMag by LilyTurf Themes
  • DbStudio
  • Re:Link
  • shameel.net
  • Privacy Policy
We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are as essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
SAVE & ACCEPT