PostgreSQL Functions

PostgreSQL functions are also known as PostgreSQL stored procedures. A PostgreSQL function or a stored procedure is a set of SQL and procedural statements (declarations, assignments, loops, flow-of-control etc.) that stored on the database server and can be invoked using the SQL interface. It facilitates you to carry out operations that would normally take several queries and round trips in a single function within the database.

You can create PostgreSQL functions in many languages such as SQL, PL/pgSQL, C, Python etc.

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;

Parameter explanation

function_name: It specifies the name of the function

[OR REPLACE]: It is optional. Itn allows you to modify an existing function.

RETURN: It specifies a data type you are going to return from the function. It can be a base, composite, or domain type, or can reference the type of a table column.

function_body: The function_body contains the executable parts.

plpgsql: It specifies the name of the language in which the function is implemented.

Let's take an example to demonstrate PostgreSQL function.

See this example:

We have a table named "EMPLOYEES" having the following data

We have to create a function named totalrecords() on that EMPLOYEES table.

Execte the following query:

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