pglite_fusion

在PG表中嵌入SQLite数据库作为数据类型

扩展总览

PIGSTY 第三方扩展: pglite_fusion : 在PG表中嵌入SQLite数据库作为数据类型

基本信息

  • 扩展编号: 3540
  • 扩展名称: pglite_fusion
  • 标准包名: pglite_fusion
  • 扩展类目: TYPE
  • 开源协议: MIT
  • 官方网站: https://github.com/frectonz/pglite-fusion
  • 编程语言: Rust
  • 其他标签: pgrx
  • 备注信息: 无

元数据

  • 默认版本: 0.0.3
  • PG大版本: 17,16,15,14,13
  • 动态加载: 需要显式加载
  • 需要DDL: 需要执行 CREATE EXTENSION DDL
  • 可重定位: 可以重定位安装至其他模式下
  • 信任程度: 未受信任,创建扩展需要超级用户权限
  • 所需模式: 无
  • 所需扩展: 无

软件包

  • RPM仓库:PIGSTY
  • RPM包名:pglite_fusion_$v
  • RPM版本:0.0.3
  • RPM依赖:无
  • DEB仓库:PIGSTY
  • DEB包名:postgresql-$v-pglite-fusion
  • DEB版本:0.0.3
  • DEB依赖:无

最新版本

系统 架构 PG17 PG16 PG15 PG14 PG13
el8 x86_64 pglite_fusion_17
PIGSTY 0.0.3
pglite_fusion_16
PIGSTY 0.0.3
pglite_fusion_15
PIGSTY 0.0.3
pglite_fusion_14
PIGSTY 0.0.3
pglite_fusion_13
PIGSTY 0.0.3
el8 aarch64 pglite_fusion_17
PIGSTY 0.0.3
pglite_fusion_16
PIGSTY 0.0.3
pglite_fusion_15
PIGSTY 0.0.3
pglite_fusion_14
PIGSTY 0.0.3
pglite_fusion_13
PIGSTY 0.0.3
el9 x86_64 pglite_fusion_17
PIGSTY 0.0.3
pglite_fusion_16
PIGSTY 0.0.3
pglite_fusion_15
PIGSTY 0.0.3
pglite_fusion_14
PIGSTY 0.0.3
pglite_fusion_13
PIGSTY 0.0.3
el9 aarch64 pglite_fusion_17
PIGSTY 0.0.3
pglite_fusion_16
PIGSTY 0.0.3
pglite_fusion_15
PIGSTY 0.0.3
pglite_fusion_14
PIGSTY 0.0.3
pglite_fusion_13
PIGSTY 0.0.3
d12 x86_64 postgresql-17-pglite-fusion
PIGSTY 0.0.3
postgresql-16-pglite-fusion
PIGSTY 0.0.3
postgresql-15-pglite-fusion
PIGSTY 0.0.3
postgresql-14-pglite-fusion
PIGSTY 0.0.3
postgresql-13-pglite-fusion
PIGSTY 0.0.3
d12 aarch64 postgresql-17-pglite-fusion
PIGSTY 0.0.3
postgresql-16-pglite-fusion
PIGSTY 0.0.3
postgresql-15-pglite-fusion
PIGSTY 0.0.3
postgresql-14-pglite-fusion
PIGSTY 0.0.3
postgresql-13-pglite-fusion
PIGSTY 0.0.3
u22 x86_64 postgresql-17-pglite-fusion
PIGSTY 0.0.3
postgresql-16-pglite-fusion
PIGSTY 0.0.3
postgresql-15-pglite-fusion
PIGSTY 0.0.3
postgresql-14-pglite-fusion
PIGSTY 0.0.3
postgresql-13-pglite-fusion
PIGSTY 0.0.3
u22 aarch64 postgresql-17-pglite-fusion
PIGSTY 0.0.3
postgresql-16-pglite-fusion
PIGSTY 0.0.3
postgresql-15-pglite-fusion
PIGSTY 0.0.3
postgresql-14-pglite-fusion
PIGSTY 0.0.3
postgresql-13-pglite-fusion
PIGSTY 0.0.3
u24 x86_64 postgresql-17-pglite-fusion
PIGSTY 0.0.3
postgresql-16-pglite-fusion
PIGSTY 0.0.3
postgresql-15-pglite-fusion
PIGSTY 0.0.3
postgresql-14-pglite-fusion
PIGSTY 0.0.3
postgresql-13-pglite-fusion
PIGSTY 0.0.3
u24 aarch64 postgresql-17-pglite-fusion
PIGSTY 0.0.3
postgresql-16-pglite-fusion
PIGSTY 0.0.3
postgresql-15-pglite-fusion
PIGSTY 0.0.3
postgresql-14-pglite-fusion
PIGSTY 0.0.3
postgresql-13-pglite-fusion
PIGSTY 0.0.3

扩展安装

使用 pig 命令行工具安装 pglite_fusion 扩展:

pig ext install pglite_fusion

使用 Pigsty剧本 安装 pglite_fusion 扩展:

./pgsql.yml -t pg_extension -e '{"pg_extensions": ["pglite_fusion"]}' # -l <集群名>

YUM仓库 手工安装 pglite_fusion RPM 包:

dnf install pglite_fusion_17;
dnf install pglite_fusion_16;
dnf install pglite_fusion_15;
dnf install pglite_fusion_14;
dnf install pglite_fusion_13;

APT仓库 手工安装 pglite_fusion DEB 包:

apt install postgresql-17-pglite-fusion;
apt install postgresql-16-pglite-fusion;
apt install postgresql-15-pglite-fusion;
apt install postgresql-14-pglite-fusion;
apt install postgresql-13-pglite-fusion;

扩展 pglite_fusion 需要通过 shared_preload_libraries 进行 动态加载

shared_preload_libraries = 'pglite_fusion'; # 修改 PG 集群配置

使用以下 SQL 命令在已经安装此扩展插件的 PG 集群上 启用 pglite_fusion 扩展:

CREATE EXTENSION pglite_fusion;

使用方法

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




最后修改 2025-03-07: routine update (2106723)