DEV Community

Ahmed Hisham
Ahmed Hisham

Posted on

4

Here's How To Create Your First PostgrSQL Extension Part 4

Continuing on part3 we got into the stage where we have .sql file left to build the extension.

We should name the file as follows add_two_numbers--0.0.1.sql including the version.

The file will contain the following:

CREATE OR REPLACE FUNCTION
add_two_numbers(int,int) RETURNS int AS 'MODULE_PATHNAME','add_two_numbers'
LANGUAGE C STRICT;
Enter fullscreen mode Exit fullscreen mode

Then we finally can install the extension:

sudo make install
Enter fullscreen mode Exit fullscreen mode

You should get an output similar to:

gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC -I. -I./ -I/usr/local/pgsql-13/include/server -I/usr/local/pgsql-13/include/internal  -D_GNU_SOURCE   -c -o add_two_numbers.o add_two_numbers.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -g -O2 -fPIC add_two_numbers.o -L/usr/local/pgsql-13/lib   -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql-13/lib',--enable-new-dtags  -shared -o add_two_numbers.so
/usr/bin/mkdir -p '/usr/local/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql-13/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql-13/lib'
/usr/bin/install -c -m 644 .//add_two_numbers.control '/usr/local/pgsql-13/share/extension/'
/usr/bin/install -c -m 644 .//add_two_numbers--0.0.1.sql  '/usr/local/pgsql-13/share/extension/'
/usr/bin/install -c -m 755  add_two_numbers.so '/usr/local/pgsql-13/lib/'
Enter fullscreen mode Exit fullscreen mode

This should mean that the extension is installed successfully. Now it's time to run a postgres instance and test the extension, start a postgres instance from the binaries directory, in my case it will be from usr/local/pgsql-13/bin:

./pg_ctl start -l logfile -D ./data
Enter fullscreen mode Exit fullscreen mode

Then the instance:

./psql postgres
Enter fullscreen mode Exit fullscreen mode

Create the extension:

postgres=# CREATE EXTENSION add_two_numbers;
CREATE EXTENSION
Enter fullscreen mode Exit fullscreen mode

Run a query to test the function:

postgres=# SELECT add_two_numbers(1,3);
 add_two_numbers
-----------------
               4
(1 row)
Enter fullscreen mode Exit fullscreen mode

It worked!, congratulations for building your first extension, I hope that was helpful!.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read more →

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more