Writing your First PostgreSQL Extension: A Guide to pg_query_filter

PostgreSQL is famous for its extensibility. While many people use it for its robust SQL engine, its true power lies in the ability to extend its core functionality using C (and other languages).

In this blog, we will walk through creating a simple but educational extension called pg_query_filter. This extension addresses a common (albeit hypothetical for production) safeguard: preventing accidental data deletion by blocking DELETE statements.

What We Are Building

We will build a PostgreSQL extension that:

1. Hooks into the query execution process.
2. Checks if a query is a DELETE operation.
3. Blocks execution if a custom configuration flag is enabled.
4. Allows dynamic toggling via a runtime configuration parameter (GUC).

1. The Anatomy of an Extension

A minimal C-based PostgreSQL extension requires four main components:

• Makefile: Directs the compilation using PostgreSQL’s build infrastructure (PGXS).
• Control File (.control): Metadata about the extension (version, description, etc.).
• SQL Script (–1.0.sql): Registers SQL-level objects (functions, views).
• C Source (.c): The actual implementation logic.

2. The Implementation

The Makefile

PostgreSQL mandates a standard build system called PGXS. This ensures your extension compiles against the correct PostgreSQL headers and libraries without hardcoding paths.

MODULES = pg_query_filter
EXTENSION = pg_query_filter
DATA = pg_query_filter--1.0.sql
PGFILEDESC = "pg_query_filter - filter queries based on rules"
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Key Point: PG_CONFIG = pg_config determines which PostgreSQL installation you are building against.

The Control File

The pg_query_filter.control file tells PostgreSQL how to handle the extension.

# pg_query_filter extension

comment = 'Filter queries based on rules'
default_version = '1.0'
module_pathname = '$libdir/pg_query_filter'
relocatable = true

The C Code (pg_query_filter.c)

This is where the magic happens. We will use a Hook. PostgreSQL exposes function pointers (hooks) at critical stages of processing. We will use ExecutorStart_hook.

Headers and Magic

First, include necessary headers and declare the magic block that ensures binary compatibility.

#include "postgres.h"
#include "fmgr.h"
#include "executor/executor.h"
#include "utils/guc.h"
#include "nodes/pg_list.h"
PG_MODULE_MAGIC;

Globals and GUCs

We need a variable to store the original hook (so we can call it later) and a boolean configuration variable (GUC).

static ExecutorStart_hook_type prev_ExecutorStart = NULL;
static bool pgqf_enabled = true;

The _PG_init Function

This function runs when the library is loaded. We register our GUC and install our hook here.

void _PG_init(void)
{
    /* Define 'pg_query_filter.enabled' */
    DefineCustomBoolVariable("pg_query_filter.enabled",
                             "Enable or disable query filtering",
                             NULL,
                             &pgqf_enabled,
                             &pgqf_enabled,
                             true,
                             PGC_USERSET,
                             NULL, NULL, NULL);
    /* Save the previous hook and install ours */
    prev_ExecutorStart = ExecutorStart_hook;
    ExecutorStart_hook = pgqf_executor_start;
}

The Hook Logic

Our custom function pgqf_executor_start implements the logic.

static void pgqf_executor_start(QueryDesc *queryDesc, int eflags)
{
    /* Check logic only if enabled */
    if (pgqf_enabled)
    {
        /* Block DELETE statements */
        if (queryDesc->operation == CMD_DELETE)
        {
            ereport(ERROR,
                    (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
                     errmsg("pg_query_filter: DELETE statements are blocked!")));
        }
    }
    /* Chain to previous hook or standard executor */
    if (prev_ExecutorStart)
        prev_ExecutorStart(queryDesc, eflags);
    else
        standard_ExecutorStart(queryDesc, eflags);
}

3. Building and Installing

Navigate to your extension directory and run:

make

sudo make install

This compiles the shared object (.so) and copies the files to the PostgreSQL extension directory.

4. Configuration

Because this extension uses hooks, it is best loaded at server start via shared_preload_libraries in postgresql.conf, although for this specific hook, session-level loading (via LOAD) often works for testing.

For consistent behavior, add this to postgresql.conf:

shared_preload_libraries = 'pg_query_filter'

And restart PostgreSQL.

5. Testing the Extension

Connect to your database and create the extension:

CREATE EXTENSION pg_query_filter;

Scenario 1: Blocking a Delete

CREATE TABLE test_data (id int);
INSERT INTO test_data VALUES (1);
DELETE FROM test_data;
-- ERROR:  pg_query_filter: DELETE statements are blocked!

Scenario 2: Toggling the Guard Since we defined the GUC as PGC_USERSET, we can change it in the session:

SET pg_query_filter.enabled = off;
DELETE FROM test_data;
-- DELETE 1

Conclusion

Congratulations! You’ve built a working PostgreSQL extension that interacts with the core executor. This pattern of “Hook -> Intercept -> Action -> Chain” is the foundation for many powerful extensions like pg_stat_statements and auto_explain. Although it is a basic extension. I am new to extension development, this is my take on learning extension development.

See this in action at PGConf India 2026 – Developing PostgreSQL Extensions in C: Hooks, Shared Memory & Best Practices presented by Suresh Dash