Creating Functions in PostgreSQL

Creating Functions in PostgreSQL

When working with relational databases like PostgreSQL, one powerful feature is the ability to create custom functions. These functions allow for more complex logic to be executed within the database itself, improving performance by reducing the need to retrieve large amounts of data into your application. In this post, we’ll explore how to create a simple table, populate it with data, and write a PostgreSQL function that calculates the average of numeric values.

Step 1: Creating the Table

To start, let’s create a simple table called numbers. This table will store some numeric data that we can later average.

CREATE TABLE numbers (
    id SERIAL PRIMARY KEY,
    value NUMERIC
);

This command creates a numbers table with two columns:

• id: A unique identifier for each row.
• value: The numeric data we want to average.

Step 2: Inserting Data

Next, we populate the table with sample data:

INSERT INTO numbers (value) 
VALUES 
(10),
(20),
(30),
(40),
(50);

We now have a table with five rows of numeric data. You can verify this by running:

SELECT * FROM numbers;

This will return:

Step 3: Creating the Function

Now that we have our data, let’s create a function that calculates the average of the values in the numbers table.

CREATE OR REPLACE FUNCTION calculate_average() 
RETURNS NUMERIC AS $$
BEGIN
    RETURN (SELECT AVG(value) FROM numbers);
END;
$$ LANGUAGE plpgsql;

Let’s break down the syntax of this function step by step:

CREATE OR REPLACE FUNCTION calculate_average()

• CREATE OR REPLACE FUNCTION: This command either creates a new function or replaces an existing one with the same name. If there is already a function called calculate_average, it will be overwritten.
• calculate_average(): This is the name of the function. The parentheses () indicate that this function takes no parameters. If the function needed arguments, they would be listed here.

RETURNS NUMERIC

• RETURNS: This specifies the data type that the function will return. In this case, the function will return a NUMERIC value. NUMERIC is a flexible type that can handle both integers and decimal numbers.

AS $$

• AS: This clause introduces the function body. The dollar-quoted string $$ ... $$ allows you to define a block of code without worrying about escaping single quotes.

BEGIN

• BEGIN: This keyword marks the start of the procedural code inside the function.

RETURN (SELECT AVG(value) FROM numbers);

• RETURN: The RETURN keyword specifies the value that the function will return. In this case, it returns the result of a SQL query.
• (SELECT AVG(value) FROM numbers): This SQL query calculates the average (AVG) of the value column in the numbers table.

END;

• END: Marks the end of the procedural code block.

$$ LANGUAGE plpgsql;

• $$: This closes the dollar-quoted string that encloses the function body.
• LANGUAGE plpgsql: Specifies that the function is written in the PL/pgSQL language, which allows you to add procedural logic, such as loops or conditionals, to your SQL code.

What the function does:

• The function calculate_average() takes no arguments and returns a numeric value.
• Inside the BEGIN ... END block, it runs a query that calculates the average value from the numbers table.
• The result of the query is returned as the output of the function.

Step 4: Using the Function

Now that our function is defined, we can call it to get the average of the numbers in our table:

SELECT calculate_average();

This query will return the result:

 calculate_average
-------------------
        30.0

As you can see, the average of 10, 20, 30, 40, and 50 is 30.

Conclusion

PostgreSQL functions can greatly simplify operations that would otherwise require multiple queries or logic in your application code. In this example, we created a simple function to calculate the average of a column in a table, but PostgreSQL functions can be used for far more complex operations as well.