PostgreSQL Stored Function Development

Introduction

In this lab, you will learn how to develop PostgreSQL stored functions. You'll go through the process of defining a basic stored function, adding input parameters with default values, executing the function within a query, and finally, removing an unused function to keep your database organized.

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 perform a specific task and can be executed within the database.

First, open a terminal and connect to the PostgreSQL database using the psql command-line tool. You will perform all database operations within this psql shell.

sudo -u postgres psql

You should now see the PostgreSQL prompt, which looks like postgres=#.

Now, create a function named get_total_products that returns a fixed integer value. This simple function will help you understand the basic syntax. Execute the following SQL command in the psql shell:

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

Let's break down this command:

CREATE FUNCTION get_total_products(): This defines a new function named get_total_products with no input parameters.
RETURNS INTEGER: This specifies that the function will return a single value of the INTEGER data type.
AS $$ ... $$: The dollar-quoted string $$ is used to enclose the body of the function. This is a common practice in PostgreSQL to avoid issues with single quotes within the function's code.
BEGIN ... END;: This block contains the executable part of the function.
RETURN 100;: This is the logic of our function, which simply returns the integer 100.
LANGUAGE plpgsql: This specifies that the function is written in plpgsql, PostgreSQL's procedural language.

After executing the command, PostgreSQL will confirm the creation of the function:

CREATE FUNCTION

To verify that the function was created, you can call it in a SELECT statement:

SELECT get_total_products();

The output will show the value returned by the function:

 get_total_products
--------------------
                100
(1 row)

This confirms that your first stored function is working correctly.

Create a Function with Parameters

Stored functions become more powerful when they can accept input parameters. In this step, you will create a new function that takes two numbers as input and returns their sum.

Ensure you are still in the psql shell from the previous step. Now, create a function named add_numbers that accepts two integer parameters.

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

The syntax is similar to the previous step, with a key difference in the function signature:

add_numbers(a INTEGER, b INTEGER): This defines two parameters, a and b, both of type INTEGER, that must be provided when the function is called.

After executing the command, you will see the CREATE FUNCTION confirmation.

Now, test the function by providing two numbers as arguments:

SELECT add_numbers(15, 25);

The function will add the two numbers and return the result:

 add_numbers
-------------
          40
(1 row)

You can also use functions with default parameter values. Let's create a new function greet_user where the greeting message has a default value.

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

Here, CREATE OR REPLACE will update the function if it already exists. The greeting parameter is assigned a default value of 'Hello'. The || operator is used for string concatenation.

Test the function by providing only the required username parameter:

SELECT greet_user('Alex');

The function uses the default greeting:

       greet_user
------------------------
 Hello, Alex!
(1 row)

Now, call it again, but this time, provide a custom greeting:

SELECT greet_user('Alex', 'Welcome');

The output now shows your custom message:

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

Execute a Function in a Query

A common use for stored functions is to perform calculations on data from tables. In this step, you will create a table, populate it with data, and then use a function in a query on that table.

First, create a simple table called products to store product names and prices.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

You will see the CREATE TABLE confirmation message.

Next, insert some sample data into the products table:

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

You will see INSERT 0 3, indicating that three rows were inserted.

Now, let's create a function to calculate the price with sales tax. This function will take a price as input and return the price including a 7% tax.

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

After creating the function, you can use it directly in a SELECT query on the products table. This query will show the original price and the price including tax for each product.

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

The output will display the results of the function call for each row:

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

This demonstrates how you can encapsulate business logic within a function and apply it to your data.

Remove a Stored Function

It is good practice to remove database objects that are no longer needed. In this step, you will learn how to remove or "drop" a stored function from your database. We will remove the get_total_products function created in the first step.

First, you can list the functions in your database to confirm that get_total_products exists.

\df

You will see a list of functions, including get_total_products.

To remove the function, use the DROP FUNCTION command. You must specify the function name. If the function has parameters, you would need to specify their types, but since get_total_products has none, you can just use the name.

DROP FUNCTION get_total_products();

PostgreSQL will confirm the action:

DROP FUNCTION

Now, if you list the functions again with \df, you will see that get_total_products is no longer in the list.

It is also important to specify the argument types if the function is overloaded (i.e., multiple functions with the same name but different parameters). For example, to drop the add_numbers function, you must specify its integer parameters:

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Finally, to clean up your environment, drop the products table created in the previous step.

DROP TABLE products;

This concludes the lab. To exit the psql shell, type \q and press Enter.

Summary

In this lab, you learned the fundamentals of developing stored functions in PostgreSQL. You created a basic function, enhanced it with parameters and default values, applied a function in a query against table data, and finally, cleaned up the database by removing the functions and tables. These skills are essential for creating more modular and efficient database solutions.