How to create extensions in PostgreSQL?

Creating a completely new extension for PostgreSQL involves several steps.
Here we will create a basic extension with that adds SQL function to calculate factorial of a given integer. This example is written using the PL/pgSQL procedural language.

Create the Factorial Extension

Setp 1:
Create factorial_extension folder in share directory in the postgres installation folder.
Set up the folder structure. This folder contains file factorial_extension.control and factorial_extension--1.0.sql

factorial_extension/
    |
    +-- factorial_extension.control
    |
    +-- factorial_extension--1.0.sql

Setp 2:
Write the control file. The control file for an extension, also known as the .control file, is a metadata file that provides information about the extension. It contains details such as the name, version, author, module path, dependencies, and other important attributes of the extension.

# factorial_extension.control
comment = 'Extension to calculate factorial'
default_version = '1.0'
module_pathname = '$libdir/factorial_extension'
relocatable = false

Step 3:
Write the SQL script (factorial_extension--1.0.sql):

-- factorial_extension--1.0.sql

-- Create a new schema for the extension
CREATE SCHEMA factorial_extension;

-- Create the factorial function
CREATE OR REPLACE FUNCTION factorial_extension.factorial(n INTEGER)
RETURNS BIGINT AS $$
DECLARE
    result BIGINT := 1;
BEGIN
    IF n < 0 THEN
        RAISE EXCEPTION 'Factorial is not defined for negative numbers';
    ELSIF n > 1 THEN
        FOR i IN 2..n LOOP
            result := result * i;
        END LOOP;
    END IF;
    RETURN result;
END;
$$ LANGUAGE plpgsql;

-- Grant execute permission to public (change to appropriate roles if needed)
GRANT EXECUTE ON FUNCTION factorial_extension.factorial(INTEGER) TO PUBLIC;

Run the Extension

Step 1: Start or restart the PostgreSQL server
Step 2: Connect to the database where you want to install the extension
Step 3: TO install the extension run the following command.

CREATE EXTENSION factorial_extension;

Use the Extension

SELECT factorial_extension.factorial(5);