pg_query_rewrite

pg_query_rewrite : Rewrite SQL statements with a PostgreSQL ProcessUtility hook

Overview

Packages

Source

pig build pkg pg_query_rewrite;		# build rpm/deb

Install

Make sure PGDG and PIGSTY repo available:

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

Install this extension with pig:

pig install pg_query_rewrite;		# install via package name, for the active PG version

pig install pg_query_rewrite -v 18;   # install for PG 18
pig install pg_query_rewrite -v 17;   # install for PG 17
pig install pg_query_rewrite -v 16;   # install for PG 16
pig install pg_query_rewrite -v 15;   # install for PG 15
pig install pg_query_rewrite -v 14;   # install for PG 14

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_query_rewrite';

Create this extension with:

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.