How to Create a Function in PostgreSQL

Introduction

Although PostgreSQL offers a wide variety of built-in functions, there are times you’ll want to create your own function to meet your application’s requirements. It’s easy to accomplish this task with the help of the CREATE FUNCTION statement, which allows you to build a new PostgreSQL function or replace an existing one. In this article, we’ll explain how to create PostgreSQL functions and show you some real-life examples.

Prerequisites

Let’s begin our discussion by reviewing some prerequisites that need to be in place before you can create PostgreSQL functions:

• You’ll need to ensure that PostgreSQL server has been properly installed and configured. The service must be running in the background.

You can download PostgreSQL for Windows and Linux here.

To start up the PostgreSQL server using Linux, you can use the following command:

• You can confirm that the PostgreSQL service is running with the following command:

The output of this command should look something like the following:

• The instructions for starting, stopping and restarting PostgreSQL server in a Windows environment are somewhat different:

1. First, open Control Panel
2. Next, open Administrative Tools
3. Open Services
4. In the Services area, find the PostgreSQL Server service
5. Finally, start, stop or restart the service

What is a PostgreSQL Function?

PostgreSQL functions are somewhat similar to stored procedures. These functions are composed of a series of statements– assignments, declarations, flow-of-control, loops and more– that get saved on the server and can then be called using SQL. PostgreSQL functions allow users to perform operations that would otherwise take multiple queries and back-and-forth trips in the database.

Shown below is the syntax used to create PostgreSQL functions:

There’s a lot going on inside this SQL statement, so let’s look at it one part at a time:

• First, we indicate the name of the function following the keywords CREATE or REPLACE. These keywords specify whether we will be creating a brand-new function or replacing an existing function.

• We can create a group of parameters separated by commas inside the parentheses that come after the defined function name.

• We can indicate the data type to be returned by our function after the keyword RETURNS.

• Within the BEGIN and END block, we insert the code or the logic of the function.

• Finally, we specify the language to be used by the function; in this case, it is plpgsql.

Example of How to Create a PostgreSQL Function

Now that we have a basic understanding of how to create PostgreSQL functions, let’s put our knowledge to work in a basic application.

We’ll start by making sure that the PostgreSQL server service is running in the background.

Then, we’ll use the following syntax on the command line to create a function that will add two numeric parameters and return the result:

The “$” in this example is used as part of a PostgreSQL feature called dollar quoting. This feature enables us to include string constants without escaping any characters. When you run this statement, PostgreSQL will notify you by outputting the words “CREATE FUNCTION” to confirm that the function was successfully created; otherwise, an error will be returned.

Let’s test the basic function that we created by using the syntax shown below:

You can see that we called our newly-created function add_int() and provided two numeric values, separated by commas, as its parameters.

The output of this function should look something like this:

As we expected, the function returns a numeric value calculated by adding the two parameters.

Conclusion

When you’re working with data in PostgreSQL, it’s important to know how to create your own functions. User-defined functions allow you to build reusable bits of code instead of creating the same sets of queries over and over again. In this article, we introduced the concept of functions in PostgreSQL and provided examples that demonstrate how to create PostgreSQL functions. With the information provided in this tutorial, you’ll be able to define your own PostgreSQL functions and streamline your database tasks.