pglite_fusion
Module:
Categories:
扩展总览
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依赖:无
最新版本
扩展安装
使用 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 ofquery_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 ofquery_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 ofquery_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');