User Defined Function in PostgreSQL

Photo by Sebastien Bonneval on Unsplash

What is User-Defined Function (UDF)?

User-defined functions extend the functionality of PostgreSQL. These functions can take input parameters, execute a series of SQL statements, and return results. It adds convenience, improve query logic and allow other applications to reuse your database.

PostgreSQL uses the CREATE FUNCTION statement to develop user-defined functions.

Syntax:

Let’s see the syntax of UDF in detail:

· First, specify the name of the function after the CREATE FUNCTION keyword.

· Then, put a comma-separated list of parameters inside the parentheses followed by function name.

· Next, specify the return type of function after the RETURNS keyword. The return type can be a datatype/variable/table/void.

· After that, place business logic inside the BEGIN and END block. The function always ends with a semicolon (;) followed by END keyword.

· Finally, indicate the procedural language of the function e.g., plpgsql.

We are going to use dvdrental dataset to explain User-defined functions in detail.

Two important things about User-Defined Functions are:

1. The function accepts four types of parameters IN, OUT, INOUT and VARIADIC.

2. The function can return any of the following types: void, any datatype variable and a table.

User-Defined Function using “IN” Parameter

The IN parameter is the default type. We no need to mention explicitly.

Example: Create a function to get the number of films acted by an actor.

In the above function we pass actor_id as input parameter and returns the integer variable(out_film_count) number of films acted by the actor.

Function call: Here we are passing actor_id as 1.

SELECT Number_of_films_acted(1) ;

Result: The actor with actor_id 1 acted in 19 films.

User-Defined Function using “OUT“ parameter:

The OUT parameters should be explicitly mentioned. The OUT parameter returns output from the function. It does not need to mention the RETURNS type and RETURN statement at the end of the block.

Example: Create a function to get the total number of actors in actor table.

Function call: SELECT total_actor_count();

Result:

User-Defined Function using “INOUT” Parameter

The INOUT parameters should be explicitly mentioned. The INOUT parameter can accept value from the caller and will return the updated value.

Example: In order to get the count of films of an actor, we can pass actor_id and get the count of films acted by the actor. Here we used actor_count as the variable for INOUT parameter.

Function call: SELECT Count_of_films(2);

Result:

The actor having actor_id 2 acted 25 films.

User-Defined Function using “VARIADIC” parameter:

PostgreSQL functions can also accept a varying number of arguments.

Each argument must have same data type which are passed to the function as an array. The parameter has to be marked as VARIADIC.

Example: The film_list variable in the below function assigned as VARIADIC type. It means we accept any number of arguments with integer data type.

The film_list variable in the above function assigned as VARIADIC type. IT means we accept any number of arguments with integer data type.

Function call: We are calling here the same function with different number of parameters.

SELECT get_total_films_acted(1),get_total_films_acted(1,2),get_total_films_acted(1,2,3) ;

Result:

The count of films all 3 actors acted are displayed separately.

User-Defined Functions using “Void” return type:

In case the function is not going to return any value, then we can assign the return type as Void.

Example: Create a function to update the last_update column for actor_id 5 as current timestamp. We no need to return here anything and so mentioned the return type as Void.

Function call: SELECT last_updated_by(5);

Result: We did not get any output from the function because the return type is Void.

User-Defined Function using “Table” return type :

To return a table from the function, you use RETURNS TABLE syntax and specify the columns of the table. Each column is separated by a comma (,).

Example: We are trying to get the actor's name and film name based on input film name pattern by using the below function with table as a return type. We are getting results in the form of table.

Function call: SELECT * FROM get_actor_detail(‘destination%’);

Result:

Conclusion:

User-defined functions in PostgreSQL are powerful tools which can enhance your database operations, code modularity and query readability. You can optimize your SQL by encapsulating logic into reusable functions and make your database tasks more efficient.

PostgreSQL functions are your gateway to unlock full potential of the database management system by build complex queries and enhance database performance. Start exploring the power of user-defined functions in PostgreSQL.

Happy coding!