PL/pgSQL – Functions

PL/pgSQL functions are named blocks of code written in the PL/pgSQL procedural language in PostgreSQL database. These functions can accept parameters, perform calculations, execute database queries, and return values. PL/pgSQL is a powerful and flexible language for writing complex database logic and stored procedures.

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

CREATE OR REPLACE FUNCTION function_name([parameters]) 
RETURNS return_type 
AS $$ 
DECLARE 
  -- Declare variables 
BEGIN 
  -- Your PL/pgSQL code here 
  -- You can use the RETURN statement to return a value 
END; 
$$ LANGUAGE plpgsql;

where

• CREATE OR REPLACE FUNCTION is the statement to create or replace a PL/pgSQL function.
• function_name is the name you want to give to your function.
• [parameters]: You can specify input parameters that the function accepts. Parameters are optional and allow you to pass values into the function. For example, (param1 datatype, param2 datatype).
• RETURNS return_type: Specifies the data type that the function will return. If the function doesn’t return a value, you can use RETURNS VOID.
• AS $$ is the beginning of the procedural block. You can think of everything between AS $$ and $$ LANGUAGE plpgsql; as the body of your function.
• DECLARE keyword is used to declare variables that you will use within the function.
• BEGIN and END keywords define the beginning and end of the function’s code block.

Inside the function’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 function that calculates the total salary of employees in a hypothetical “employees” table and returns the result:

CREATE OR REPLACE FUNCTION calculate_total_salary() RETURNS numeric 
AS $$ 
DECLARE 
 total_salary numeric; 
BEGIN 
 SELECT SUM(salary) INTO total_salary FROM employees; 
 RETURN total_salary; 
END; 
$$ LANGUAGE plpgsql;

You can execute the function like this

SELECT calculate_total_salary();

This example creates a function called calculate_total_salary that calculates the sum of salaries from the “employees” table and returns the result.

PL/pgSQL functions are incredibly versatile and can be used for a wide range of tasks, from simple calculations to complex data processing and manipulation. They provide a way to encapsulate and reuse database logic, making your code more modular and maintainable.