PostgreSQL - User Defined Functions

PostgreSQL, one of the most powerful open-source relational database management systems (RDBMS), provides a strong feature set for creating and utilizing user-defined functions (UDFs). By using user-defined functions, we can enhance the modularity, maintainability, and performance of our database applications.

In this article, we will explain how to create, use, and manage user-defined functions in PostgreSQL with practical examples. We will cover syntax, function overloading, and some essential points to keep in mind when working with PostgreSQL functions.

What is a PostgreSQL User-Defined Function (UDF)?

A PostgreSQL user-defined function is a function created by the database user to perform specific operations that are not covered by built-in functions. These functions can be written in various programming languages supported by PostgreSQL, including PL/pgSQL, SQL, C, and more. UDFs allow us to encapsulate complex logic, make the code more reusable, and streamline database operations.

Syntax

CREATE FUNCTION function_name(p1 type, p2 type)
RETURNS type AS $$
BEGIN
 -- logic
END;
LANGUAGE language_name;

Key Terms

• Function Name: Specify the name of the function after the CREATE FUNCTION keywords.
  
• Parameters: Provide a comma-separated list of parameters inside the parentheses following the function name.
  
• Return Type: Indicate the return type of the function after the RETURNS keyword.
  
• Function Body: Place the code inside the BEGIN and END block. The function body ends with a semicolon (;) followed by the END keyword.
  
• Language: Specify the procedural language of the function, such as plpgsql for PL/pgSQL.

Examples of PostgreSQL User-Defined Functions

Let us take a look at some of the examples of User Defined Functions in PostgreSQL. These examples will cover basic function creation, function overloading, and handling parameters.

Example 1: Creating a Simple Function

In this example, we will develop a very simple function named 'inc' that increases an integer by 1 and returns the result.

Step 1: Launch pgAdmin and connect to the dvdrental sample database.

Step 2: Enter the following commands to create the 'inc' function.

CREATE FUNCTION inc(val integer) RETURNS integer AS $$
BEGIN
RETURN val + 1;
END; $$
LANGUAGE PLPGSQL;

Step 3: Click the Execute button to create the function.

Dollar Quoting

The entire function definition that us provide to the CREATE FUNCTION must be a single quoted string. It means that if the function has any single quote ('), we have to escape it. Fortunately, from version 8.0, PostgreSQL provides a feature called dollar quoting that allows us to choose a suitable string that does not appear in the function so that we don’t have to escape it.

A dollar quote is a string of characters between $ characters. If the function is valid, PostgreSQL will create the function and return the CREATE FUNCTION statement as shown above.

Testing the Function

Let’s test the inc function. We can call the 'inc' function like any built-in functions as follows. After executing the below given query, we will observe that It worked as expected.

SELECT inc(20);

SELECT inc(inc(20));

Example 2: Function Overloading

PostgreSQL supports function overloading, allowing multiple functions with the same name but different parameters. In this example, we will create a version of the inc function that accepts two arguments, where the first argument is increased by the second argument

Steps to Create a function using pgAdmin

The following steps show you how to create a function from the pgAdmin.

Step 1: Launch pgAdmin and connect to the dvdrental database.

Step 2: Right-click on the Functions and select Create > Function… menu item. A new window will display.

Step 3: Enter 'inc' in the name of the function.

Step 4: In the Definition tab, click the + button to add two arguments 'i' and 'val' with 'bigint' as the data type.

Step 5: In the same tab change language to 'plpgsql'.

Step 6: Switch to the Code tab and define the function as shown below:

Step 7: Click the SQL tab to see the generated code and click the Save button to create the function:

Step 8: The function may not display in the function list. To see the new inc function, right-click the Functions and click Refresh….

Here is the new inc function:

Testing the Overloaded Function

We can now test the overloaded inc function by passing two arguments. Now let's test the function with the below query

SELECT inc(10, 20);

Output

Important Points About User Defined Functions in PostgreSQL

• PostgreSQL User-Defined Functions are created using the CREATE FUNCTION statement.
  
• To avoid escaping single quotes in the function body, PostgreSQL supports Dollar Quoting.
  
• Functions can include logic to handle NULL values, ensuring robust and error-free operations.
  
• PostgreSQL supports function overloading, allowing multiple functions with the same name but different parameters.
  
• User-Defined Functions enable the encapsulation of logic to be reused across different queries and operations within the database.

Conclusion

PostgreSQL user-defined functions (UDFs) provide an efficient way to extend the capabilities of the database with custom logic. By using PL/pgSQL, SQL, or even C, we can create reusable, modular functions that simplify our SQL queries and enhance application performance. From basic functions to function overloading, PostgreSQL offers powerful tools to build custom solutions tailored to our needs.