I wanted use FTS5 from sqlite to replace postgresql's built-in FTS, which does not have BM25 ranking support.
-- Create a table. And an external content fts5 table to index it.
CREATE TABLE tbl(a INTEGER PRIMARY KEY, b, c);
CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='tbl', content_rowid='a', tokenize='myowntokenizer');
-- Triggers to keep the FTS index up to date.
CREATE TRIGGER tbl_ai AFTER INSERT ON tbl BEGIN
INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
CREATE TRIGGER tbl_ad AFTER DELETE ON tbl BEGIN
INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
END;
CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN
INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
END;
to sync data from postgresql table to a sqlite foreign table, but I found that I cannot load the customized tokenizer (myowntokenizer.so/myowntokenizer.dll) on the sqlite side.
Problem
Is it possible to support sqlite plugins (.so/.dll)?
Context
I wanted use FTS5 from sqlite to replace postgresql's built-in FTS, which does not have BM25 ranking support.
The idea was to use something like
to sync data from postgresql table to a sqlite foreign table, but I found that I cannot load the customized tokenizer (myowntokenizer.so/myowntokenizer.dll) on the sqlite side.