PostgreSQL – FUNCTIONS

Functions are database objects that encapsulate a set of SQL statements or procedural code, allowing you to perform specific tasks, manipulate data, and execute complex operations. There are two main types of functions in PostgreSQL:

Built-in Functions: PostgreSQL provides a wide range of built-in functions that perform various operations on data. These functions cover mathematical operations, string manipulation, date and time operations, aggregate calculations, and more. You can use these functions directly in your SQL queries to perform specific tasks without writing custom code. Examples of built-in functions:

SUM(), AVG(), COUNT(): Aggregate functions for calculations.

CONCAT(), UPPER(), LOWER(): String manipulation functions.

DATE_PART(), TO_CHAR(): Date and time functions.

User-Defined Functions (UDFs)

User-defined functions are functions that you create to perform custom logic. There are several types of user-defined functions in PostgreSQL:

Scalar Functions

These functions take one or more input values and return a single value. They are similar to built-in functions and can be used within SQL queries.

Table-Valued Functions

These functions return a set of rows (a table) as their output. They are used in the FROM clause of a query and can be thought of as par[‘ameterized views.

Set-Returning Functions (SRFs)

Similar to table-valued functions, these functions also return sets of rows. However, they are used in the SELECT clause and can be invoked for each row in the result set.

Aggregate Functions

You can create custom aggregate functions that operate on groups of rows and return a single value per group. This is useful for creating custom aggregation logic that is not covered by built-in aggregates.

Creating User-Defined Functions:

To create a user-defined function in PostgreSQL, you use the CREATE FUNCTION statement. Here’s a basic syntax for creating a scalar function:

CREATE FUNCTION function_name(param1 datatype, param2 datatype) 
RETURNS return_datatype 
LANGUAGE plpgsql -- or your chosen language (SQL, Python, etc.) 
AS 
$$ 
DECLARE  
         -- Declare local variables here (optional)
BEGIN 
         -- Function logic here 
         RETURN result_value; 
END; 
$$

Calling Functions

Once you’ve created a function, you can call it just like any other function, whether it’s a built-in function or a user-defined function as shown below:

SELECT function_name(param1, param2);