DbSchema | How to Create a Procedure in PostgreSQL?

Publish on DbSchema Blog >>>

---

Table of Contents

• Introduction
• What is a Procedure?
• Advantages of Using a Procedure
• Drawbacks of Using a Procedure
• Prerequisites
• Procedure Parameters Explained
• Creating a Procedure in psql
• Creating a Procedure in DbSchema
• Conclusion
• References

---

Introduction

In the world of databases, procedures play a crucial role in automating routine tasks and encapsulating repetitive SQL queries. PostgreSQL, a highly powerful and flexible open-source relational database, supports the creation and use of stored procedures.

This article will guide you through the process of creating a procedure in PostgreSQL, both using psql command-line interface and the graphical interface DbSchema.

What is a Procedure?

In the context of databases, a procedure (often referred to as a stored procedure) is a precompiled collection of SQL statements and control-of-flow statements. They are stored on the database server and can be invoked by applications as required. Procedures can accept parameters, perform actions and return a value.

Advantages of Using a Procedure

1. Efficiency: Procedures are precompiled and stored on the database server, reducing the amount of information sent over the network and improving performance.

2. Modularity: You can write a procedure once, and call it from multiple places in your application.

3. Security: Procedures provide a layer of security, as you can grant permissions to execute the procedure, instead of granting access to all the underlying tables.

4. Maintenance: Changes in a procedure do not require changes in the application code as long as the procedure interface (parameters and result) remains consistent.

Drawbacks of Using a Procedure

1. Portability: Procedures are typically specific to one type of database system. If you switch to a different database system, you may need to rewrite your procedures.

2. Debugging: Debugging stored procedures can often be more complex than debugging application code.

3. Version Control: Keeping track of changes to stored procedures can be more difficult than with application code, especially when multiple developers are involved.

Prerequisites

Before you start, you need to have PostgreSQL installed and set up on your system. You should also have a basic understanding of SQL. To use dbSchema, you need to have it installed and connected to your PostgreSQL database.

For installation and establishing connection refer to PostgreSQL-How to create a database?

Procedure Parameters Explained

When creating a PostgreSQL procedure, you can specify several parameters:

Parameter	Description
name	The name of the procedure.
argmode	The mode of the argument. Can be IN (the argument is an input argument), OUT (the argument is an output argument), or INOUT (the argument is both an input and an output argument).
argname	The name of the argument.
argtype	The data type of the argument.
default_expr	The default value of the argument.
lang_name	The procedural language in which the procedure is written. PostgreSQL supports several procedural languages, including PL/pgSQL, PL/Tcl, PL/Perl, and PL/Python.
configuration parameter value	Custom configuration parameters for the procedure.
definition	The SQL body of the procedure. This contains the SQL statements that are executed when the procedure is called.
obj_file	For external language procedures, this is the file with the procedure’s code.
link_symbol	For external language procedures, this is the name of the symbol to link to in the file specified by obj_file.
sql_body	The SQL code that makes up the procedure.

Creating a Procedure in psql

In PostgreSQL, creating a stored procedure involves the use of the CREATE OR REPLACE PROCEDURE command. Here is a step-by-step guide:

1. Open psql: Launch your PostgreSQL command-line interface.

2. Begin the procedure declaration: Type CREATE OR REPLACE PROCEDURE, followed by the name of your procedure.

3. Define parameters: Next, you define the parameters your procedure will accept in parentheses, specifying their mode (IN, OUT, INOUT), name, and data type.

4. Specify the procedural language: After the parameter list, specify the procedural language with the LANGUAGE keyword. Most commonly, this is plpgsql.

5. Write the procedure body: Between the $$ delimiters, you write the SQL statements that make up your procedure.

CREATE OR REPLACE PROCEDURE update_salary(emp_id INT, increase_amount INT)
LANGUAGE plpgsql
AS $$
BEGIN
   -- This is the SQL statement that gets executed when the procedure is called.
   -- It increases the salary of the specified employee by the specified amount.
   UPDATE employees
   SET salary = salary + increase_amount
   WHERE id = emp_id;
   
   -- After the update, we commit the transaction to make sure the changes are saved.
   COMMIT;
END;
$$;

In this example, update_salary is a procedure that increases the salary of an employee. The procedure takes two parameters: emp_id and increase_amount.

6. Call the procedure: To call the procedure, you would use the CALL command, followed by the procedure name and any required parameters. For example:

CALL update_salary(1, 500);

Creating a Procedure in DbSchema

Creating a procedure in dbSchema involves similar steps, but instead of typing SQL commands, you use a graphical interface.

1. Open DbSchema: Launch your dbSchema application.

2. Navigate to the Procedures section: In the ‘Schema’ pane, find and click on ‘Procedures’.

3. Create a new procedure: Right-click on the ‘Procedures’ section and select ‘Create New Procedure’.

4. Define the procedure: In the new window, you fill in the procedure name, parameters (with their mode, name, and data type), and body. You can enter the SQL statements directly in the ‘Procedure Body’ field.

5. Save the procedure: After filling in all necessary information, click ‘OK’. The procedure will be created in your PostgreSQL database.

6. Call the procedure: In DbSchema, you can call the procedure by right-clicking on it in the ‘Procedures’ section and selecting ‘Call Procedure’. Then, fill in any required parameters and click ‘OK’.

Create Procedure and Visually Manage PostgreSQL using DbSchema

DbSchema is a PostgreSQL client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Create Procedure

Start the application and connect to the Postgres database. Navigate to the procedure section and create a new procedure.

Conclusion

Procedures in PostgreSQL provide a powerful way to encapsulate and automate routine SQL tasks. Although they come with some drawbacks, their benefits often outweigh the negatives, making them an essential tool for efficient and secure database management.

References

1. PostgreSQL Official Documentation: https://www.postgresql.org/docs/
2. DbSchema Documentation: https://www.dbschema.com/documentation/
3. SQL Syntax: https://www.w3schools.com/sql/
4. PostgreSQL Tutorial: https://www.postgresqltutorial.com/
5. DbSchema Interactive Diagrams: https://www.dbschema.com

Remember, the official documentation for both PostgreSQL and DbSchema is the most reliable source for up-to-date information. These resources can provide more in-depth knowledge and cover other complex aspects of creating and managing databases.