pglite_fusion

pig build get pglite_fusion; # get pglite_fusion source code
pig build dep pglite_fusion; # install build dependencies
pig build pkg pglite_fusion; # build extension rpm or deb
pig build ext pglite_fusion; # build extension rpms

pig repo add pgsql -u   # add PGDG + Pigsty repo and update cache (leave existing repos)

pig ext install pglite_fusion; # install by extension name, for the current active PG version
pig ext install pglite_fusion; # install via package alias, for the active PG version
pig ext install pglite_fusion -v 18;   # install for PG 18
pig ext install pglite_fusion -v 17;   # install for PG 17
pig ext install pglite_fusion -v 16;   # install for PG 16
pig ext install pglite_fusion -v 15;   # install for PG 15
pig ext install pglite_fusion -v 14;   # install for PG 14
pig ext install pglite_fusion -v 13;   # install for PG 13

CREATE EXTENSION pglite_fusion;

-- 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';

SELECT empty_sqlite();

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

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

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

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

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

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