PL/pgSQL – Procedures

In PostgreSQL, a PL/pgSQL procedure is a named block of code written in the PL/pgSQL language that performs a specific task or set of tasks. Procedures are similar to functions, but they do not return values like functions do. Instead, they may perform operations within the database or manipulate data in some way. PL/pgSQL is a procedural language for PostgreSQL that allows you to write complex database logic.

Here’s the basic structure of a PL/pgSQL procedure:

CREATE OR REPLACE PROCEDURE procedure_name ([parameters]) 
LANGUAGE plpgsql 
AS $$ 
DECLARE 
   -- Declare variables 
BEGIN 
   -- Your PL/pgSQL code here 
END; 
$$;

where

• CREATE OR REPLACE PROCEDURE is the statement used to create or replace a PL/pgSQL procedure.
• procedure_name is the name you want to give to your procedure.
• [parameters]: You can specify input parameters that the procedure accepts. Parameters are optional and allow you to pass values to the procedure. For example, (param1 datatype, param2 datatype).
• LANGUAGE plpgsql: Specifies that you are writing the procedure in the PL/pgSQL language.
• AS $$: The beginning of the procedural block. Everything between AS $$ and $$; is the body of your procedure.
• DECLARE keyword is used to declare variables that you will use within the procedure.
• BEGIN and END keywords define the beginning and end of the procedure’s code block.

Inside the procedure’s body, you can write SQL statements, control structures (like IF and FOR loops), and other PL/pgSQL code to perform your desired tasks.

Here’s a simple example of a PL/pgSQL procedure that inserts a new record into a hypothetical “employees” table:

CREATE OR REPLACE PROCEDURE add_employee( IN employee_name text, IN employee_salary numeric ) 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
  INSERT INTO employees (name, salary) VALUES (employee_name, employee_salary); 
END; 
$$;

You can execute the procedure like this:

CALL add_employee('John Doe', 50000.00);

This example creates a procedure called add_employee that accepts two input parameters (employee_name and employee_salary) and inserts a new record into the “employees” table with the provided values.