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.

Please follow and like us: