how to use the zombodb plugin to support native access - ApsaraDB RDS

ZomboDB is a PostgreSQL extension plugin that provides powerful text indexing and analytics capabilities for PostgreSQL databases through native access methods.

Prerequisites

The RDS instance runs PostgreSQL 11.
The RDS instance runs a minor engine version of 20230830 or later.
Important
This plugin was supported before the 20230830 minor engine version. However, to standardize plugin management and enhance security protection for RDS PostgreSQL plugins, RDS plans to optimize plugins with security risks in kernel version iterations. Some plugins cannot be created in lower minor engine versions. For more information, see Restrictions on creating plugins.
- If your instance runs a minor engine version earlier than 20230830 and you have already used this plugin, your use is not affected.
- If you are creating this plugin for the first time or recreating it, upgrade the minor engine version to the latest.

Background information

ZomboDB provides a full set of query languages to query relational data. You can also create ZomboDB indexes. In this case, ZomboDB takes over remote Elasticsearch indexes and ensures transaction correctness of query results from text search.

ZomboDB lets you use Elasticsearch without the need to handle synchronization or communication issues.

Create and delete the extension

Create the extension.
```
CREATE EXTENSION zombodb;
```
Delete the extension.
```
DROP EXTENSION zombodb;
```

Examples

Create a table.

CREATE TABLE products (
    id SERIAL8 NOT NULL PRIMARY KEY,
    name text NOT NULL,
    keywords varchar(64)[],
    short_summary text,
    long_description zdb.fulltext,
    price bigint,
    inventory_count integer,
    discontinued boolean default false,
    availability_date date
);

Create a ZomboDB index for the table.
```
CREATE INDEX idxproducts
          ON products
       USING zombodb ((products.*))
        WITH (url='localhost:9200/');
```
Note
The WITH clause is followed by an Elasticsearch endpoint, which points to a running Elasticsearch cluster.

Query data using the ZomboDB index.

SELECT *
  FROM products
 WHERE products ==> '(keywords:(sports OR box) OR long_description:"wooden away"~5) AND price:[1000 TO 20000]';

Note

For more information about the query syntax, see ZomboDB Documentation.