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
Efficiency: Procedures are precompiled and stored on the database server, reducing the amount of information sent over the network and improving performance.
Modularity: You can write a procedure once, and call it from multiple places in your application.
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.
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
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.
Debugging: Debugging stored procedures can often be more complex than debugging application code.
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:
Open psql
: Launch your PostgreSQL command-line interface.
Begin the procedure declaration: Type CREATE OR REPLACE PROCEDURE
, followed by the name of your procedure.
Define parameters: Next, you define the parameters your procedure will accept in parentheses, specifying their mode (IN, OUT, INOUT), name, and data type.
Specify the procedural language: After the parameter list, specify the procedural language with the LANGUAGE
keyword. Most commonly, this is plpgsql
.
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
.
- 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.
Open DbSchema: Launch your dbSchema application.
Navigate to the Procedures section: In the ‘Schema’ pane, find and click on ‘Procedures’.
Create a new procedure: Right-click on the ‘Procedures’ section and select ‘Create New Procedure’.
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.
Save the procedure: After filling in all necessary information, click ‘OK’. The procedure will be created in your PostgreSQL database.
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
- PostgreSQL Official Documentation: https://www.postgresql.org/docs/
- DbSchema Documentation: https://www.dbschema.com/documentation/
- SQL Syntax: https://www.w3schools.com/sql/
- PostgreSQL Tutorial: https://www.postgresqltutorial.com/
- 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.