DbSchema Database Designer

DbSchema | How to Create a Procedure in PostgreSQL?


Table of Contents


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.

  1. 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.

DbSchema Features

DbSchema → Your Trusted Partner in Database Design

Simplify complex database workflows and improve productivity with DbSchema's advanced design and management tools

Visual Design & Modeling
Visual Design & Schema Layout

➤ Create and manage your database schema visually through a user-friendly graphical interface.

➤ Easily arrange tables, columns, and foreign keys to simplify complex database structures, ensuring clarity and accessibility.

GIT & Collaboration
Version Control & Collaboration

➤ Manage schema changes through version control with built-in Git integration, ensuring every update is tracked and backed up.

➤ Collaborate efficiently with your team to maintain data integrity and streamline your workflow for accurate, consistent results.

Data Explorer & Query Builder
Relational Data & Query Builder

➤ Seamlessly navigate and visually explore your database, inspecting tables and their relationships.

➤ Build complex SQL queries using an intuitive drag-and-drop interface, providing instant results for quick, actionable insights.

Interactive Documentation & Reporting
HTML5 Documentation & Reporting

➤ Generate HTML5 documentation that provides an interactive view of your database schema.

➤ Include comments for columns, use tags for better organization, and create visually reports.