pg_fsql

Overview

Release tag 1.1.0 still ships extension SQL version 1.0; shared_preload_libraries is optional and only needed for session-start GUC availability.

Version

Build

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

pig build pkg pg_fsql         # build RPM / DEB packages

Install

You can install pg_fsql 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_fsql;          # Install for current active PG version

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

dnf install -y pg_fsql_18       # PG 18
dnf install -y pg_fsql_17       # PG 17
dnf install -y pg_fsql_16       # PG 16
dnf install -y pg_fsql_15       # PG 15
dnf install -y pg_fsql_14       # PG 14

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

Create Extension:

CREATE EXTENSION pg_fsql CASCADE;  -- requires: plpgsql

Usage

Sources: README, control file

pg_fsql is a recursive SQL template engine for PostgreSQL. It combines a C-based placeholder renderer with PL/pgSQL template execution, hierarchical template composition, and optional SPI plan caching. The upstream project emphasizes that it does not require superuser privileges.

Quick Start

CREATE EXTENSION pg_fsql;

INSERT INTO fsql.templates (path, cmd, body)
VALUES ('user_count', 'exec',
        'SELECT jsonb_build_object(''total'', count(*))
         FROM users WHERE status = {d[status]!r}');

SELECT fsql.run('user_count', '{"status":"active"}');
SELECT fsql.render('user_count', '{"status":"active"}');

Catalog Tables

The extension installs two main catalog tables:

fsql.templates (
    path varchar(500) primary key,
    cmd varchar(50),
    body text,
    defaults text,
    cached boolean default false
)

fsql.params (
    key_param varchar(255) primary key,
    type_param varchar(255) not null
)

path is dot-separated and defines the template hierarchy.

Commands and Placeholders

The README documents six command types:

• exec to execute SQL and return jsonb
• ref to redirect to another template
• if to choose a child branch
• exec_tpl to execute SQL and re-render the result as a template
• map to collect children into a JSON object
• NULL for text fragments inserted into parents

The renderer supports placeholders such as:

• {d[key]}
• {d[key]!r} for quote_literal
• {d[key]!j} for JSONB literals
• {d[key]!i} for quote_identifier

The special key _self injects the full input JSON object.

Public API

The upstream public functions include:

• fsql.run(path, data, debug) to execute a template tree
• fsql.render(path, data) to preview rendered SQL
• fsql.tree(path) to inspect hierarchy
• fsql.explain(path, data) to trace expansion
• fsql.validate() to check templates
• fsql.depends_on(path) to inspect dependencies
• fsql.clear_cache() to free cached SPI plans

Hierarchical Example

INSERT INTO fsql.templates (path, cmd, body) VALUES
    ('report', 'exec',
     'SELECT jsonb_build_object(''data'', array_agg(row_to_json(t)))
      FROM (SELECT {d[cols]} FROM {d[src]} {d[where]}) t'),
    ('report.cols',  NULL, 'id, name, email'),
    ('report.src',   NULL, 'customers'),
    ('report.where', NULL, 'WHERE city = {d[city]!r}');

SELECT fsql.run('report', '{"city":"Moscow"}');
SELECT fsql.render('report', '{"city":"Moscow"}');

Notes

The README lists PostgreSQL 14+ and plpgsql. The control file currently declares SQL extension version 1.0, even though the package task tracks release 1.1.0. No official release notes were published in the repository; the user-facing README still documents the same core API and behavior.