Developing User-defined Functions Using PostgreSQL CREATE FUNCTION Statement

Summary: in this tutorial, we will show you step by step how to develop the first user-defined function using PostgreSQL CREATE FUNCTION statement.

Introduction to CREATE FUNCTION statement

To create a new user-defined function in PostgreSQL, you use the CREATE FUNCTION statement as follows:

Let’s examine the CREATE FUNCTION statement in more detail.

• First, specify the name of function followed by the CREATE FUNCTION clause.
• Then, put a comma-separated list of parameters inside the parentheses followed the function name.
• Next, specify the return type of the function after the RETURNS keyword.
• After that, place the code inside the BEGIN and END block. The function always ends with a semicolon (;) followed by the END keyword.
• Finally, indicate the procedural language of the function e.g., plpgsql in case PL/pgSQL is used.

PostgreSQL CREATE FUNCTION examples

We are going to develop a very simple function named incthat increases an integer by 1 and returns the result.

Creating a function using psql

First, launch psql program and login as postgres user to connect to the dvdrental sample database.

Second, enter the following commands to create the inc function.

The entire function definition that you provide to the CREATE FUNCTION must be a single quoted string. It means that if the function has any single quote ('), you have to escape it.

Fortunately, in version 8.0 or later, PostgreSQL provides a feature called dollar quoting that allows you to choose a suitable string that does not appear in the function so that you 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.

Let’s test the  inc function.

You can call the  inc function like any built-in functions as follows:

It worked as expected.

Creating a function using pgAdmin

In PostgreSQL, functions that have different parameters can share a same name. This is called function overloading, which is similar to function overloading in C.

We can create a new function named inc that accepts two arguments. In the function, we will increase value of the first argument by the second argument.

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

First, launch pgAdmin and connect to dvdrental database.

Second, right mouse click on the functions and choose “New Function…” menu item. A new windows will display.

Third, enter inc in the name input field and a comment for the function.

Fourth, in the definition tab, choose integer as the return type and plpgsql as the procedural language.

Fifth, in the parameter tab, enter the name for each parameter and click Add button.

Sixth, enter the following code in the Code tab editor:

There are more options in the Variables, Privileges and Security Labels tabs. However, we just need the basic option for now.

Seventh, click the SQL tab to preview the code generated by pgAdmin:

Eighth, Click OK button to create the inc function.

You can call the new inc function as follows:

It returns 30. Perfect!

Congratulation! you have taken the first step to develop user-defined functions in PostgreSQL. Let’s explore the PL/pgSQL language features to create more complex user-defined functions in the next tutorials.