Create a Stored Procedure in PostgreSQL

What are Stored Procedures in PostgreSQL?

In PostgreSQL, a stored procedure is like a set of instructions that you create and save inside the database itself. Just like a recipe that guides you through cooking a specific dish, a stored procedure guides the database on how to perform a specific task.

But why do we need Stored Procedures? Imagine you have a complex task that needs to be done repeatedly, like updating multiple rows of data, calculating some values, filtering the data, aggregating the values, or performing several database operations together. Instead of writing the same code for a task every time you need it, you can create a stored procedure. Easy right?

Think of stored procedures in PostgreSQL like functions in programming languages, both are used to avoid repetition and using the same piece of code for repeated use.

CREATE OR REPLACE Stored Procedures in PostgreSQL

In PostgreSQL, CREATE OR REPLACE PROCEDURE creates a new procedure definition or replaces an already existing procedure with a new definition.

Syntax for Creating or Replacing Stored Procedures in PostgreSQL

Syntax Explanation

• CREATE [OR REPLACE] PROCEDURE:
  This SQL statement is used to create a procedure or replace an already existing procedure with the same name. The CREATE OR REPLACE keyword ensures that a new procedure will be created, irrespective of a procedure already existing with the same name. The OR REPLACE part of the statement is optional, avoided in case you don't want to replace an already existing procedure by mistake.
• procedure_name:
  The name of the procedure you are creating.
• Procedure parameters:
  If the procedure needs to take input values, you can list them inside the parentheses after the procedure name. These parameters will act as placeholders when you pass arguments to the procedure while calling it.
• AS $$ ... $$:
  The code block between the AS $$ and $$ keywords is the body of the stored procedure where you write the SQL statements and procedural code.
• DECLARE:
  This keyword is used to define variables. Each variable should have a name and type.
• BEGIN ... END:
  The BEGIN keyword marks the beginning of the procedure's main code block and the END keyword marks the end of the procedure's main code block.
• LANGUAGE plpgsql:
  This line is used to specify the procedural language for the stored procedure. Here, we are using plpgsql, which stands for PL/pgSQL, the procedural language for PostgreSQL.

Creating a Stored Procedure

Let's say you want to create a stored procedure that calculates the average marks of students in a classroom of a specified subject.

In the above code, we are creating a new procedure of the name calculate_avg_marks. It has a single parameter subject of type text, used to pass the subject name we want to calculate the average marks of.
Then we declare a variable named avg_marks of type numeric that is used to store the average marks of students in a particular subject. The resulting value of the select statement is then passed to the avg_marks variable. In the end, the output is printed.

Calling a Stored Procedure in PostgreSQL

Having gained the essential knowledge to create or replace procedures in PostgreSQL, we can take it a step further by learning how to call and utilize these procedures effectively.

Syntax to Call Stored Procedures in PostgreSQL

The CALL keyword is used to call stored procedures in PostgreSQL.

Calling the Stored Procedure that we Created in the Previous Section

In the above code, we are calling the calculate_avg_marks() procedure to calculate the average marks of students in mathematics subject.

Stored Procedures in PostgreSQL Example

First, let's define a sample dataset on which we will demonstrate different ways of using stored procedures in PostgreSQL in different examples.

Let's assume we have a table called Employees with the following structure:

Table name: Employees

Now we will create this table and populate it with some data to play with.

The resultant table of this code will be:

Inserting Data Using a Stored Procedure

In this example, we will insert data into the Employees table using a stored procedure.

Stored procedure code:

Calling stored procedure:

Output:

No output will be displayed, but the Employees table will have a new row with the inserted data.

Displaying a Message on the Screen

In this example, we will display a message using a stored procedure.

Stored procedure code:

Calling stored procedure:

Output:

A message will be displayed on the screen.

Using Transaction Control

In this example, we will use a stored procedure to update data of the Employees table while ensuring the ACID compliance of the transaction (transaction control).

Stored procedure code:

Calling stored procedure:

Output:

No output will be displayed, but the first row of the Employees table's salary is updated to a different value.

Using Column's Data Types

In this example, we will use the %TYPE notation after table_name.attribute to infer the data type of the attribute (column).

Stored procedure code:

Calling stored procedure:

Output:

The output of the above code will be the following message:

Raising Notices, Warnings, and Info Messages

Here's an example of a PostgreSQL stored procedure that demonstrates raising notices, warnings, and info messages.

Stored procedure code:

Calling stored procedure:

Output:

The output of the above code will be the following:

Raising Exceptions

In this example, we will code a stored procedure to raise an exception.

If any employee in the table has a salary less than the threshold salary, an exception will be raised!

Stored procedure code:

Calling stored procedure:

Output:

An exception is raised because we found that one or more employees are being paid less than the threshold salary.

Traversing Values in a Table Using a "for" Loop

In this example, we will traverse values in a table using a for loop inside a PostgreSQL stored procedure.

Stored procedure code:

Calling stored procedure:

Output:

We are traversing in the table Employees using a for loop and raising a notice for each row's details.

Setting Configuration Parameters

In this example, we will be setting configuration parameters using a PostgreSQL stored procedure.

Stored procedure code:

Calling stored procedure:

Output:

In the above code, the set_config_param() procedure sets the search_path configuration parameter to public for the current session using dynamic SQL with the EXECUTE statement. It then raises a notice message indicating that the configuration parameter search_path has been set to public.

PostgreSQL Stored Procedures vs Functions

Now that you know what are stored procedures in PostgreSQL (hopefully), it's high time we discuss their differences from functions to avoid any confusion.

Stored procedures in PostgreSQL are like powerful multi-taskers, capable of handling complex calculations and operations. They execute a series of SQL statements and procedural logic.

On the other hand, functions in PostgreSQL are like mini wizards for quick calculations, data transformations, and manipulations. They work like super-quick calculators, returning either a single value or a set of values.

Let us delve deeper into their differences. Shall we?

While it is important to know the differences between stored procedures and functions in PostgreSQL, it should be kept in mind that both stored procedures and functions have their specific use cases, and choosing the appropriate one depends on the requirements of the task you want to achieve.

Advantages and Disadvantages of Using Stored Procedures in PostgreSQL

Like any other tool, stored procedures come with their own set of strengths and weaknesses. Let's embark on a journey to explore the advantages and disadvantages of using stored procedures in PostgreSQL so that you can make informed decisions for your database adventures!

Advantages of Using Stored Procedures in PostgreSQL

1. Enhanced Performance:
   Stored procedures can save a lot of time! By residing on the server and being precompiled, they reduce the overhead of parsing SQL statements, resulting in faster execution and improved overall performance.
2. Modularity and Reusability:
   Keeps your database castle tidy! Stored procedures allow you to break down complex tasks into smaller, reusable units of code, promoting modularity and reducing code redundancy.
3. Data Security and Access Control:
   Stored procedures grant fine-grained control over data access. You can specify who can execute which procedures, enhancing data security.
4. Consistency with Transactions:
   Say goodbye to database chaos! Stored procedures enable you to define transactions, ensuring that in a series of operations either all succeed or none at all, preventing incomplete transactions. This helps maintain data integrity and consistency.
5. Efficient Network Usage:
   Since stored procedures are executed on the server, only the necessary results or data are sent to the client, optimizing network efficiency.

Disadvantages of Using Stored Procedures in PostgreSQL

1. Complexity Concerns:
   Developing and maintaining stored procedures can be a complex and intricate thing to pull off, as using too many stored procedures where they are not even needed can clutter the server like a jumbled puzzle.
2. Portability Ponderings:
   Stored procedures are specific to their database, here we are using PostgreSQL. A PostgreSQL stored procedure can't be used with other databases. This makes migrating to other databases with different procedural languages challenging.
3. Code and Database Coupling:
   Extensive use of stored procedures can couple application logic with the database. This coupling can prove to be a major annoyance as it makes it harder to change the storage solutions or decouple the two.
4. Deployment Dilemmas:
   Coordinating updates to stored procedures requires careful planning to avoid unintended consequences.
5. Debugging Quests:
   Debugging stored procedures can be a hair-pulling experience, as the process is trickier than debugging regular code. Debugging stores procedures require proper error handling and debugging tools.