ApsaraDB RDS:rds_ai 拡張機能を使用した RAG の実装

前提条件

開始する前に、以下の条件を満たしていることを確認してください。

• PostgreSQL 16 を実行し、マイナーエンジンバージョンが 20241230 以降の RDS インスタンスが必要です。アップグレードについては、「マイナーエンジンバージョンの更新」をご参照ください。

• RDS インスタンス上の特権アカウントが必要です。「アカウントの作成」をご参照ください。

• Alibaba Cloud Model Studio がアクティブ化されており、API キーが準備できている必要があります。「APIキーの取得」をご参照ください。

• RDS インスタンスのネットワークが、外部モデルにアクセスできるよう構成されている必要があります。「ネットワーク構成」をご参照ください。

仕組み

このパイプラインは、RDS インスタンス内で 5 つのステージを通じて完全に実行されます。

1. ドキュメントのチャンク化 — SQL を使用してドキュメントを固定サイズのチャンクに分割します (PostgreSQL インスタンス内でローカルに実行)。

2. テキスト埋め込み — rds_ai を使用してチャンクを埋め込みベクトルと tsvector に変換します (リモートの埋め込みモデルを呼び出し)。

3. マルチモーダル検索 — ベクトル類似度とキーワードマッチングの両方で候補を検索します (ローカルで実行)。

4. 結果の再ランキング — 候補を再スコアリングして、最も関連性の高い結果を表示します (ローカルアルゴリズムまたはリモートの再ランキングモデル)。

5. LLM の呼び出し — 上位の結果を LLM に送信し、回答を返します (リモートのチャットモデルを呼び出し)。

ステップ1：rds_ai のインストールと設定

拡張機能のインストール

1. ApsaraDB RDS コンソールで インスタンス ページを開きます。上部メニューで、インスタンスが存在するリージョンを選択し、インスタンス ID をクリックします。

2. 左側メニューで、[プラグイン] をクリックします。

3. [拡張機能マーケットプレイス] タブで、[rds_ai] セクションの [インストール] をクリックします。

4. 表示されるダイアログで、データベースとアカウントを選択し、[インストール] をクリックします。

5. インスタンスの状態が [インスタンスのメンテナンス中] から [実行中] に変わるまでお待ちください。

拡張機能の設定

RDS for PostgreSQL インスタンスに接続し、public スキーマで以下のステートメントを実行します。

1. すべてのデフォルトモデルの API キーを設定します。

   -- rds_ai.model_list に記載されているすべてのモデルの API キーを設定します。
   SELECT rds_ai.update_model(model_name, 'token', 'sk-****') FROM rds_ai.model_list;

2. リクエストタイムアウトを設定します。rds_ai 拡張機能は pgsql-http 拡張機能を使用して HTTP 経由でモデルを呼び出すため、実行時間が長くなる呼び出しには明示的なタイムアウトが必要です。

   重要

   これらの設定は現在のセッションにのみ適用されます。新しいセッションを開くたびに再設定してください。

   -- リクエストタイムアウトをミリ秒単位で設定します。
   SET http.timeout_msec TO 200000;
   
   SELECT http.http_set_curlopt('CURLOPT_TIMEOUT', '200000');
   
   -- 接続タイムアウトをミリ秒単位で設定します。
   SELECT http.http_set_curlopt('CURLOPT_CONNECTTIMEOUT_MS', '200000');

ステップ2：RAG 用のデータの準備

このステップでは、ドキュメントが挿入されたときに自動的に埋め込みを行う、テーブル、チャンク化ロジック、およびトリガーを設定します。

1. 元のドキュメント用の doc テーブルと処理済みチャンク用の chunk テーブルを作成し、インデックスを追加します:

   CREATE TABLE doc (
       id SERIAL PRIMARY KEY,
       title VARCHAR(255),
       content TEXT
   );
   
   CREATE TABLE chunk (
       id SERIAL PRIMARY KEY,
       doc_id INTEGER NOT NULL,
       text TEXT,
       embedding VECTOR(1024),
       ts_vector_extra tsvector
   );
   
   -- チャンクをソースドキュメントに結合するためのインデックス。
   CREATE INDEX idx_doc_id ON chunk (doc_id);
   
   -- 近似最近傍 (ANN) ベクトル検索用の HNSW インデックスを作成します。
   -- 以下のオプションのいずれか 1 つを選択して実行してください。
   
   -- オプション 1: デフォルト設定 (ほとんどのワークロードに推奨)
   -- このインデックスは、一般的なユースケースにおいて、速度と精度のバランスが取れています。
   CREATE INDEX ON chunk USING hnsw (embedding vector_cosine_ops);
   
   -- オプション 2: カスタムパラメータ
   -- 大規模なデータセットで、検索速度と再現率のトレードオフをより細かく制御する場合に使用します。
   -- CREATE INDEX ON chunk USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
   
   -- キーワードによる全文検索用の GIN インデックス。
   CREATE INDEX chunk_text_gin ON chunk USING gin (ts_vector_extra);

2. 長いテキストを設定可能なオーバーラップを付けて固定サイズのチャンクに分割する split_text 関数を作成します。 複雑なドキュメントの場合は、代わりに専用のテキストチャンキングフレームワークを使用してください。

   CREATE OR REPLACE FUNCTION split_text(
       input_text TEXT,
       chunk_size INT,
       chunk_overlap INT
   ) RETURNS SETOF TEXT AS $$
   DECLARE
       current_idx INT;
       start_idx INT;
       chunk TEXT;
   BEGIN
       -- オーバーラップがチャンクサイズより小さいことを検証します。
       IF chunk_overlap >= chunk_size THEN
           RAISE EXCEPTION 'chunk_overlap must be less than chunk_size';
       END IF;
   
       current_idx := 1;
   
       LOOP
           -- 開始位置を戻すことでオーバーラップを適用します。
           start_idx := current_idx - chunk_overlap;
   
           IF start_idx < 1 THEN
               start_idx := 1;
           END IF;
   
           chunk := substr(input_text, start_idx, chunk_size);
   
           IF chunk IS NULL OR length(chunk) = 0 THEN
               EXIT;
           END IF;
   
           RETURN NEXT chunk;
           current_idx := current_idx + chunk_size - chunk_overlap;
       END LOOP;
   END;
   $$ LANGUAGE plpgsql;

3. ドキュメントが挿入されるたびに、チャンク化、埋め込み、および tsvector の生成を自動化するトリガーを作成します。

   説明

   ドキュメントが複雑で、固定サイズの関数を使用して分割するのが難しい場合は、このステップをスキップして、直接 ステップ3 に進んでください。

   -- 挿入されたドキュメントをチャンクに分割し、chunk テーブルに書き込みます。
   CREATE OR REPLACE FUNCTION insert_into_chunk()
   RETURNS TRIGGER AS $$
   BEGIN
       INSERT INTO chunk (doc_id, text)
       SELECT NEW.id, result
       FROM split_text(NEW.content, 300, 50) AS result;
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER after_insert_doc
   AFTER INSERT ON doc
   FOR EACH ROW
   EXECUTE FUNCTION insert_into_chunk();
   
   -- ソースドキュメントが削除されたときにチャンクを削除します。
   CREATE OR REPLACE FUNCTION delete_from_chunk()
   RETURNS TRIGGER AS $$
   BEGIN
       DELETE FROM chunk WHERE doc_id = OLD.id;
       RETURN OLD;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER after_delete_doc
   AFTER DELETE ON doc
   FOR EACH ROW
   EXECUTE FUNCTION delete_from_chunk();
   
   -- rds_ai.embed を呼び出して、新規または更新された各チャンクの埋め込みベクトルを生成します。
   CREATE OR REPLACE FUNCTION update_chunk_embedding()
   RETURNS TRIGGER AS $$
   BEGIN
       NEW.embedding := rds_ai.embed(NEW.text)::vector;
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER set_chunk_embedding
   BEFORE INSERT OR UPDATE ON chunk
   FOR EACH ROW
   EXECUTE FUNCTION update_chunk_embedding();
   
   -- キーワード検索用に tsvector を構築します。
   CREATE TRIGGER embedding_tsvector_update
   BEFORE UPDATE OR INSERT ON chunk
   FOR EACH ROW
   EXECUTE PROCEDURE tsvector_update_trigger(
       'ts_vector_extra',
       'pg_catalog.english',
       'text'
   );

4. doc テーブルにドキュメントを挿入します。前のステップで作成されたトリガーが、各ドキュメントを自動的に分割、埋め込み、インデックス化します。次のサンプルデータは参考用です。

   INSERT INTO doc (title, content) VALUES
     ('A Brief History of PostgreSQL', 'The object-relational database management system now known as PostgreSQL is derived from the POSTGRES package written at the University of California, Berkeley. After more than two decades of development, PostgreSQL is the most advanced open-source database available in the world.\n\n2.1. The POSTGRES Project at Berkeley\nThe POSTGRES project, led by Professor Michael Stonebraker, was sponsored by the Defense Advanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. The implementation of POSTGRES began in 1986. The original concepts of the system are detailed in [ston86]. The initial data model definition is found in [rowe87]. The design of the rule system at that time is described in [ston87a]. The theoretical foundation and architecture of the storage manager are described in detail in [ston87b].\n\nSince then, POSTGRES has undergone several major version updates. The first demonstration system was available in 1987 and was exhibited at the ACM-SIGMOD conference in 1988. Version 1 was released in June 1989 (see [ston90a]) for some external users. In response to criticism of the first rule system ([ston89]), the rule system was redesigned ([ston90b]), and version 2, using the new rule system, was released in June 1990. Version 3 appeared in 1991, adding support for multiple storage managers and improving the query executor and rewriting the rule system. Subsequent versions, until the release of Postgres95 (see below), focused mainly on portability and reliability.\n\nPOSTGRES has been used to implement many different research and production applications. These applications include a financial data analysis system, a jet engine performance monitoring package, an asteroid tracking database, a medical information database, and some geographic information systems. POSTGRES has also been used by many universities for teaching purposes. Finally, Illustra Information Technologies (later merged into Informix, which is now owned by IBM) commercialized the code. In late 1992, POSTGRES became the primary data manager for the Sequoia 2000 scientific computing project.\n\nIn 1993, the number of external users nearly doubled. As the number of users increased, the time spent on source code maintenance increased and took up too much time that should have been used for database research. To reduce the support burden, the POSTGRES project at Berkeley officially ended at version 4.2.\n\n2.2. Postgres95\nIn 1994, Andrew Yu and Jolly Chen added an SQL language interpreter to POSTGRES. They subsequently released the source code under the new name Postgres95 on the Internet for everyone to use, becoming the open-source successor to the original POSTGRES Berkeley code.\n\nThe source code of Postgres95 is entirely ANSI C, and the code size was reduced by 25%. Many internal modifications improved performance and maintainability. The 1.0.x version of Postgres95 was about 30-50% faster than version 4.2 of POSTGRES when running the Wisconsin Benchmark test. In addition to fixing some bugs, the following are some major improvements:\n\nThe original query language PostQUEL was replaced by SQL (implemented on the server side). The interface library libpq was named after PostQUEL. Before PostgreSQL, subqueries were not supported (see below), but they could be simulated in Postgres95 by user-defined SQL functions. Aggregate functions were re-implemented. Support for GROUP BY query clauses was also added.\n\nA new program for interactive SQL queries using the Readline of GNU was added (psql). This program largely replaced the old monitor program.\n\nA new frontend library (libpgtcl) was added to support Tcl-based clients. A sample shell (pgtclsh) provided new Tcl commands for interaction between Tcl programs and the Postgres95 server.\n\nThe interface for large objects was completely rewritten. The inversion file system was removed, retaining inversion as the only mechanism for storing large objects.\n\nThe instance-level rule system was removed. However, rules still exist in the form of rewrite rules.\n\nA brief tutorial introducing SQL and Postgres95 features was added to the released source code.\n\nPostgres95 could be compiled using unpatched GCC (fixing double-precision data alignment issues).\n\n\n2.3. PostgreSQL\nBy 1996, it was clear that the name Postgres95 was outdated. We chose a new name, PostgreSQL, to reflect the relationship between the original POSTGRES and the latest version with SQL capabilities. The version number also started from 6.0, returning to the sequence initially started by the Berkeley POSTGRES project.\n\nMany people continue to use Postgres to refer to PostgreSQL (now rarely in all uppercase letters) due to tradition or easier pronunciation. This usage is widely accepted as a nickname or alias.\n\nThe development of Postgres95 focused on identifying and understanding existing issues in the backend code. The development of PostgreSQL shifted to some controversial features and functions, with work in all areas being carried out simultaneously.\n\n Changes to PostgreSQL since then can be found in Appendix E.'),
     ('Overview of MySQL Database Management System', 'MySQL is the most popular open-source SQL database management system, developed, released, and supported by MySQL AB. MySQL AB is a commercial company founded by several MySQL developers. It is a second-generation open-source company that combines open-source values, methods, and a successful business model.\n\nThe MySQL website (http://www.mysql.com/) provides the latest information about MySQL and MySQL.\n\n·         MySQL is a database management system.\n\nA database is a structured collection of data. It can be anything from a simple shopping list to an art gallery or a vast amount of information in a corporate network. To add data to a database or access and process data stored in a computer database, you need to use a database management system such as the MySQL server. Computers are ideal tools for handling large amounts of data, so database management systems play a critical central role in computing, either as standalone utilities or as components of other applications.\n\nMySQL is a relational database management system.\n\nA relational database stores data in different tables rather than putting all data in one large repository. This increases speed and flexibility. SQL in MySQL refers to Structured Query Language. SQL is the most commonly used standardized language for accessing databases, defined by the ANSI/ISO SQL standard. The SQL standard has evolved since 1986, with several versions. In this manual, SQL-92 refers to the standard released in 1992, SQL:1999 refers to the standard released in 1999, and SQL:2003 refers to the current version of the standard. We use the term SQL standard to denote the current version of the SQL standard.\n\nMySQL software is open-source software.\n\nOpen source means that anyone can use and change the software. Anyone can download MySQL software from the Internet without paying any fees. If you want, you can study the source code and make appropriate changes to meet your own needs. MySQL software is licensed under the GPL (GNU General Public License), http://www.mysql.com/company/legal/licensing/).\n\nThe MySQL database server is fast, reliable, and easy to use.\n\nIf it is what you are looking for, give it a try. The MySQL server also has a set of practical features developed through close cooperation with our users. Our benchmark homepage provides comparison results of the MySQL server and other database managers. See 7.1.4 MySQL Benchmark Suite.\n\nThe MySQL server was initially developed to handle large databases, and it is faster than existing solutions. Over the years, it has been successfully used in many demanding production environments. Although MySQL is constantly evolving, the MySQL server currently offers rich and useful features. It has good connectivity, speed, and security, making MySQL very suitable for accessing databases on the Internet.\n\nThe MySQL server operates in client/server mode or embedded systems.\n\nThe MySQL database software is a client/server system consisting of a multithreaded SQL server that supports different backends, several different client programs and libraries, numerous management tools, and extensive application programming interfaces (APIs).\n\nWe can also provide the MySQL server as an embedded multithreaded library that you can link to your application, resulting in a smaller, faster, and more manageable product.\n\nThere is a large amount of shared MySQL software available.\n\nIt is very likely that your favorite applications and languages support the MySQL database server.\n\nThe official pronunciation of MySQL is My Ess Que Ell (not my sequel), but we do not mind if you pronounce it as my sequel or in any other local way.'),
     ('What is SQL Server?', 'Microsoft SQL Server is a relational database management system (RDBMS). Applications and tools connect to SQL Server instances or databases and communicate using Transact-SQL (T-SQL).\n\nDeployment Options\nSQL Server can be installed on Windows or Linux, deployed in Linux containers, or deployed on Azure virtual machines or other virtual machine platforms. You may have previously referred to it as a boxed product.\n\nWhich SQL Server versions are supported depends on your licensing agreement, but for the purposes of this document, we refer to SQL Server 2016 (13.x) and later versions. For documentation on SQL Server 2014 (12.x) and earlier versions, see the documentation for previous versions of SQL Server. To find out which SQL Server versions are currently supported, see SQL Server End of Support Options.\n\nThe following products and services also use the underlying SQL Server database engine:\n\nAzure SQL Database\nAzure SQL Managed Instance\nMicrosoft Analytics Platform System (PDW)\nAzure Synapse Analytics\nAzure SQL Edge\nFor a list of features supported by various versions of SQL Server on Windows, see:\n\nSQL Server 2022 versions and their supported features\nSQL Server 2019 versions and their supported features\nVersions and features supported by SQL Server 2017\nVersions and features supported by SQL Server 2016\nSQL Server Components and Technologies\nThis section introduces some key technologies available in SQL Server.\n\nComponent\tDescription\nDatabase Engine\tThe database engine is the core service for storing, processing, and protecting data. The database engine provides controlled access and transaction processing to meet the requirements of the most demanding data consumption applications within an enterprise. The database engine also provides comprehensive support for business continuity and database recovery (SQL Server) to maintain business continuity.\nMachine Learning Services (MLS)\tSQL Server Machine Learning Services supports integrating machine learning into enterprise workflows using popular R and Python languages.\n\nMachine Learning Services (in-database) integrates R and Python with SQL Server, allowing users to easily generate, retrain models, and score models by calling stored procedures. Machine Learning Server provides enterprise-level support for R and Python, allowing users to use them without SQL Server.\nIntegration Services (SSIS)\tSQL Server Integration Services is a platform for building high-performance data integration solutions, including packages for extract, transform, and load (ETL) processing for data warehouses.\nAnalysis Services (SSAS)\tSQL Server Analysis Services is an analytical data platform and toolset for personal, team, and corporate business intelligence. Server and client designers support traditional OLAP solutions, new tabular modeling solutions, and self-service analytics and collaboration by using Power Pivot, Excel, and SharePoint Server environments. Analysis Services also includes data mining, allowing you to discover patterns and relationships hidden in large amounts of data.\nReporting Services (SSRS)\tSQL Server Reporting Services provides web-enabled enterprise-level reporting capabilities. It allows users to create reports that extract content from multiple data sources, publish reports in various formats, and centrally manage security and subscriptions.\nReplication\tSQL Server Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Replication allows you to distribute data to different locations and distribute it to remote or mobile users over local area networks, wide area networks, dial-up connections, wireless connections, and the Internet.\nData Quality Services (DQS)\tData Quality Services provides a knowledge-driven data cleansing solution. DQS allows you to generate a knowledge base and then use this knowledge base, along with computer-assisted and interactive methods, to perform data correction and deduplication. You can use cloud-based reference data services and generate a data management solution that integrates DQS with SQL Server Integration Services and Master Data Services.\nMaster Data Services (MDS)\tMaster Data Services is the master data management solution for SQL Server. Solutions built on Master Data Services help ensure that reports and analyses are based on the appropriate information. With Master Data Services, you can create a central repository for master data and maintain an auditable, secure record of master data as it changes over time.\n')
   ;

5. chunk テーブルに、分割されたチャンク、その埋め込みベクトル、および tsvector が含まれていることを確認します:

   SELECT * FROM chunk;

ステップ3：埋め込みのバッチ生成

ステップ2で埋め込みトリガーを作成した場合は、このステップをスキップしてください。トリガーは、ドキュメントが挿入されたときに自動的に埋め込みを生成します。

固定サイズの関数で分割できない複雑なドキュメントについては、LangChain などのフレームワークを使用して事前にチャンクを準備します。チャンクがエンベディングなしで chunk テーブルに格納された後、次のいずれかの方法を使用してエンベディングを生成します。

UPDATE chunk
SET embedding = rds_ai.embed(text)::vector;

ステップ4：マルチモーダル検索の実装

ベクトル検索は、正確な単語が異なる場合でも、意味的に関連するコンテンツを検索します。キーワード検索は、クエリ内の特定の用語を含む結果を検索します。両方を組み合わせることで、より幅広いクエリでの再現率が向上します。特定のエラーコードに関する質問の場合、キーワード検索は正確な用語を検索します。表現が異なる概念的な質問の場合、ベクトル検索は関連する箇所を検索します。

両方の検索メソッドを実行し、結合された結果を返すmulti_retrieve 関数を作成します。

CREATE OR REPLACE FUNCTION multi_retrieve(query TEXT)
RETURNS TABLE(ret_chunk text, score numeric, method text, rank int) AS $$
DECLARE
    rec RECORD;
BEGIN
    -- tsvector 全文検索を使用したキーワード検索。
    FOR rec IN
        SELECT
            text,
            subquery.score,
            'retrieve_by_key_word' AS method,
            RANK() OVER (ORDER BY subquery.score DESC) AS rank_id
        FROM (
            SELECT
                text,
                ts_rank(
                    ts_vector_extra,
                    to_tsquery(replace(
                        text(plainto_tsquery('pg_catalog.english', query)), '&', '|'
                    ))
                ) AS score
            FROM chunk
            WHERE ts_vector_extra @@ to_tsquery(replace(
                text(plainto_tsquery('pg_catalog.english', query)), '&', '|'
            ))
        ) AS subquery
        ORDER BY subquery.score DESC
        LIMIT 5
    LOOP
        ret_chunk := rec.text;
        score := rec.score;
        method := rec.method;
        rank := rec.rank_id;
        RETURN NEXT;
    END LOOP;

    -- 埋め込みベクトルのコサイン距離を使用したベクトル検索。
    FOR rec IN
        SELECT
            *,
            'retrieve_by_vector' AS method,
            RANK() OVER (ORDER BY distance) AS rank_id
        FROM rds_ai.retrieve(
            query,
            'public',
            'chunk',
            'text',
            'embedding',
            distance_type => 'cosine',
            topn => 5
        )
    LOOP
        ret_chunk := rec.chunk;
        score := rec.distance;
        method := rec.method;
        rank := rec.rank_id;
        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

サンプルクエリで検索をテストします。

SELECT *
FROM multi_retrieve('Introduce the origins of the PostgreSQL database.')
ORDER BY method, rank;

ステップ5：結果の再ランキング

再ランキングは、結合された検索結果を再スコアリングして、LLM に渡す前に最も関連性の高いチャンクを上位に表示します。2つのアプローチが利用可能です。

DROP FUNCTION get_reranked_results(text, integer);
CREATE OR REPLACE FUNCTION get_reranked_results(query TEXT, top_n INT DEFAULT 1)
RETURNS TABLE(score FLOAT, value TEXT) AS $$
DECLARE
    result_array TEXT[];
BEGIN
    SELECT array_agg(ret_chunk)
    INTO result_array
    FROM multi_retrieve(query);

    RETURN QUERY
    SELECT score_value AS score, text_value AS value
    FROM rds_ai.rank(query, result_array)
    ORDER BY score DESC
    LIMIT top_n;
END $$ LANGUAGE plpgsql;

SELECT * FROM get_reranked_results('Introduce the origins of the PostgreSQL database.');

CREATE TYPE score_agg_state AS (
    vector_score numeric,
    keyword_score numeric
);

CREATE OR REPLACE FUNCTION score_agg_transfn(state score_agg_state, rank numeric, method text)
RETURNS score_agg_state AS $$
BEGIN
    CASE method
        WHEN 'retrieve_by_vector' THEN
            state.vector_score := COALESCE(1 / (60+rank), 0);
        WHEN 'retrieve_by_key_word' THEN
            state.keyword_score := COALESCE(1 / (60+rank), 0);
    END CASE;
    RETURN state;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION score_agg_finalfn(state score_agg_state)
RETURNS numeric AS $$
BEGIN
    RETURN COALESCE(state.vector_score, 0) + COALESCE(state.keyword_score, 0);
END;
$$ LANGUAGE plpgsql;

CREATE AGGREGATE rff_function(numeric, text)(
    SFUNC = score_agg_transfn,
    STYPE = score_agg_state,
    FINALFUNC = score_agg_finalfn,
    INITCOND = '(0, 0)'
);

CREATE OR REPLACE FUNCTION get_reranked_results(query TEXT, top_n INT DEFAULT 1)
RETURNS TABLE(score numeric, value TEXT) AS $$
BEGIN
    RETURN QUERY
    SELECT
        rff_function(rank, method) AS score,
        ret_chunk AS value
    FROM
        multi_retrieve(query)
    GROUP BY
        value
    ORDER BY
        score DESC
    LIMIT top_n;
END $$ LANGUAGE plpgsql;

DROP FUNCTION get_reranked_results(text, integer);
CREATE OR REPLACE FUNCTION get_reranked_results(query TEXT, top_n INT DEFAULT 1)
RETURNS TABLE(score FLOAT, value TEXT) AS $$
DECLARE
    result_array TEXT[];
BEGIN
    SELECT array_agg(chunk)
    INTO result_array
    FROM rds_ai.retrieve(query, 'public', 'chunk', 'text', 'embedding');

    RETURN QUERY
    SELECT score_value AS score, text_value AS value
    FROM rds_ai.rank(query, result_array)
    ORDER BY score DESC
    LIMIT top_n;
END $$ LANGUAGE plpgsql;

-- テスト
SELECT * FROM get_reranked_results('Introduce the origins of the PostgreSQL database.');

ステップ6：LLM からの回答の取得

SELECT *
FROM rds_ai.rag(
    'Introduce the origins of the PostgreSQL database.',
    'public',
    'chunk',
    'text',
    'embedding'
);