fbsql

Overview

Requires PL/R 8.4.0 or newer; PIGSTY packages target PostgreSQL 16 through 18.

Version

Build

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

pig build pkg fbsql         # build RPM / DEB packages

Install

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

pig ext install -y fbsql -v 18  # PG 18
pig ext install -y fbsql -v 17  # PG 17
pig ext install -y fbsql -v 16  # PG 16

dnf install -y fbsql_18       # PG 18
dnf install -y fbsql_17       # PG 17
dnf install -y fbsql_16       # PG 16

apt install -y postgresql-18-fbsql   # PG 18
apt install -y postgresql-17-fbsql   # PG 17
apt install -y postgresql-16-fbsql   # PG 16

Create Extension:

CREATE EXTENSION fbsql CASCADE;  -- requires: plr

Usage

Sources:

• FbSQL 0.1.0 README
• FbSQL 0.1.0 changes
• Extension control file
• PGXN release

fbsql is a proof-of-concept statistical-modeling DSL that keeps fitting and prediction relational: SQL queries go in and rows come back, while models are described with R formula syntax. Release 0.1.0 implements generalized linear models through PL/R for fitting and pure PL/pgSQL for prediction.

Prerequisites

FbSQL was developed and tested with PostgreSQL 16 and requires PL/R 8.4.0 or newer plus R. plr is an untrusted language, so a superuser must install the dependency and extension.

CREATE EXTENSION fbsql CASCADE;
SELECT fbsql.version();

Grant regular users only the function access and source-data privileges they require.

Core Workflow

Fit a binomial churn model and retain the returned relation:

CREATE TEMP TABLE churn_model AS
SELECT *
FROM fbsql.fit_glm(
  relation => $$
    SELECT churn_flag, age, gender
    FROM customer
    WHERE created_at >= DATE '2025-01-01'
      AND created_at <  DATE '2026-01-01'
  $$,
  formula => 'churn_flag ~ age + gender',
  family => 'binomial'
);

Prediction accepts a query for new rows and a query returning the saved model. Because it returns SETOF record, supply the output columns at the call site:

SELECT customer_id, churn_flag_predicted
FROM fbsql.predict_glm(
  relation => $$SELECT customer_id, age, gender FROM customer_2026$$,
  model    => $$SELECT * FROM churn_model$$
) AS p(
  customer_id bigint,
  age integer,
  gender text,
  churn_flag_predicted double precision
);

Important Objects

• fbsql.fit_glm(relation, formula, family) returns one row per model term, repeated fit statistics, and metadata jsonb containing the information needed for prediction.
• fbsql.predict_glm(relation, model, on_new_levels) appends <response>_predicted to the input rows. on_new_levels is error by default or na to produce a null prediction for unseen factor levels.
• fbsql.version() reports the extension version.

Supported Surface and Caveats

Version 0.1.0 supports Gaussian models with the identity link and binomial models with the logit link, using numeric and factor predictors. Fitting applies complete-case analysis and reports used and dropped row counts; prediction returns NULL when a predictor is null. Prediction uses stored coefficients and metadata and does not invoke R at runtime.

Interactions, custom contrasts, offsets, weights, prediction intervals, additional families and links, and distributed fitting are not supported. The relation and model parameters contain SQL text: construct them from trusted SQL, not unsanitized user input, and review the executing role’s privileges.