pglite_fusion

Overview

Version

Build

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

pig build pkg pglite_fusion         # build RPM / DEB packages

Install

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

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

dnf install -y pglite_fusion_18       # PG 18
dnf install -y pglite_fusion_17       # PG 17
dnf install -y pglite_fusion_16       # PG 16
dnf install -y pglite_fusion_15       # PG 15
dnf install -y pglite_fusion_14       # PG 14

apt install -y postgresql-18-pglite-fusion   # PG 18
apt install -y postgresql-17-pglite-fusion   # PG 17
apt install -y postgresql-16-pglite-fusion   # PG 16
apt install -y postgresql-15-pglite-fusion   # PG 15
apt install -y postgresql-14-pglite-fusion   # PG 14

Preload:

shared_preload_libraries = 'pglite_fusion';

Create Extension:

CREATE EXTENSION pglite_fusion;

Usage

https://github.com/frectonz/pglite-fusion/blob/main/README.md

Here’s some demo usage.

-- Load PG extension
CREATE EXTENSION pglite_fusion;

-- Create a table with an SQLite column
CREATE TABLE people (
                        name     TEXT NOT NULL,
                        database SQLITE DEFAULT init_sqlite('CREATE TABLE todos (task TEXT)')
);

-- Insert a row into the people table
INSERT INTO people VALUES ('frectonz');

-- Create a todo for "frectonz"
UPDATE people
SET database = execute_sqlite(
        database,
        'INSERT INTO todos VALUES (''solve multitenancy'')'
               )
WHERE name = 'frectonz';

-- Create a todo for "frectonz"
UPDATE people
SET database = execute_sqlite(
        database,
        'INSERT INTO todos VALUES (''buy milk'')'
               )
WHERE name = 'frectonz';

-- Fetch frectonz's info
SELECT
    name,
    (
        SELECT json_agg(get_sqlite_text(sqlite_row, 0))
        FROM query_sqlite(
                database,
                'SELECT * FROM todos'
             )
    ) AS todos
FROM
    people
WHERE
    name = 'frectonz';

API Doc

empty_sqlite

Creates an empty SQLite database and returns it as a binary object. This can be used to initialize an empty SQLite database in a PostgreSQL column.

Example Usage:

SELECT empty_sqlite();

query_sqlite

Executes a SQL query on a SQLite database stored as a binary object and returns the result as a table of JSON-encoded rows. This function is useful for querying SQLite databases stored in PostgreSQL columns.

Parameters:

• sqlite: The SQLite database to query, stored as a binary object.
• query: The SQL query string to execute on the SQLite database.

Example Usage:

SELECT * FROM query_sqlite(
        database,
        'SELECT * FROM todos'
              );

execute_sqlite

Executes a SQL statement (such as INSERT, UPDATE, or DELETE) on a SQLite database stored as a binary object. The updated SQLite database is returned as a binary object, allowing further operations on it.

Parameters:

• sqlite: The SQLite database to execute the SQL query on, stored as a binary object.
• query: The SQL statement to execute on the SQLite database.

Example Usage:

UPDATE people
SET database = execute_sqlite(
        database,
        'INSERT INTO todos VALUES (''solve multitenancy'')'
               )
WHERE name = 'frectonz';

init_sqlite

Creates an SQLite database with an initialization query already applied on it. This can be used to initialize a SQLite database with the expected tables already created.

Parameters:

• query: The SQL statement to execute on the SQLite database.

Example Usage:

CREATE TABLE people (
                        name     TEXT NOT NULL,
                        database SQLITE DEFAULT init_sqlite('CREATE TABLE todos (task TEXT)')
);

get_sqlite_text

Extracts a text value from a specific column in a row returned by query_sqlite. Use this function to retrieve text values from query results.

Parameters:

• sqlite_row: A row from the results of query_sqlite.
• index: The index of the column to extract from the row.

Example Usage:

SELECT get_sqlite_text(sqlite_row, 0)
FROM query_sqlite(database, 'SELECT * FROM todos');

get_sqlite_integer

Extracts an integer value from a specific column in a row returned by query_sqlite. Use this function to retrieve integer values from query results.

Parameters:

• sqlite_row: A row from the results of query_sqlite.
• index: The index of the column to extract from the row.

Example Usage:

SELECT get_sqlite_integer(sqlite_row, 1)
FROM query_sqlite(database, 'SELECT * FROM todos');

get_sqlite_real

Extracts a real (floating-point) value from a specific column in a row returned by query_sqlite. Use this function to retrieve real number values from query results.

Parameters:

• sqlite_row: A row from the results of query_sqlite.
• index: The index of the column to extract from the row.

Example Usage:

SELECT get_sqlite_real(sqlite_row, 2)
FROM query_sqlite(database, 'SELECT * FROM todos');