pg_query_rewrite

Overview

Requires shared_preload_libraries=pg_query_rewrite.

Version

Build

You can build the RPM / DEB packages for pg_query_rewrite using pig build:

pig build pkg pg_query_rewrite         # build RPM / DEB packages

Install

You can install pg_query_rewrite directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

pig install pg_query_rewrite;          # Install for current active PG version

pig ext install -y pg_query_rewrite -v 18  # PG 18
pig ext install -y pg_query_rewrite -v 17  # PG 17
pig ext install -y pg_query_rewrite -v 16  # PG 16
pig ext install -y pg_query_rewrite -v 15  # PG 15
pig ext install -y pg_query_rewrite -v 14  # PG 14

dnf install -y pg_query_rewrite_18       # PG 18
dnf install -y pg_query_rewrite_17       # PG 17
dnf install -y pg_query_rewrite_16       # PG 16
dnf install -y pg_query_rewrite_15       # PG 15
dnf install -y pg_query_rewrite_14       # PG 14

apt install -y postgresql-18-pg-query-rewrite   # PG 18
apt install -y postgresql-17-pg-query-rewrite   # PG 17
apt install -y postgresql-16-pg-query-rewrite   # PG 16
apt install -y postgresql-15-pg-query-rewrite   # PG 15
apt install -y postgresql-14-pg-query-rewrite   # PG 14

Preload:

shared_preload_libraries = 'pg_query_rewrite';

Create Extension:

CREATE EXTENSION pg_query_rewrite;

Usage

Source: README

pg_query_rewrite rewrites an exact source SQL statement into a predefined target statement using rules stored in shared memory.

Required setup

-- postgresql.conf
shared_preload_libraries = 'pg_query_rewrite'
pg_query_rewrite.max_rules = 10

CREATE EXTENSION pg_query_rewrite;

Rule management

SELECT pgqr_add_rule('select 10;', 'select 11;');
SELECT pgqr_rules();
SELECT pgqr_remove_rule('select 10;');
SELECT pgqr_truncate();

• pgqr_add_rule(source, target): add a rewrite rule.
• pgqr_remove_rule(source): remove one rule.
• pgqr_truncate(): remove all rules.
• pgqr_rules(): inspect current shared-memory rules and rewrite counts.

Caveats

• Matching is exact: case, whitespace, and semicolons must match character-for-character.
• Parameterized SQL is not supported.
• Maximum statement length is hard-coded at 32 KB.
• Rules are not persisted; they vanish on restart unless you reapply them.