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. We will write the extension code in c++.
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, factorial_extension--1.0.sql, factorial_extension.cpp and MakeFile.
factorial_extension/
|
+-- factorial_extension.control
|
+-- factorial_extension.cpp
|
+-- factorial_extension--1.0.sql
|
+-- Makefile
Setp 2:
Write code for factorial in C++
#include <postgres.h>
#include <fmgr.h>
#include <utils/numeric.h>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
extern "C" {
PG_FUNCTION_INFO_V1(factorial);
Datum factorial(PG_FUNCTION_ARGS) {
int32 arg = PG_GETARG_INT32(0);
if (arg < 0)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("Factorial input must be a non-negative integer")));
int64 result = 1;
for (int i = 2; i <= arg; ++i)
result *= i;
PG_RETURN_INT64(result);
}
}
Setp 3:
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 4:
Write the SQL script (factorial_extension--1.0.sql):
-- factorial_extension--1.0.sql
-- Create the extension schema
CREATE SCHEMA factorial_extension;
-- Create the SQL function
CREATE OR REPLACE FUNCTION factorial_extension.factorial(integer)
RETURNS bigint AS 'factorial_extension', 'factorial'
LANGUAGE C STRICT;
-- Grant execute permission to public (change to appropriate roles if needed)
GRANT EXECUTE ON FUNCTION factorial_extension.factorial(integer) TO PUBLIC;
Step 4:
Write the MakeFile:
MODULES = factorial_extension
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
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);
Top comments (1)
your Makefile does not use g++