PostgreSQL Stored Function Development

Introduction

In this lab, you will learn how to develop PostgreSQL stored functions. You'll go through defining a basic stored function, adding input parameters, executing it within a query, and removing an unused function.

Skills Graph

Define a Basic Stored Function

In this step, you will learn how to define a basic stored function in PostgreSQL. Stored functions are reusable blocks of code that can be executed within the database.

First, connect to the PostgreSQL database as the postgres user:

sudo -u postgres psql

Now, let's create a function named add_numbers that takes two integer inputs and returns their sum. Execute the following SQL command:

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Let's break down this code:

• CREATE FUNCTION add_numbers(a INTEGER, b INTEGER): This defines the function name as add_numbers and specifies two integer input parameters, a and b.
• RETURNS INTEGER: This specifies that the function will return an integer value.
• AS $$ ... $$: This defines the function body. The $$ symbols are used to delimit the function body.
• BEGIN ... END;: This block contains the function's logic.
• RETURN a + b;: This calculates the sum of a and b and returns the result.
• LANGUAGE plpgsql: This specifies that the function is written in the PL/pgSQL procedural language.

After executing the SQL statement, you should see the following output:

CREATE FUNCTION

This indicates that the function was created successfully.

To verify the function's creation, you can list all functions using the following command:

\df

You should see add_numbers in the list.

Finally, let's test the function. Execute the following query:

SELECT add_numbers(5, 3);

The output should be:

 add_numbers
-------------
           8
(1 row)

This confirms that the function is working correctly.

Add Input Parameters with Default Values

In this step, you'll learn how to add input parameters with default values to a function. This allows you to call the function with fewer arguments, using the default values for the missing ones.

Let's create a new function called add_numbers_default that takes two integer input parameters, a and b, with default values of 0. Execute the following SQL command:

CREATE OR REPLACE FUNCTION add_numbers_default(a INTEGER DEFAULT 0, b INTEGER DEFAULT 0)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

Let's break down this code:

• CREATE OR REPLACE FUNCTION add_numbers_default(a INTEGER DEFAULT 0, b INTEGER DEFAULT 0): This defines the function name as add_numbers_default and specifies two integer input parameters, a and b, with default values of 0. CREATE OR REPLACE allows you to create the function if it doesn't exist, or replace it if it does.
• RETURNS INTEGER: This specifies that the function will return an integer value.
• AS $$ ... $$: This defines the function body.
• BEGIN ... END;: This block contains the function's logic.
• RETURN a + b;: This calculates the sum of a and b and returns the result.
• LANGUAGE plpgsql: This specifies that the function is written in the PL/pgSQL procedural language.

After executing the SQL statement, you should see the following output:

CREATE OR REPLACE FUNCTION

Now, let's test the function with different combinations of input parameters:

1. Provide both parameters:

SELECT add_numbers_default(5, 3);

The output should be:

 add_numbers_default
---------------------
                     8
(1 row)

2. Provide only the first parameter:

SELECT add_numbers_default(5);

The output should be:

 add_numbers_default
---------------------
                     5
(1 row)

In this case, a is 5 and b defaults to 0, so the result is 5.

3. Provide no parameters:

SELECT add_numbers_default();

The output should be:

 add_numbers_default
---------------------
                     0
(1 row)

In this case, both a and b default to 0, so the result is 0.

Execute the Function in a Query

In this step, you will learn how to execute the function within a query. This demonstrates how stored functions can be used to manipulate and retrieve data.

First, let's create a simple table called numbers with two integer columns, num1 and num2:

CREATE TABLE numbers (
  num1 INTEGER,
  num2 INTEGER
);

After executing the SQL statement, you should see the following output:

CREATE TABLE

Now, let's insert some sample data into the numbers table:

INSERT INTO numbers (num1, num2) VALUES (10, 5);
INSERT INTO numbers (num1, num2) VALUES (20, 3);
INSERT INTO numbers (num1, num2) VALUES (30, 0);

After executing the SQL statements, you should see the following output for each INSERT statement:

INSERT 0 1

Now, let's execute the add_numbers_default function within a query to calculate the sum of num1 and num2 for each row in the numbers table:

SELECT num1, num2, add_numbers_default(num1, num2) AS sum FROM numbers;

The output should be:

 num1 | num2 | sum
------+------+-----
   10 |    5 |  15
   20 |    3 |  23
   30 |    0 |  30
(3 rows)

As you can see, the add_numbers_default function was executed for each row in the numbers table, and the result was displayed in the sum column.

Let's try another query where we only provide one parameter to the add_numbers_default function. We'll use num1 as the first parameter and let num2 default to 0:

SELECT num1, add_numbers_default(num1) AS sum FROM numbers;

The output should be:

 num1 | sum
------+-----
   10 |  10
   20 |  20
   30 |  30
(3 rows)

Finally, let's try a query where we don't provide any parameters to the add_numbers_default function:

SELECT add_numbers_default() AS sum FROM numbers;

The output should be:

 sum
-----
   0
   0
   0
(3 rows)

Remove an Unused Function

In this step, you will learn how to remove an unused function from your PostgreSQL database. Removing unused functions helps keep your database clean and organized. We will remove the add_numbers function that we created in the first step.

Before removing the function, let's verify that it exists. You can list all functions using the following command:

\df

You should see add_numbers in the list. If you don't see it, it means you either didn't create it in the first step or you already removed it.

Now, let's remove the add_numbers function using the DROP FUNCTION command:

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Let's break down this code:

• DROP FUNCTION add_numbers(INTEGER, INTEGER): This specifies that we want to drop the function named add_numbers. It's important to specify the input parameter types (in this case, INTEGER, INTEGER) because PostgreSQL allows function overloading (having multiple functions with the same name but different parameter types).

After executing the SQL statement, you should see the following output:

DROP FUNCTION

Now, let's verify that the function has been removed. You can list all functions again using the following command:

\df

You should no longer see add_numbers in the list of functions.

Finally, let's drop the numbers table created in the previous step to clean up the environment:

DROP TABLE numbers;

Summary

In this lab, you learned how to develop PostgreSQL stored functions. You covered defining a basic stored function, adding input parameters with default values, executing the function within a query, and removing an unused function. These skills are fundamental for building more complex and efficient database applications.