PostgreSQL Function Example
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.
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
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:
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:
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:
CREATE FUNCTION func_name (args)
RETURNS data_type AS $var_name$
$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:
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:
CREATE TABLE your_tblname(
COL_NAME + DATA_TYPE + CONSTRAINTS [OPTIONAL]
Then we can insert some records into the table using the
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:
CREATE OR REPLACE FUNCTION records ()
RETURNS INTEGER AS $overall_employee$
DECLARE overall_employee INTEGER;
SELECT COUNT(*) ID INTO overall_employee FROM employee;
$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:
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