DEV Community: Ahmed Mohamed

Migrating Schema from MongoDB to PostgreSQL: A Practical Example

Ahmed Mohamed — Mon, 28 Aug 2023 21:06:16 +0000

Database migrations aren't just for upgrading databases inside the same system. They are also essential when switching between different database systems. We'll walk through the process of converting a schema from MongoDB to PostgreSQL using Python and related modules in this.
The following is just a quick example to know the idea.

Understanding the Scenario

Assume you're working on a social networking platform that allows users to send messages. The data was originally stored in a MongoDB database, but you've decided to migrate it to PostgreSQL for better querying and scalability. The user messages and associated data will be moved from MongoDB to PostgreSQL.

Step 1: Install Required Libraries

Make sure you have the necessary libraries installed:

pip install pymongo psycopg2

Step 2: Fetch Data from MongoDB

Use the pymongo library to connect to the MongoDB database and retrieve the data you want to migrate:

from pymongo import MongoClient

# Connect to MongoDB
mongo_client = MongoClient("mongodb://localhost:27017/")
mongo_db = mongo_client["social_media_db"]
mongo_messages = mongo_db["messages"]

# Fetch messages
messages_to_migrate = list(mongo_messages.find())

Step 3: Set Up PostgreSQL Connection

Create a PostgreSQL database and set up the schema to match the MongoDB data. Use the psycopg2 library to connect to the PostgreSQL database and insert data:

import psycopg2

# Connect to PostgreSQL
conn = psycopg2.connect(
    host="localhost",
    database="social_media_db",
    user="your_username",
    password="your_password"
)
cur = conn.cursor()

# Create schema in PostgreSQL if not already done
create_schema_query = """
CREATE TABLE IF NOT EXISTS messages (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    content TEXT
);
"""
cur.execute(create_schema_query)
conn.commit()

# Insert migrated messages
for message in messages_to_migrate:
    insert_query = "INSERT INTO messages (user_id, content) VALUES (%s, %s);"
    cur.execute(insert_query, (message["user_id"], message["content"]))
    conn.commit()

# Close connections
cur.close()
conn.close()

Step 4: Data Transformation (Optional)

If data types or structures between MongoDB and PostgreSQL are different, you might need to transform the data before insertion.

Step 5: Testing and Verification

Before using the new PostgreSQL-based system, thoroughly test the migrated data to ensure accuracy and data integrity.

Conclusion

Migrating schema and data between database systems is a difficult operation that needs meticulous planning, data transformation, and validation. In this example, we used Python and the pymongo and psycopg2 modules to convert user messages from MongoDB to PostgreSQL. Data is retrieved from the source database, the destination schema is configured, and data is transformed and inserted into the target database. Database migration between different systems can be accomplished successfully with careful planning and execution, allowing enterprises to capitalize on the capabilities of each database platform.

Navigating the Landscape of Database Migration: Benefits and Best Practices

Ahmed Mohamed — Mon, 28 Aug 2023 21:04:41 +0000

Data management is critical in the ever-changing world of software development. The need to rearrange or relocate data between storage systems emerges when applications develop and evolve. This is where the idea of database migration comes in. In this detailed blog post, we'll go through what database migration is, why it's important, and the recommended practices for successful migrations.

Understanding Database Migration

The process of transferring data and schema objects from one database system to another is referred to as database migration. This procedure entails transporting data, preserving data integrity, changing data to conform to the new schema, and ensuring that the application continues to function normally after the migration.

Why Use Database Migration?

Platform Upgrades: Migrating to a new version of a database system guarantees that data is compatible with the new features and optimizations, which improves performance and security.
Switching Database Vendors: If your organization decides to transfer to a different database provider, migration allows you to port your data and applications without having to completely rebuild them.
Datacenter Relocation: Database migration is used during datacenter migrations to move data from one location to another without impacting business operations.
Scaling: Scalability is ensured by migrating data to a more resilient database system, which can manage increasing data volumes and user demands.
Cloud Adoption: Migration ensures that your data is securely and efficiently transferred to the cloud-based database when transferring to the cloud.

Benefits of Database Migration

Performance Improvement: Migrating to a more optimized database system can enhance query performance and overall application responsiveness dramatically.
Cost Efficiency: Migration to open-source or cloud-based databases can save money on licensing and operations.
Scalability: Migrating to a more scalable system allows you to successfully handle increased data loads and user traffic.
Security: Database migrations frequently involve upgrading to systems with improved security measures, which better secure critical data.
New Features: Migrations enable you to take advantage of new features and advancements provided by modern database systems.

Best Practices for Successful Database Migration

Planning: A well-defined migration strategy describes the scope, goals, dates, and resources needed for a successful migration.
Data Analysis: Before designing your migration approach, properly analyze your data to understand its structure, dependencies, and linkages.
Testing: Run extensive tests on a dummy production environment to discover potential issues and confirm the migration procedure.
Backup and Rollback strategy: Make backups of your data before migrating, and have a backup strategy in place in case of unanticipated complications.
Data Transformation: Ensure that data is accurately transformed and mapped to match the new schema, while maintaining data integrity.
Communication: To minimize disruptions, keep stakeholders informed about the relocation process and anticipated downtimes.
Monitoring and Optimization: After the migration is complete, monitor the moved system and optimize queries or configurations.

Leveraging PostgreSQL Full Text Search for Enhanced Content Discovery in Content Management Systems

Ahmed Mohamed — Mon, 28 Aug 2023 21:02:53 +0000

CMS (information Management Systems) have become essential for properly managing and delivering digital information. However, as the volume of content expands, it might be difficult to identify useful information. PostgreSQL's Full Text Search (FTS) features shine here. In this post, we'll look at how PostgreSQL FTS may be used to provide robust search capabilities within CMS platforms.

Example Scenario: Building a Blog Post Search

Consider a CMS that hosts a large number of blog articles. Traditional searches may focus on exact keyword matches, ignoring content that use similar phrases or synonyms. Let's have a look at how PostgreSQL FTS can enhance the search experience:

Step 1: Creating a Full Text Search Index

To get started, create a full-text search index on the relevant columns of your database table. In this case, we'll index the title and content columns of the blog_posts table:

CREATE INDEX blog_posts_fts_idx
ON blog_posts
USING gin(to_tsvector('english', title || ' ' || content));

Step 2: Implementing FTS Queries

Now, let's explore various FTS queries to enhance content discovery:

1. Basic Text Search:

Retrieve all blog posts containing the word "technology":

SELECT id, title
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'technology');

2. Ranking Results:

Rank the results by relevance using the ts_rank function:

SELECT id, title, ts_rank(to_tsvector('english', title || ' ' || content), to_tsquery('english', 'technology')) AS rank
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'technology')
ORDER BY rank DESC;

3. Phrase Search:

Search for blog posts containing the phrase "machine learning":

SELECT id, title
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ phraseto_tsquery('english', 'machine learning');

4. Synonyms and Stemming:

Consider synonyms and stemming for a broader search:

SELECT id, title
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'analyzing | analysing');

5. Highlighting Matched Words:

Highlight matched words using the ts_headline function:

SELECT id, title, ts_headline('english', title || ' ' || content, to_tsquery('english', 'database')) AS highlighted_text
FROM blog_posts
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database');

Harnessing the Power of PostgreSQL Full Text Search: Examples and Applications

Ahmed Mohamed — Mon, 28 Aug 2023 21:01:23 +0000

Before we go into the examples, let's first define PostgreSQL Full Text Search. FTS examines and processes text data to extract useful results based on linguistic and semantic patterns, as opposed to simple string matching, which only discovers precise matches. This makes it perfect for situations in which users need to search for specific words or phrases inside unstructured or semi-structured text data.

Inverted indexing is a technique used by PostgreSQL FTS in which each unique word (or token) inside the indexed text is associated with a list of locations where it appears. This allows for speedier search queries and more accurate search result ranking.

Example 1: E-Commerce Search

E-commerce platforms are one of the most common places where PostgreSQL Full Text Search is used. Consider a user who is looking for a certain product in a huge inventory. If the user's query is even slightly different from the product name, traditional exact-match searches may miss relevant listings. FTS allows the search algorithm to consider synonyms, misspellings, and wording variants.

For example, if a user searches for "comfortable running shoes," the FTS system may offer results such as "running sneakers for comfort" or "best shoes for jogging." This amount of search versatility improves the user experience and increases the possibility of sales.

Example 2: Content Management Systems

PostgreSQL FTS is extremely useful in content-heavy applications like as content management systems (CMS) or blogging platforms. More than just matching keywords is required when searching through a large collection of articles, blog posts, or documents. FTS provides powerful search capabilities such as:

FTS may rank search results based on relevance, taking into account characteristics such as keyword frequency, position, and proximity.

Phrasal Search: Users can search for exact phrases even if the words in the text are not nearby. FTS understands word variations and synonyms, resulting in complete results.

This feature enables users to find relevant content fast, enabling effective information retrieval.

Example 3: Social Media Platforms

From posts and comments to hashtags and user profiles, social media platforms are constantly bombarded with text-based material. Implementing PostgreSQL FTS improves the search experience by allowing for:

Hashtag Searches: Even if the precise hashtag is not used, users can search content related with specific hashtags.
User Search: Searching for users becomes more flexible, taking into account usernames, real names, and even misspellings.
Trending Topics: FTS can help find trending topics by analyzing frequently used terms.

Example 4: Job Portals

To match job seekers with appropriate positions, employment portals must have accurate and efficient search capabilities. PostgreSQL FTS can improve job search functionality by doing the following:

Location-Based Search: Job seekers can search for vacancies based on their location or closeness to a specific area.
Skill Matching: FTS can find and prioritize job advertisements that match a candidate's abilities and qualifications.

Industry Jargon and Specific Keywords: Industry jargon and specific keywords are recognized, resulting in more exact search results.
This guarantees that job seekers find employment that are a good fit for their skills and preferences.

Unleashing the Benefits: Why Embrace PostgreSQL Full Text Search

Ahmed Mohamed — Mon, 28 Aug 2023 20:58:11 +0000

PostgreSQL has proven to be a strong database system in the field of modern data management, acclaimed for its versatility and broad feature set. PostgreSQL Full Text Search (FTS) stands out as a fantastic tool for unleashing the entire potential of text-based data exploration among its features. This article explores the compelling reasons to use PostgreSQL FTS, emphasizing its benefits and real-world applications.

Understanding PostgreSQL Full Text Search

Before we get into the benefits, let's go through what PostgreSQL Full Text Search is. FTS uses linguistic and semantic analysis to give contextually relevant search results, as opposed to traditional database search approaches that focus on exact word matching. This means that, rather than merely looking for a keyword, FTS takes into account word variants, synonyms, and word proximity to provide a more comprehensive and accurate search experience.

Advantages of PostgreSQL Full Text Search

Flexible Search Capabilities:

PostgreSQL FTS takes into account the intricacies of human language. Users can search for synonyms, similar terms, and even misspellings, allowing them to find content that might otherwise go unnoticed with typical searches.

Ranking and Relevance:

FTS presents a ranking algorithm that ranks search results depending on their relevance. This means that users will get the most relevant content at the front of their search results, which will improve the user experience.

Support for Complex Queries:

Advanced query options supported by FTS include phrase searches, boolean operators (AND, OR, NOT), proximity searches, and wildcard searches. This enables users to construct complex searches that capture unique needs.

Efficiency and Performance:

The underlying indexing methods of PostgreSQL allow FTS to provide speedy search results even in databases with millions of records. Inverted indexing dramatically reduces search times while maintaining good performance.

Language Support:

FTS is not restricted to English; it supports a diverse range of languages, enabling global applications without sacrificing accuracy.

Integration with Other PostgreSQL Features:

PostgreSQL FTS works in tandem with other PostgreSQL functionalities. Combining FTS with normal SQL queries ensures consistency while also simplifies your development process.

Conclusion

PostgreSQL Full Text Search is an amazing tool that completely changes the way we interact with text-based data. Its ability to understand language semantics and give contextually appropriate results enables organizations to design user-friendly search experiences. PostgreSQL FTS plays a critical role in enabling data exploration, boosting engagement, and ultimately contributing to the success of a wide range of applications, from e-commerce to CMS and beyond. Adopting PostgreSQL FTS is a strategic move that may propel your data-driven initiatives to new heights.

Installing EDB Postgres Advanced Server on Ubuntu 22.04

Ahmed Mohamed — Fri, 14 Jul 2023 18:03:12 +0000

Introduction

EDB EnterpriseDB is a robust and feature-rich open-source database management system (DBMS) built upon PostgreSQL. If you're considering leveraging EDB EnterpriseDB for your Linux environment, this step-by-step guide will walk you through the installation process.

Installation steps

update your package manager

sudo apt update

. Set up the EDB repository
Setting up the repository is a one-time task. If you have already set up your repository, you don't need to perform this step.

To determine if your repository exists, enter this command:

apt-cache search enterprisedb

If no output is generated, the repository isn't installed.

To set up the EDB repository:

Go to EDB repositories.
Select the button that provides access to the EDB repo.
Select the platform and software that you want to download.

Install the package

sudo apt-get -y install edb-as<xx>-server

Where <xx> is the version of the EDB Postgres Advanced server you are installing. For example, if you are installing version 15, the package name would be edb-as15-server.

To install an individual component:

sudo apt-get -y install <package_name>

Initial configuration

This section steps you through getting started with your cluster including logging in, ensuring the installation was successful, connecting to your cluster, and creating the user password.

To work in your cluster, login as the enterprisedb user. Connect to the database server using the psql command line client (although you can use a client of your choice with the appropriate connection string).

sudo su - enterprisedb

psql edb

The server runs with the peer or ident permission by default. You can change the authentication method by modifying the pg_hba.conf file.

Before changing the authentication method, assign a password to the database superuser, enterprisedb. For more information on changing the authentication, see modifying the pg_hba.conf file.

ALTER ROLE enterprisedb IDENTIFIED BY password;

Ref:
EDB DOCs

Unlocking the power of full-text search in PostgreSQL

Ahmed Mohamed — Fri, 14 Jul 2023 18:02:45 +0000

Introduction

PostgreSQL, one of the most popular open source relational database management systems, provides powerful and feature-rich full-text search. With PostgreSQL full-text search, businesses can improve the search experience by enabling complex text searches, ranking results by relevance, and implementing advanced language features. In this blog post, we will explore the power and potential of full-text search in PostgreSQL.

Understanding Full Text Search

Full-text search goes beyond simple pattern matching and allows users to search large volumes of text efficiently. PostgreSQL's full-text search offers several key features, including native search, ranking, phrase search, and support for multiple languages. Native search helps match different forms of the same word, ranking assigns points to search results based on relevance, and phrase search helps find exact terms. In addition, PostgreSQL full-text search supports advanced language features such as thesaurus and stopword lists, improving the accuracy and precision of search results.

Set up full-text search

To start using full-text search in PostgreSQL, the database must be configured correctly. First, make sure that the text search profile is installed and available. Then define a full-text search index on the desired column(s) of the table containing the text data. Indexes can be created using the tsvector and tsquery data types. Once the index is in place, queries can be created using the @@ operator to perform a full-text search on the indexed data.

Do a full-text search

PostgreSQL provides a wide range of operators and functions for building powerful full-text search queries. These include @@ to match text to a query, @@plainto_tsquery to generate a query from plain text, and @@to_tsquery to generate a query from a sentence. In addition, PostgreSQL allows the use of Boolean operators (AND, OR, NOT) to combine search conditions and further refine the results. By leveraging these operators and functions, enterprises can implement sophisticated and precise full-text search to meet the diverse needs of users.

Full-text search optimization

To ensure optimal performance, PostgreSQL provides various configuration options to fine-tune full-text search. These options include modifying the search ranking algorithm, adjusting the default dictionary, and customizing the original list and stopwords. In addition, PostgreSQL allows the creation of custom dictionaries and attachments for specific languages or domains. By fine-tuning these parameters, businesses can optimize full-text search to suit their specific needs and improve search accuracy, relevance, and performance.

Conclusion

PostgreSQL's full-text search capability is a powerful feature that enables companies to implement advanced text retrieval and search functionality. By leveraging features like native generation, ratings, and language support, organizations can improve the search experience, deliver more accurate results, and unlock valuable insights from data. documents are stored in their database. PostgreSQL.

PostgreSql DOCs

Unleashing the Potential of EDB EnterpriseDB: Embracing Open-Source Database Solutions

Ahmed Mohamed — Fri, 14 Jul 2023 17:47:25 +0000

Introduction

In the realm of enterprise grade databases EDB EnterpriseDB emerges as a remarkable and adaptable open source database management system (DBMS) that empowers businesses to achieve outstanding performance, scalability, and cost effectiveness. Developed by EnterpriseDB. EDB Postgres combines the dependability and robustness of the PostgreSQL open source database with enterprise grade functionalities and comprehensive support.

This article delves into the fundamental features and advantages of EDB EnterpriseDB shedding light on why it is gaining popularity among organizations in search of a resilient and flexible database solution.

Powerful Features

PostgreSQL Foundation:
At its core. EDB EnterpriseDB is constructed upon the PostgreSQL database, renowned for its unwavering reliability, data integrity, and extensibility.
By preserving compatibility with PostgreSQL. EDB Postgres ensures smooth transitions for existing applications and tools to embrace EDB EnterpriseDB without significant modifications. This foundation empowers organizations to leverage the extensive ecosystem and community support present within PostgreSQL—including an array of plugins, extensions, and tools.
Advanced Performance and Scalability:
EDB EnterpriseDB is designed to handle mission critical workloads with unparalleled performance and scalability. It incorporates a multitude of performance enhancing features including parallel processing, query optimization techniques, and advanced indexing mechanisms. Moreover this database encompasses built in sharding capabilities that effortlessly enable horizontal scaling—facilitating seamless management of growing data volumes and high traffic scenarios for organizations.
Enterprise-Grade Security:
Security is given high priority by EDB EnterpriseDB, which excels in this area. It offers a wide range of security features such as data encryption both at rest and in transit, secure authentication mechanisms, role-based access control (RBAC), and fine-grained auditing capabilities. With these features, EDB EnterpriseDB ensures that sensitive information remains protected from unauthorized access and complies with industry regulations. Additionally, it provides comprehensive support options to promptly resolve any critical issues.
High Availability and Disaster Recovery:
High Availability and Disaster Recovery are crucial for businesses, as downtime can have severe consequences. EDB EnterpriseDB offers various features to ensure continuous operations and minimize downtime. These include built-in replication mechanisms, automatic failover, and support for geographically distributed clusters. Organizations can achieve near-zero downtime and rapid disaster recovery in case of system failures or natural disasters.
Extensibility and Compatibility:
EDB EnterpriseDB provides Extensibility and Compatibility by offering extensive support for SQL, making it compatible with a wide range of applications and frameworks. It also provides support for programming languages such as Python, Java, and C++, allowing developers to work in their preferred language. The extensibility of EDB EnterpriseDB is enhanced by the vast ecosystem of PostgreSQL, enabling the integration of custom-built or third-party extensions to meet specific business requirements.

Benefits for Organizations

Cost-Effectiveness:
Organizations benefit from the Cost-Effectiveness of EDB EnterpriseDB, as it significantly reduces licensing and maintenance costs compared to proprietary database solutions. The open-source nature of EDB Postgres eliminates the need for expensive licensing fees, while comprehensive support ensures expert assistance when needed.
Flexibility and Agility:
Flexibility and Agility are provided by EDB EnterpriseDB, allowing organizations to adapt and scale their database environment according to evolving business needs. Its compatibility with PostgreSQL facilitates seamless migration from existing database systems, while the extensive feature set enables customization and optimization based on specific requirements. This agility enables organizations to respond quickly to changing market dynamics and stay ahead of the competition.
Reliability and Support:
Reliability and Support are crucial for organizations, and EnterpriseDB has a proven track record in providing reliable and robust support for EDB EnterpriseDB. Organizations benefit from timely bug fixes, security patches, and regular updates to ensure the stability and longevity of their database infrastructure. Comprehensive support options, including 24/7 support, guarantee that critical issues can be resolved promptly.

Ref:
EDB DOCs

Debugging Session for AGE using VSCode

Ahmed Mohamed — Wed, 28 Jun 2023 21:05:17 +0000

In this blog we will have a debugging session using vscode it looks similar to the previous blog using GDB but here we will add some configurations for vscode.

After opening vscode on the age project directory, you will see .vscode directory. Make sure that to put all the following configurations
Note: replace 'yourUserName' in the following files with your current system username
launch.json

{
    // Use IntelliSense to learn about possible attributes.
    // Hover to view descriptions of existing attributes.
    // For more information, visit: https://go.microsoft.com/fwlink/?linkid=830387
    "version": "0.2.0",
    "configurations": [
        {
            "name": "(gdb) Attach",
            "type": "cppdbg",
            "request": "attach",
            "program": "/home/yourUserName/postgresql-14.7/bin/postgres",
            "MIMode": "gdb",
            "setupCommands": [
                {
                    "description": "Enable pretty-printing for gdb",
                    "text": "-enable-pretty-printing",
                    "ignoreFailures": true
                },
                {
                    "description": "Set Disassembly Flavor to Intel",
                    "text": "-gdb-set disassembly-flavor intel",
                    "ignoreFailures": true
                }
            ]
        }

    ]
}

settings.json

{
    "files.associations": {
        "postgres.h": "c",
        "genam.h": "c",
        "heapam.h": "c",
        "tableam.h": "c",
        "table.h": "c",
        "htup_details.h": "c",
        "skey.h": "c",
        "pg_operator.h": "c",
        "namespace.h": "c"
    }
}

c_cpp_properties.json

{
    "configurations": [
        {
            "name": "Linux",
            "includePath": [
                "/usr/include/**/**",
                "${workspaceFolder}/**",
                "/usr/include/**",
                "/usr/local/pgsql/include/**",
                "/usr/local/pgsql/include/server/**",
                "/home/yourUserName/postgresql-14.7/src/**",
                "/home/yourUserName/postgresql-14.7/**",
                "/home/yourUserName/postgresql-14.7/src/backend/**",
                "/usr/local/pgsql/lib/**",
                "${workspaceFolder}/src/include/**"
            ],
            "defines": [],
            "compilerPath": "/usr/bin/gcc",
            "cStandard": "c11",
            "cppStandard": "gnu++17",
            "intelliSenseMode": "linux-gcc-x64",
            "configurationProvider": "ms-vscode.makefile-tools"
        }
    ],
    "version": 4
}

Now we can start debugging

START postgres server and CONNECT through psql session
In the psql terminal get the session pid using command SELECT pg_backend_pid();
Set your breakpoints
press F5 then type PID that you got in a previous step

run a query on the psql terminal and the debugging will begin.

Ref:
apache age repo

Debugging Session for AGE using GDB

Ahmed Mohamed — Wed, 28 Jun 2023 20:39:21 +0000

After have Postgres configured with enabling debugging flags and a running Postgres instance. An open session running and age extension is loaded

Get PID of the current session

SELECT pg_backend_pid();

Open new terminal tab and run sudo gdb to run the debugger
Attach gdb to to the PID that we got from the previous step e.g. PID: 123

(gdb) attach 123

Add a break point to a specific line or function at some where

(gdb) b <function_name>

The following are The basic GDB commands:

b for breakpoint, (b )
c for continue - continues to the next breakpoint
n for next line
s for step into
p for print, (p *) for pointers
d for delete all breakpoints
q for quit

Ref:
apache age repo
GDB Docs

AGE Contribution Guide

Ahmed Mohamed — Wed, 28 Jun 2023 19:31:27 +0000

In the previous blog we wrote add_two_numbers function implementation.

file name is add_numbers.c

#include "postgres.h"
#include <string.h>
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_one);

Datum
add_two_numbers(PG_FUNCTION_ARGS)
{
    int32   arg1 = PG_GETARG_INT32(0);
    int32   arg2 = PG_GETARG_INT32(1);

    PG_RETURN_INT32(arg1 + arg2);
}

Now, let's add this function to age.
save the file in the following path: src/backend/commands

then add the next line to makefile on the top directory of the project
src/backend/commands/add_numbers.o
add this line in OBJS section.

Add the signature to the age--1.3.0.sql at the end of the file


CREATE FUNCTION ag_catalog.add_numbers()
RETURNS Integer
LANGUAGE c
AS 'MODULE_PATHNAME';

then run the following commands in the project directory

sudo make clean PG_CONFIG=/usr/local/pgsql/bin/pg_config
sudo make PG_CONFIG=/usr/local/pgsql/bin/pg_config
sudo make install PG_CONFIG=/usr/local/pgsql/bin/pg_config

Now we are done, after running the postgres server you can use this function as follows

select * from ag_catalog.add_numbers(2,3);

apache age github repo
Create a Custom Function in Postgres

Create a Custom Function in Postgres

Ahmed Mohamed — Wed, 28 Jun 2023 19:03:27 +0000

User-defined functions can be written in C language. Such functions are compiled into dynamically loadable objects.

function implementation in c

The version-1 calling convention relies on macros to suppress most of the complexity of passing arguments and results. the declaration of a version-1 function will be as follows

Datum funcname(PG_FUNCTION_ARGS)

before that we will add the macro call

PG_FUNCTION_INFO_V1(funcname);

to declare the function name

then the function implementation of adding two numbers will be as follows.

#include "postgres.h"
#include <string.h>
#include "fmgr.h"

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(add_one);

Datum
add_two_numbers(PG_FUNCTION_ARGS)
{
    int32   arg1 = PG_GETARG_INT32(0);
    int32   arg2 = PG_GETARG_INT32(1);

    PG_RETURN_INT32(arg1 + arg2);
}

notice that PG_RETURN_xxx or PG_GETARG_xxx
where xxx refer to the variable datatype

then, we could define the functions to PostgreSQL with commands like this

CREATE FUNCTION add_one(integer) 
RETURNS integer
AS 'DIRECTORY/funcs', 'add_two_numbers'
LANGUAGE C;

Ref:
postgresql documentation