Functions in PostgreSQL

PostgreSQL Functions
PostgreSQL functions are stored procedures and can be easily understood as a set of SQL and procedural statements. They are stored on the database server. A function can be invoked using the SQL interface. It facilitates to ease the operations within the database. A PostgreSQL function can be created in several languages including SQL, PL/pgSQL, C and Python.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS $variable_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END;
LANGUAGE plpgsql;


Parameters:
function_name: It is used to specify the name of the function.
[OR REPLACE]: It is an optional method which facilitates to modify an existing function.
RETURN: It is used to specify the data type to be returned from the function. Its value can reference the type of a table column or can be a base, composite, or domain type.
function_body: It is used to specify the executable parts.
plpgsql: It is used to specify the name of the language in which the function is implemented.

Example:

CREATE OR REPLACE FUNCTION total_students() RETURNS integer AS $total$ declare total integer; BEGIN SELECT count(*) into total FROM STUDENTS; RETURN total; END; $total$ LANGUAGE plpgsql;

Output:
Query returned successfully with no result in 300 ms.

Explanation:
Here we are creating a function named total_students() on the STUDENTS table.