Custom PostgreSQL Functions and Extensions

PostgreSQL is a highly extensible database that allows users to add custom functionality through functions and extensions. Custom functions enable us to encapsulate complex logic and reuse it in queries.

While extensions enhance PostgreSQL’s capabilities by adding new data types and procedural languages. This article will explain how to create custom functions and use extensions to extend PostgreSQL's core functionality.

PostgreSQL Custom Functions

Custom functions in PostgreSQL are user-defined routines that can perform various operations, like calculations, data transformations, or even direct interaction with database tables. These functions can be written in different languages supported by PostgreSQL, including SQL, PL/pgSQL, PL/Python, and PL/Perl.

Syntax:

CREATE [OR REPLACE] FUNCTION function_name (parameter1 data_type, parameter2 data_type, ...)
RETURNS return_data_type AS $$
BEGIN
    -- Function logic goes here
    RETURN result;
END;
$$ LANGUAGE plpgsql;

key terms

• function_name: The name of the function.
  
• parameter1, parameter2: The input parameters for the function.
  
• return_data_type: The type of data returned by the function.
  
• LANGUAGE: The language in which the function is written.

Example 1: Custom Function in PL/pgSQL

Before utilizing the total_salary function, ensure that the employees table exists. Here's how to create the employees table and populate it with sample data:

Query:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary NUMERIC
);
INSERT INTO employees (name, department, salary) 
VALUES 
('Alice', 'HR', 50000),
('Bob', 'IT', 60000),
('Charlie', 'HR', 55000),
('David', 'IT', 65000),
('Eva', 'Finance', 70000);

Output:

Custom Function to Calculate Total Salary

Now, let’s create a custom function that calculates the total salary of employees in a given department. Now we can now call the total_salary function and it should work without errors:

Query:

CREATE OR REPLACE FUNCTION total_salary(dept_name VARCHAR)
RETURNS NUMERIC AS $$
DECLARE
    total NUMERIC;
BEGIN
    -- Calculate the total salary for the given department
    SELECT SUM(salary) INTO total
    FROM employees
    WHERE department = dept_name;
    
    -- Return the total salary
    RETURN total;
END;
$$ LANGUAGE plpgsql;

SELECT total_salary('IT');

Output:

Explanation:

When you run the SELECT total_salary('IT'); query, it will return the total salary of employees in the 'IT' department, which is the sum of 60,000 and 65,000, giving an output of 125,000.

Example 2: Custom Function in SQL

Custom functions can also be written in plain SQL. Here is a simple function that returns the square of a number. Now use SQL statements to calculate the square of the number.

Query:

CREATE OR REPLACE FUNCTION square(num INTEGER)
RETURNS INTEGER AS $$
    SELECT num * num;
$$ LANGUAGE SQL;
SELECT square(5);

Output:

Explanation:

In this example, the square function is defined using plain SQL to calculate the square of a given number. When we call the function with SELECT square(5);, it multiplies the input value 5 by itself (5 * 5), returning the result 25.

PostgreSQL Extensions

PostgreSQL’s extension system allows users to install additional features that are not part of the core database. Extensions can provide new data types, functions, index methods, or even new procedural languages. PostgreSQL comes with several built-in extensions, and you can also create or install third-party extensions.

How to Install an Extension

We can install an extension using the CREATE EXTENSION command. For example, to install the popular hstore extension (used for storing key-value pairs), use the following command:

CREATE EXTENSION hstore;

Popular PostgreSQL Extensions

1. pg_stat_statements

This extension provides a way to track execution statistics of all SQL queries executed by the database. It is helpful for query performance monitoring.

Installation:

CREATE EXTENSION pg_stat_statements;

2. hstore

The hstore extension enables the storage of key-value pairs in a single PostgreSQL column, ideal for semi-structured data.

Installation:

CREATE EXTENSION hstore;

3. PostGIS

A popular extension that adds support for geographic objects, allowing us to run spatial queries.

Installation:

CREATE EXTENSION postgis;

Example 3: Using hstore Extension

The SQL queries provided demonstrate how to create a table utilizing the hstore extension and insert data into it. The hstore data type allows for the storage of key-value pairs, making it ideal for handling semi-structured data.

Step 1: Installation

CREATE EXTENSION hstore;

Step 2: Creation and insertion of table

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    attributes hstore
);

INSERT INTO products (name, attributes) 
VALUES ('Laptop', 'color => "black", weight => "2kg"');

Output:

Step 3: Query the hstore field:

SELECT name, attributes->'color' AS color
FROM products;

Output:

Example 4: Using pg_stat_statements Extension

The pg_stat_statements extension tracks query execution statistics for monitoring performance. Once installed, you can query the statistics like this:

Query:

SELECT query, calls, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Output:

Explanation:

The output from the query executed against the pg_stat_statements extension provides insights into the performance of SQL queries executed in the database.

Conclusion

Custom functions and extensions make PostgreSQL more flexible and powerful. Functions help us to reuse code, while extensions add new features and tools. Whether we need to perform complex calculations, work with special data types, or improve performance. It a great choice for both developers and database administrators.