PostgreSQL Function Example

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

When you’re working with PostgreSQL, there may be a certain series of SQL statements or queries that you find yourself executing time and again. One way to make the task more efficient is to package these statements into a PostgreSQL function, also known as a stored procedure. Creating a function allows you to execute the whole series of statements or queries by calling just a single function name. In this article, we’ll provide a PostgreSQL function examples that illustrates how a typical function is created and used.

Prerequisites

Before we get started with our tutorial, we need to check for any prerequisites that might be necessary for the task. In this case, there’s only one prerequisite that needs to be in place: You need to have a PostgreSQL database cluster installed on your machine. To check if PostgreSQL is installed, just use the command psql -V.

Screenshot of a terminal window getting the PostgreSQL server status and psql version

Connect to the psql

The first thing we’ll need to do is access our PostgreSQL database cluster. We’ll do this by using the following command in the terminal:

1
sudo su - postgres

This command will prompt for a password in order to grant root privileges. Once you enter the password, just press ENTER and then type the following command:

1
psql

The PostgreSQL function

A PostgreSQL function is a procedure statement that allows you to wrap up multiple statements or queries in a reusable chunk of code. Functions are also known as stored procedures in PostgreSQL.

Here’s the basic syntax used when creating a PostgreSQL function:

1
2
3
4
5
6
7
8
CREATE FUNCTION func_name (args)
    RETURNS data_type AS $var_name$
        DECLARE declare_variable;
    BEGIN
    -function_statement-
    RETURN var_name;
END;
$var_name$ LANGUAGE plpgsql;

Let’s break down this code line by line:

  • CREATE FUNCTION – creates the function and also specifies the name of it

  • RETURNS/RETURN – specifies what value is returned when the function completes its task

  • DECLARE – is used to initialize a value for a variable declaration

  • function_statement – contains the executable parts of the function statement

  • BEGIN – indicates a transaction that will start the function

  • END – indicates the end of the transaction

  • plpgsql – identifies the language in which the function will be implemented

Create a database and table

We’ll need a database and table to work with when we demonstrate our PostgreSQL function examples, so let’s create those next. We’ll start with our database:

1
CREATE DATABASE your_dbname;

After creating the database, use the command \c to enter the database and create a table.

To create a table in PostgreSQL, we’ll use the command shown below:

1
2
3
CREATE TABLE your_tblname(
    COL_NAME + DATA_TYPE + CONSTRAINTS [OPTIONAL]
);

Then we can insert some records into the table using the INSERT statement:

1
2
INSERT INTO your_tblname(COL1, COL2, COL3)
        VALUES(VAL1, VAL2, VAL3);

PostgreSQL function example

Now that we’ve created a database and table, we’re ready to create our function. Let’s imagine we have a table of employees that contains a total of 201 records. We can create a function that counts all the records inside the table:

1
2
3
4
5
6
7
8
CREATE OR REPLACE FUNCTION records ()
    RETURNS INTEGER AS $overall_employee$
        DECLARE overall_employee INTEGER;
    BEGIN
    SELECT COUNT(*) ID INTO overall_employee FROM employee;
    RETURN overall_employee;
END;
$overall_employee$ LANGUAGE plpgsql;

NOTE: You can use REPLACE when declaring the function name to modify the function if it has already been created.

Now, let’s use a SELECT statement to call the function we created:

1
2
3
4
5
SELECT records();
 records
---------
     201
(1 ROW)

Conclusion

Creating functions is a simple way to make your PostgreSQL work easier and more efficient. When you create a function, you can wrap multiple SQL statements and queries into a reusable piece of code that can be executed with a single function call. In this article, we provided a PostgreSQL function example that showed how to both create and call a typical function. With this example and our step-by-step instructions, you’ll be able to build your own functions to streamline your PostgreSQL tasks.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.