Mastering PostgreSQL Databases: From Basics to Advanced/

...

/

Stored Procedures and Functions

Stored Procedures and Functions

Learn how to use stored procedures and functions to perform simple operations and calculations in the PostgreSQL database.

We'll cover the following...

Stored procedures and functions are a way to group multiple SQL statements into a reusable module. This allows for better organization and often improved performance as well. They can also accept input parameters and return values, making them even more versatile.

Some common use cases for stored procedures and functions include:

  • Data validation

  • Performing repetitive tasks

  • Security measures such as password encryption

Stored procedures

Stored procedures are reusable blocks of SQL code that can be called and executed whenever needed. They allow for faster execution of repetitive tasks and increased security by defining strict parameters for input and output. The syntax for creating stored procedures is given below:

Press + to interact
CREATE PROCEDURE <procedure_name>(
   <parameter_1> <data_type_1>, 
   <parameter_2> <data_type_2>,
   ...
   <parameter_n><data_type_n>)
LANGUAGE plpgsql
AS $$
BEGIN
   -- SQL Statements
END; $$

Stored procedures can accept input parameters and return multiple values.

Here, <procedure_name> is the procedure’s name, <parameter_1> <data_type_1>, ... are the input parameters and corresponding data types that the procedure takes in, and within the BEGIN and END statements is the SQL code that will be executed. It’s written in the PL/pgSQL language. Stored procedures and functions can be created using any of PostgreSQL’s supported languages, including SQL, PL/pgSQL, Perl, Tcl, and Python.

Note: PostgreSQL does not support nested procedures, meaning a procedure can’t be called within itself or another already executed procedure.

Press + to interact
CREATE PROCEDURE update_employee(employee_id int, job_title text)
LANGUAGE plpgsql
AS $$
BEGIN 
   UPDATE Employee 
   SET title = $2
   WHERE id = $1;
END; $$;

In this example, we create a stored procedure called update_employee that takes in two parameters: an integer for the employee ID and a text value for the new job title. The ...