What are Postgres functions?

PostgreSQL functions are a way to encapsulate a series of SQL statements into a reusable unit. They allow you to create custom functions that can be called from SQL queries or other functions. Functions in PostgreSQL can be written in various programming languages such as SQL, PL/pgSQL, PL/Python, PL/Perl, PL/Tcl, etc.

PostgreSQL functions can have input parameters and can return a value. They can be used to perform complex calculations, manipulate data, or implement business logic. Functions can be created at the database level or within a specific schema.

Here’s an example of a simple PostgreSQL function that calculates the sum of two numbers:

CREATE FUNCTION add_numbers(a integer, b integer)
RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

In this example, the function is named add_numbers and takes two integer parameters a and b. It returns an integer value which is the sum of the two input parameters. The LANGUAGE plpgsql specifies that the function is written in PL/pgSQL, which is the default procedural language for PostgreSQL.

Once the function is created, it can be called like any other SQL function:

SELECT add_numbers(5, 10); -- Output: 15

PostgreSQL functions provide a powerful way to extend the functionality of the database and make your SQL queries more modular and reusable. They can be used to improve performance, simplify complex queries, and implement custom business logic.