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 inshare directory in the postgres installation folder.
Set up the folder structure. This folder contains filefactorial_extension.control andfactorial_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.controlcomment = '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 extensionCREATE SCHEMA factorial_extension;-- Create the factorial functionCREATE 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);