pg_fsql 1.1.0

This Release

pg_fsql 1.1.0

Date

2026-02-20

Status

Stable

Abstract

Recursive SQL template engine for PostgreSQL

Description

Hierarchical template composition and execution — pure C + PL/pgSQL. Dot-path template trees, 6 command types (exec, ref, if, exec_tpl, map, NULL), SPI plan caching, jsonb in/out, {d[key]} / !r / !j / !i placeholder formats, _self virtual key. No plpython3u, no superuser required.

Released By

ykhmyrov

License

PostgreSQL

Resources

Special Files

Tags

Extensions

pg_fsql 1.1.0: Recursive SQL template engine

Documentation

CHANGELOG: Changelog

README

pg_fsql

Recursive SQL template engine for PostgreSQL.

Hierarchical template composition and execution — pure C + PL/pgSQL, no plpython3u.

Features

C renderer — fast {d[key]} / {d[key]!r} / {d[key]!j} / {d[key]!i} placeholder substitution
SPI plan cache — optional per-template prepared plan caching
Recursive engine — hierarchical dot-path templates with parent-child composition
6 cmd types — exec, ref, if, exec_tpl, map, NULL
Safe execution — parameterized queries via fsql.params type catalog
No superuser — superuser = false, safe for shared hosting
Debug trace — RAISE NOTICE logging with _debug = true
Legacy compatible — old data_algorithms names still work

Quick Start

# Build & install
cd pg_fsql
make && sudo make install

# Create extension
psql -d mydb -c "CREATE EXTENSION pg_fsql;"

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

-- Run it
SELECT fsql.run('user_count', '{"status":"active"}');
-- {"total": 42}

-- Preview generated SQL (dry-run)
SELECT fsql.render('user_count', '{"status":"active"}');
-- SELECT jsonb_build_object('total', count(*)) FROM users WHERE status = 'active'

Requirements

PostgreSQL 14+ (tested on 17.8)
plpgsql (included by default)
Build: gcc, make, postgresql-server-dev-XX

Installation

Linux

# Ubuntu / Debian
sudo apt-get install gcc make postgresql-server-dev-17

# RHEL / CentOS
sudo yum install gcc make postgresql17-devel

cd pg_fsql/
make PG_CONFIG=/usr/bin/pg_config
sudo make install PG_CONFIG=/usr/bin/pg_config
psql -d mydb -c "CREATE EXTENSION pg_fsql;"

Docker

docker cp pg_fsql postgres:/tmp/pg_fsql/
docker exec postgres bash -c "cd /tmp/pg_fsql && make install"
docker exec postgres psql -U postgres -d mydb -c "CREATE EXTENSION pg_fsql;"

Verify

SELECT fsql._c_render('hello {d[name]}', '{"name":"world"}'::jsonb);
-- hello world

Schema

fsql.templates (
    path      varchar(500) PRIMARY KEY,   -- dot-separated hierarchy
    cmd       varchar(50),                -- exec | ref | if | exec_tpl | map | NULL
    body      text,                       -- SQL template or target path
    defaults  text,                       -- JSON defaults
    cached    boolean DEFAULT false       -- enable SPI plan caching
)

fsql.params (
    key_param   varchar(255) PRIMARY KEY, -- parameter name
    type_param  varchar(255) NOT NULL     -- PostgreSQL cast type
)

Command Types

cmd	Action	body contains
`NULL`	Text fragment, substituted into parent as `{d[child_name]}`	Raw text
`exec`	Execute SQL, return jsonb	SQL returning jsonb
`ref`	Redirect to another template	Target path
`if`	Conditional branch — evaluate body, pick child by result	SQL returning branch name
`exec_tpl`	Execute SQL, then re-render result as template	SQL
`map`	Collect children into JSON object	Template body

Legacy aliases: exejson = exec, templ = ref, json = map, exejsontp = exec_tpl

Template Hierarchy

Templates form a tree via dot-separated paths:

report                    ← root (cmd=exec)
  report.columns          ← fragment (cmd=NULL, body='id, name')
  report.source           ← fragment (cmd=NULL, body='orders')
  report.filter           ← redirect (cmd=ref, body='common_filter')

Direct children: path LIKE 'parent.%' with exactly one extra dot-level. Child values merge into parent data as {d[child_name]}.

Placeholders

Syntax	Behavior
`{d[key]}`	Replace with value (`NULL` → `null`)
`{d[key]!r}`	Replace with `quote_literal(value)` (`NULL` → `''`)
`{d[key]!j}`	jsonb literal: `'<value>'::jsonb` (strings auto-quoted, objects/arrays pass through)
`{d[key]!i}`	`quote_identifier(value)` — safe SQL identifier (`order` → `"order"`, `name` → `name`)

Virtual key _self — full input JSON object, injected automatically when {d[_self]} or {d[_self]!j} appears in template body. Useful for passing the entire input payload as jsonb.

Values containing {d[...]} are substituted first (nested expansion).

Functions

Core (internal)

Function	Description
`fsql._c_render(template, data)`	C placeholder substitution
`fsql._c_execute(sql, params, use_cache)`	SPI execution with plan caching
`fsql._exec_templ(templ, data, cached)`	Parameterized execution via `fsql.params`
`fsql._process(path, data, debug, depth)`	Recursive template engine

Public API

Function	Description
`fsql.run(path, data, debug)`	Execute template tree → jsonb
`fsql.render(path, data)`	Dry-run: render SQL without executing
`fsql.tree(path)`	Show template hierarchy
`fsql.explain(path, data)`	Step-by-step expansion trace
`fsql.validate()`	Check all templates for errors
`fsql.depends_on(path)`	List recursive dependencies
`fsql.clear_cache()`	Free all cached SPI plans

Examples

Basic exec

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"}');
-- {"total": 42}

Template with children

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

Conditional branching

INSERT INTO fsql.templates (path, cmd, body) VALUES
    ('greeting',         'if', 'SELECT {d[lang]!r}'),
    ('greeting.en',      NULL, 'Hello'),
    ('greeting.ru',      NULL, 'Привет'),
    ('greeting.default', NULL, 'Hi');

SELECT fsql.run('greeting', '{"lang":"ru"}');
-- {"key": "Привет"}

Template reference

INSERT INTO fsql.templates (path, cmd, body) VALUES
    ('my_report', 'ref', 'report');

SELECT fsql.run('my_report', '{"city":"SPb"}');

Inspect & validate

SELECT * FROM fsql.tree('report');
SELECT * FROM fsql.explain('report', '{"city":"Moscow"}');
SELECT * FROM fsql.validate();
SELECT * FROM fsql.depends_on('report');

Debug trace

SELECT fsql.run('report', '{"city":"Moscow"}', true);
-- NOTICE:  [fsql] report (cmd=exec)
-- NOTICE:    exec → SELECT ...
-- NOTICE:    result → {"data": [...]}

REST CRUD with `_self!j`

Dynamic UPDATE that handles any subset of columns — the key use-case for !j and _self:

-- PUT: update only supplied columns via jsonb_populate_record
INSERT INTO fsql.templates (path, cmd, body) VALUES
('rest.put', 'exec',
 'UPDATE {d[tbl]} SET ({d[columns]}) = (
    SELECT {d[columns]} FROM (
      SELECT (jsonb_populate_record(null::{d[tbl]}, {d[_self]!j} - ''id'')).*
    ) sub
  ) WHERE id = {d[id]}
  RETURNING jsonb_build_object(''id'', id)');

-- Child: dynamically intersect input keys with table columns (cmd=exec)
INSERT INTO fsql.templates (path, cmd, body) VALUES
('rest.put.columns', 'exec',
 'SELECT jsonb_build_object(''columns'',
    string_agg(c.column_name, '','' ORDER BY c.ordinal_position))
  FROM information_schema.columns c
  WHERE c.table_schema || ''.'' || c.table_name = {d[tbl]!r}
    AND c.column_name != ''id''
    AND {d[_self]!j} ? c.column_name');

-- Usage:
SELECT fsql.run('rest.put',
    '{"tbl":"myschema.orders","id":42,"price":19.99,"qty":5}');
-- {d[_self]!j} → '{"tbl":"myschema.orders","id":42,"price":19.99,"qty":5}'::jsonb
-- rest.put.columns resolves → "price,qty" (only supplied keys)
-- jsonb_populate_record fills a typed record from the input JSON

-- Preview the generated SQL:
SELECT fsql.render('rest.put',
    '{"tbl":"myschema.orders","id":42,"price":19.99,"qty":5}');

How it works: - {d[_self]!j} injects the full input JSON as a '...'::jsonb literal - Child rest.put.columns (cmd=exec) queries information_schema to find which input keys match actual table columns — render() executes children via _process - jsonb_populate_record casts the JSON into a proper row type, so PostgreSQL handles type conversion - Result: one template set serves UPDATE for any table with any subset of columns

Configuration

GUC	Type	Default	Description
`fsql.max_depth`	int	64	Max recursion depth
`fsql.cache_plans`	bool	true	Global SPI plan cache switch

SET fsql.max_depth = 128;
SET fsql.cache_plans = false;

GUC variables load on first C function call (_PG_init). For session-start availability, add to postgresql.conf:

shared_preload_libraries = 'pg_fsql'

Plan Caching

Problem

Every fsql.run() call on an exec template generates SQL, prepares a plan, executes it, and discards the plan. For hot-path templates called repeatedly with the same structure, the SPI_prepare overhead is wasted work.

Solution

Two-level opt-in caching:

fsql.cache_plans = true     (GUC — global switch, default on)
    AND
templates.cached = true     (per-template, default off)
    →
plan is cached in a backend-local hash table

When both conditions are true, _c_execute stores the prepared plan via SPI_keepplan(). Subsequent calls with the same SQL text reuse the cached plan — only SPI_execute_plan runs, skipping parse/plan.

What gets cached

_exec_templ renders a template into parameterized SQL:

Template:   SELECT ... FROM {d[src]} WHERE id = {d[id]}
                            ↑ inlined              ↑ becomes $1[N]::bigint

Result SQL: SELECT ... FROM orders WHERE id = $1[2]::bigint
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
                     cache key = hash of this SQL text

Inlined values (not in fsql.params) become part of the SQL text
Parameterized values (in fsql.params) become $1[N]::type — do not affect the plan
Cache key = hash_any_extended(sql_text) — uint64, collision-free for practical sizes

Scenario	Cache
Same template, same inline data, different param values	HIT
Same template, different inline data	MISS — different SQL
Different templates	MISS — different SQL

Usage

-- Enable for hot templates
UPDATE fsql.templates SET cached = true WHERE path = 'my_template';

-- Calls are transparent — caching is automatic
SELECT fsql.run('my_template', '{"id":"1"}');  -- prepare + cache
SELECT fsql.run('my_template', '{"id":"2"}');  -- cache hit

-- After DDL changes, flush stale plans
SELECT fsql.clear_cache();

-- Disable globally for debugging
SET fsql.cache_plans = false;

When to enable

Use case	cached	Why
Frequently called, stable inline data	true	Saves `SPI_prepare` on every call
User-supplied `{d[key]!r}` values	false	Every call = different SQL, no reuse
Called once per session	false	No benefit
Hot path in loop / batch	true	Maximum effect

When to call clear_cache()

After DDL: ALTER TABLE, DROP INDEX, CREATE VIEW, etc.
After updating fsql.params (type changes → different SQL)
After modifying body of templates with cached = true
On suspected stale plan (unexpectedly slow query)

Migration from data_algorithms

-- Copy templates
INSERT INTO fsql.templates (path, cmd, body, defaults)
SELECT path, cmd, stempl, mask_before
FROM   data_algorithms.c_sql_templ
WHERE  path IS NOT NULL
ON CONFLICT (path) DO UPDATE SET
    cmd = EXCLUDED.cmd, body = EXCLUDED.body, defaults = EXCLUDED.defaults;

-- Copy params
INSERT INTO fsql.params (key_param, type_param)
SELECT key_param, type_param FROM data_algorithms.c_params
ON CONFLICT (key_param) DO UPDATE SET type_param = EXCLUDED.type_param;

-- Optional: compatibility wrappers
CREATE FUNCTION data_algorithms.f_template_load_sql(_t text, _d jsonb)
RETURNS text LANGUAGE sql STABLE AS $$ SELECT fsql._c_render(_t, _d) $$;

CREATE FUNCTION data_algorithms.f_sql(_p text, _d jsonb DEFAULT '{}', _dbg boolean DEFAULT false)
RETURNS SETOF jsonb LANGUAGE sql VOLATILE AS $$ SELECT fsql._process(_p, _d, _dbg, 0) $$;

Testing

# Run full test suite (inside PostgreSQL container or host)
psql -d test_db -f test/sql/00-seed.sql
psql -d test_db -f test/sql/01-render.sql
psql -d test_db -f test/sql/02-process.sql
psql -d test_db -f test/sql/03-render-tree-validate.sql
psql -d test_db -f test/sql/05-gen-select.sql
psql -d test_db -f test/sql/06-cache.sql
psql -d test_db -f test/sql/07-rest-crud.sql

# Or use the runner
cd test && bash run_tests.sh

File Structure

pg_fsql/
├── src/
│   ├── pg_fsql.c            _PG_init, GUC definitions
│   ├── render.c             C renderer: {d[key]} substitution
│   └── execute.c            SPI plan cache: _c_execute, clear_cache
├── sql/parts/
│   ├── 00-types.sql         composite types
│   ├── 01-tables.sql        templates, params tables
│   ├── 02-indexes.sql       indexes
│   ├── 03-cfuncs.sql        C function declarations
│   ├── 04-exec-templ.sql    parameterized execution
│   ├── 05-process.sql       recursive engine
│   ├── 06-run.sql           public API: run()
│   ├── 07-render.sql        dry-run rendering
│   ├── 08-tree.sql          tree visualization
│   ├── 09-explain.sql       expansion trace
│   ├── 10-validate.sql      template validation
│   ├── 11-depends-on.sql    dependency analysis
│   └── 99-compat.sql        migration notes
├── test/
│   ├── run_tests.sh         test runner
│   └── sql/
│       ├── 00-seed.sql      test fixtures
│       ├── 01-render.sql    _c_render tests
│       ├── 02-process.sql   _process + run tests
│       ├── 03-render-tree-validate.sql
│       ├── 04-migration.sql data_algorithms migration
│       ├── 05-gen-select.sql  generic SELECT builder
│       ├── 06-cache.sql     SPI plan cache tests
│       └── 07-rest-crud.sql REST CRUD with _self!j
├── Makefile
├── META.json              PGXN distribution metadata
├── pg_fsql.control
├── CHANGELOG.md
├── LICENSE
└── README.md

License

Released under the PostgreSQL License.

PGXN

PostgreSQL Extension Network

pg_fsql 1.1.0

Extensions

Documentation

README

Contents

pg_fsql

Features

Quick Start

Requirements

Installation

Linux

Docker

Verify

Schema

Command Types

Template Hierarchy

Placeholders

Functions

Core (internal)

Public API

Examples

Basic exec

Template with children

Conditional branching

Template reference

Inspect & validate

Debug trace

REST CRUD with _self!j

Configuration

Plan Caching

Problem

Solution

What gets cached

Usage

When to enable

When to call clear_cache()

Migration from data_algorithms

Testing

File Structure

License

REST CRUD with `_self!j`