Postgres PLpgsql Function Parameters

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

Introduction

If you’re planning to use PL/pgSQL, the procedural language for PostgreSQL, it’s important to understand how functions work in this language. One key component of a function is its parameters. In PostgreSQL, you can define both IN and OUT parameters when you create a PL/pgSQL function. This article will take a closer look at Postgres PLpgSQL function parameters and show you some examples to demonstrate how they’re used.

Prerequisites

If you’d like to follow along with the examples in this tutorial, you’ll need to have Postgres installed and configured. Simply download the following for your operating system:

Basic PostgreSQL knowledge is also required to get the most out of this tutorial.

PLpgsql Function IN Parameter

In this section, we’ll show how to use the Postgres IN parameter.

If you have any programming experience, the IN parameter will seem familiar– this type of parameter is used to pass a value to a specific function within an application. Keep in mind that the attribute of this parameter is read-only, which means we cannot alter its value within the method.

Shown below is the basic form of the IN parameter:

1
2
CREATE [OR REPLACE] FUNCTION <function_name> (
  first_param IN datatype, second_param IN datatype ... )

The example below shows how to use the IN parameter in a function:

1
2
3
4
5
6
7
8
9
10
CREATE OR REPLACE FUNCTION Sum_it_up(
   value1 NUMERIC,
   value2 NUMERIC,
   value3 NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
   RETURN value1 + value2 + value3;
END; $$

LANGUAGE plpgsql;

There’s quite a bit going on in this example, so let’s take a closer look at it:

  • We created a function named Sum_it_up() that takes three parameters: value1, value2 and value3. The function returns a NUMERIC type as declared. We are allowed to pass the Postgres IN parameters to the function; we then retrieve the sum of these values as part of the result. The syntax shown below demonstrates how to execute the function:
1
postgres=# SELECT sum_it_up(2,4,6);

After we execute the function, we should see something like this:

1
2
3
4
sum_it_up
-----------
12
(1 row)

We can see that the function accepted our IN parameters and added the values together to return the sum.

PLpgsql Function OUT Parameter

In this section, we’ll show you how to use the Postgres OUT parameter.

Unlike the IN parameter, the OUT parameter is returned either as a component or as part of the result.

Let’s look at the basic form of the OUT parameter:

1
CREATE [OR REPLACE] FUNCTION <function_name> (parameter_name OUT datatype)

The following example shows us how to use the OUT parameter as part of a function:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION higher_lower(
   value1 NUMERIC,
   value2 NUMERIC,
   value3 NUMERIC,
      OUT higher NUMERIC,
   OUT LOWER NUMERIC)
AS $$
BEGIN
   higher := GREATEST(value1,value2,value3);
   LOWER := LEAST(value1,value2,value3);
END; $$

LANGUAGE plpgsql;

Let’s discuss the above example in further detail:

We created a function that accepts five parameters. Shown below is the breakdown of these parameters:

  • We provide three IN parameters: value1, value2 and value3.
  • We have two OUT parameters: higher and lower

Notice that we did not specify a return value– by default, the OUT parameters are used to return the multiple values. In this example, the OUT parameters will return the highest and lowest values that are supplied by the IN parameters.

We can execute this function using a SELECT statement as seen in the following example:

1
SELECT higher_lower(3,6,9);

The result should look something like the following:

1
2
3
higher_lower
--------------
(9,3)

We can see that our OUT parameters worked as expected, returning the highest and lowest values from the set of IN parameters.

Conclusion

It’s important to understand how functions are defined when you work with PL/pgSQL. Knowing how function parameters work is essential for writing effective PL/pgSQL functions. In this article, we discussed the different types of Postgres PLpgsql function parameters and provided examples of their use. With these examples and instructions to get you started, you’ll be ready to create PL/pgSQL functions that work with your own PostgreSQL database.

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.