PostgreSQL: How to Create/Use/Delete Stored Functions

PostgreSQL’s stored functions allow you to write SQL queries and code contained within re-usable functions. This article will show you how to use them, with code examples.

Stored functions are similar to, but not the same as, stored procedures.

How to Create a Stored Function

You will need to use the CREATE FUNCTION statement to create new stored functions in PostgreSQL. The syntax for creating a function is as follows:

CREATE FUNCTION function_name (parameter_1 data_type, parameter_2 data_type, ...)
RETURNS return_data_type
LANGUAGE language
AS $$
-- Your code goes here
$$;

Note that:

• function_name is the name of the function you are creating. It must be unique.
• multiple parameters, each with a type, can be defined. If accepting more than one parameter, separate them with a comma.
• a return_data_type must be supplied specifying the type of value that the function will return.
• The language the code in the function is written in must be specified. This will usually be SQL.
• The AS statement will contain your function code. Note the $$ – these are dollar quotes to encapsulate multiple lines of code.
• The output of SELECT statements in the function code (if present) is the return value of the function, or you can supply a RETURN QUERY

In the below example, a PostgreSQL stored function is created that calculates the area of a rectangle:

CREATE FUNCTION calculate_rectangle_area (length NUMERIC, width NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
AS $$
    SELECT length * width;
$$;

Or, using a RETURN statement:

CREATE FUNCTION calculate_rectangle_area (length NUMERIC, width NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
RETURN length * width;

Two numeric parameters are passed to the function, which are then multiplied. The result of the final SELECT statement is the return value of the function, but a RETURN statement can also be used. Note the added RETURNS NULL ON NULL INPUT statement, which does exactly what it says – if no parameters are passed, the function returns a null value.

Using Stored Functions in PostgreSQL

Functions can be used anywhere in your queries where you would expect to use any other value of the same type as the function returns. The most simple usage is to output the return value of a function using a SQL SELECT statement:

SELECT function_name(parameter_value_1, parameter_value_2);

So, to see the result of calling the calculate_rectangle_area function from the above example, you would use:

SELECT calculate_rectangle_area(3, 4);

… which would output the value:

12

You could also use the function in a SELECT statement:

SELECT * FROM rectangles_table WHERE rectangle_area = rectangle_area(3, 4);

Or an INSERT statement:

INSERT INTO rectangles_table VALUES ('my rectangle', rectangle_area(2,5));

How to Rename a Stored Function in PostgreSQL

The ALTER FUNCTION statement is used to rename a stored procedure:

ALTER FUNCTION function_name(arguments)
RENAME TO new_function_name;

Note that the arguments must be supplied for the original stored procedure in the first line.

How to Delete a Stored Function in PostgreSQL

Finally, if you no longer need a stored procedure and want to delete it entirely, use the DROP FUNCTION statement:

DROP FUNCTION function_name(arguments);