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:

• Install
• pig
• dnf
• apt

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

Syntax:

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"}');

Source: README

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.

Core Objects

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.

Template Commands

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

Placeholders

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

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"}');

Requirements

The README lists PostgreSQL 14+, plpgsql, and standard build dependencies such as gcc, make, and PostgreSQL server development headers.