Tous les produits
Search

Ce produit

    ApsaraDB RDS:Implement RAG using the rds_ai extension

    Centre de documentation

    ApsaraDB RDS:Implement RAG using the rds_ai extension

    Dernière mise à jour :Oct 12, 2026

    L'extension rds_ai pour RDS for PostgreSQL intègre des modèles avancés d'Alibaba Cloud Model Studio, notamment Qwen, ainsi que des modèles d'embedding et de reclassement de texte polyvalents. Cette rubrique explique comment utiliser l'extension rds_ai pour implémenter la génération augmentée par récupération (RAG) directement dans une base de données, en utilisant uniquement du SQL.

    and general-purpose text reranking

    Prérequis

    • Vérifiez que votre instance RDS for PostgreSQL respecte les exigences de version indiquées dans le tableau suivant :

      Version majeure

      Version mineure

      PostgreSQL 16

      20241230 ou ultérieure

      PostgreSQL 14, PostgreSQL 15 et PostgreSQL 17

      20250430 ou ultérieure

      PostgreSQL 18

      20251130 ou ultérieure

      Pour mettre à jour la version mineure du moteur, consultez la rubrique Mettre à jour la version mineure du moteur.

    • Un compte privilégié pour RDS for PostgreSQL a été créé. Pour savoir comment créer un compte privilégié, consultez la rubrique Créer un compte.

    • Vous devez activer Alibaba Cloud Model Studio et obtenir une clé API. Pour plus d'informations, consultez la rubrique Créer une clé API.

    • Le réseau doit être configuré pour permettre à votre instance RDS for PostgreSQL d'accéder aux modèles externes. Pour plus d'informations, consultez la rubrique Configurer une connexion réseau.

    Présentation de la solution

    Cette solution utilise l'extension rds_ai pour implémenter la génération augmentée par récupération (RAG) au sein de votre base de données à l'aide de commandes SQL simples. Cette approche est idéale pour les cas d'utilisation peu complexes, car elle vous permet de créer une application RAG sans écrire de code applicatif.

    Aucune gestion de frameworks RAG complexes, d'environnements d'exécution ou d'autres composants n'est nécessaire. Une compréhension approfondie du RAG n'est pas requise. Vous pouvez effectuer des opérations RAG via de simples instructions SQL, ce qui réduit les barrières techniques et améliore l'efficacité. Pour plus d'informations, consultez la rubrique rds_ai.

    La solution suit les étapes suivantes :

    1. Fractionnement des documents : utilisez SQL pour diviser le contenu de vos documents en fragments plus petits.

    2. Embedding de texte : utilisez l'extension rds_ai pour convertir le texte en vecteurs.

    3. Récupération multimodale : utilisez l'extension rds_ai pour récupérer des informations via plusieurs méthodes, telles que la récupération basée sur l'embedding et la récupération basée sur les mots-clés.

    4. Reclassement : utilisez l'extension rds_ai pour reclasser les résultats récupérés afin d'améliorer leur pertinence.

    5. Appel de modèle de langage large (LLM) : utilisez l'extension rds_ai pour appeler un service LLM afin de synthétiser une réponse à votre question.

    Étape 1 : Installer et configurer les extensions

    1. Installez les extensions rds_ai et pg_jieba. L'extension pg_jieba fournit la segmentation des mots chinois. Pour plus d'informations, consultez la rubrique Segmentation des mots chinois (pg_jieba).

      1. Accédez à la page Instances. Dans la barre de navigation supérieure, sélectionnez une région. Cliquez ensuite sur l'ID de l'instance cible.

      2. Dans le volet de navigation de gauche, cliquez sur Plug-ins.

      3. Sous l'onglet Extension Marketplace, localisez l'extension rds_ai et cliquez sur Install.

      4. Dans la boîte de dialogue qui s'affiche, sélectionnez la base de données et le compte cibles, puis cliquez sur Install pour installer l'extension dans la base de données.

      5. Attendez que l'état de l'instance passe de Maintaining Instance à Running. Répétez ensuite les étapes précédentes pour installer l'extension pg_jieba.

        Remarque

        L'installation prend environ une minute. Actualisez la page pour vérifier l'état de l'instance.

    2. Se connecter à votre instance RDS for PostgreSQL et configurez l'extension rds_ai dans le schéma public de la base de données cible.

      1. Définissez la clé API pour tous les modèles par défaut.

        -- Set the API key for all models in rds_ai.model_list.
SELECT rds_ai.update_model(model_name,'token','sk-****') FROM rds_ai.model_list;

      2. L'extension rds_ai utilise l'extension pgsql-http pour appeler des modèles à distance. Définissez les paramètres de délai d'expiration suivants pour interrompre les appels de longue durée.

        Important

        Il s'agit de paramètres au niveau de la session qui doivent être reconfigurés pour chaque nouvelle session.

        -- Set the request timeout in milliseconds.
SET http.timeout_msec TO 200000;

SELECT http.http_set_curlopt('CURLOPT_TIMEOUT', '200000');

-- Set the connection timeout.
SELECT http.http_set_curlopt('CURLOPT_CONNECTTIMEOUT_MS', '200000');

    Étape 2 : Préparer les données pour RAG

    Insérez les documents de la base de connaissances dans une base de données à l'aide d'instructions SQL. Lors de cette opération, portez une attention particulière à l'échappement du texte. Vous pouvez utiliser un outil d'échappement pour traiter ces caractères. Par exemple, un saut de ligne est échappé sous la forme \n.

    1. Créez les tables de test et les index.

      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 indexes.
CREATE INDEX idx_doc_id ON chunk (doc_id);

CREATE INDEX ON chunk USING hnsw (embedding vector_cosine_ops);

CREATE INDEX chunk_text_gin ON chunk USING gin (ts_vector_extra);

CREATE INDEX ON chunk USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

    2. Créez la fonction split_text pour diviser les textes longs. Cette fonction utilise une longueur fixe en caractères pour le découpage et définit une longueur de chevauchement entre deux blocs consécutifs. Pour des méthodes de découpage plus complexes, nous vous recommandons d'utiliser un framework spécialisé dans le fractionnement de texte.

      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
    -- Handle edge cases.
    IF chunk_overlap >= chunk_size THEN
        RAISE EXCEPTION 'chunk_overlap must be less than chunk_size';
    END IF;

    current_idx := 1;

    LOOP
        -- Set the start position.
        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. Créez des déclencheurs pour remplir automatiquement la table chunk lors de l'insertion de documents dans la table doc.

      Si vos scénarios de traitement et de fragmentation de documents sont complexes, reportez-vous à l'Étape 3 : Convertir les données documentaires en embeddings par lots pour mettre en œuvre l'intégration de texte par lots.

      -- Create a trigger to automatically call the split_text function and insert chunks into the chunk table when data is inserted into the doc table.
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 a trigger to automatically delete chunks from the chunk table when the corresponding document is deleted.
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();

-- Create a trigger to automatically call the rds_ai.embed function to convert the text in the chunk table to an embedding.
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();

-- Create a trigger to automatically convert the text in the chunk table to a tsvector for keyword-based retrieval.
CREATE TRIGGER embedding_tsvector_update
BEFORE UPDATE OR INSERT ON chunk
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger(
    'ts_vector_extra',
    'public.jiebacfg',
    'text'
);

    4. Insérez les données des documents de la base de connaissances. Par exemple :

      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. Consultez la table chunk. Chaque ligne contient un fragment de texte issu du document d'origine, ainsi que son embedding et son tsvector générés automatiquement.

      SELECT * FROM chunk;

      6. Étape 3 : Convertir les documents en embeddings

      Vous pouvez ignorer cette étape si vous avez déjà créé des déclencheurs pour mettre à jour automatiquement la table chunk lors de l'insertion de données dans la table doc.

      Si vos scénarios de traitement et de découpage des documents sont complexes, nous vous recommandons d'utiliser un framework RAG tel que LangChain pour un traitement programmatique. Vous pouvez effectuer des opérations d'embedding sur une table chunk existante dans la base de données. Cette rubrique explique comment réaliser l'embedding de texte en utilisant des appels individuels à la fonction rds_ai.embed ou en effectuant des appels par lot via une fonction personnalisée.

      • Appels individuels

        Utilisez la fonction rds_ai.embed pour convertir le texte de chaque enregistrement de la table chunk en une représentation vectorielle, puis stockez ces vecteurs dans la colonne embedding.

        -- Convert the entire table to embeddings by using individual calls.
UPDATE chunk
SET embedding = rds_ai.embed(text)::vector;

      • Appels par lot

        1. Ajoutez un modèle personnalisé text-embedding-v3-batch pour les appels par lot.

          SELECT rds_ai.add_model(
    'text-embedding-v3-batch',          -- Model name
    'POST',                             -- Request method 
    ARRAY[('Authorization', 'Bearer %s')]::http.http_header[],  -- Request header
    'https://dashscope.aliyuncs.com/compatible-mode/v1/embeddings', -- Request URL
    'application/json',                 -- Request content type
    '{
        "model": "text-embedding-v3",
        "input": %s
    }',                               -- Request body
    'SELECT %L::jsonb->''data''' -- SQL to process the response
);

        2. Définissez la clé API pour le nouveau modèle.

          SELECT rds_ai.update_model(
    'text-embedding-v3-batch',
    'token',
    'sk-xxxxxx'
);

        3. Testez le modèle.

          SELECT * 
FROM rds_ai.invoke_model(
    'text-embedding-v3-batch',
    ARRAY['["01","02","03"]']
);

      Les appels par lot sont nettement plus rapides, mais leur mise en œuvre est plus complexe. Toutefois, par souci de simplicité, nous vous recommandons de commencer par des appels individuels. La meilleure approche dépendra de vos résultats de test.

      DO $$
DECLARE
    batch_size INT := 20;         -- Batch size for embedding calls, can be adjusted based on the model
    pointer INT := 0;             -- Pointer to traverse the table
    record_count INT;             -- Total number of records in the table
    input_text TEXT;              -- Concatenated string of fields to be embedded in ["item1", "item2",..., "item n"] format
    json_results JSON;            -- JSON response from embedding API call
    json_item JSON;               -- Single element from the JSON array response containing index and embedding fields
    idx INT;                      -- Index from the JSON response
BEGIN
    -- Get the total number of records
    SELECT COUNT(*) INTO record_count FROM chunk;

    -- Loop through each batch
    WHILE pointer < record_count LOOP
        -- Construct input
        SELECT json_agg(text::TEXT) INTO input_text
        FROM (
            SELECT text
            FROM chunk
            ORDER BY id
            LIMIT batch_size OFFSET pointer
        ) AS subquery;

        -- Call the embedding model
        json_results := rds_ai.invoke_model('text-embedding-v3-batch', ARRAY[input_text]);

        -- Loop through the model results and update corresponding rows
        FOR idx IN 0..json_array_length(json_results) - 1 LOOP
            json_item := json_results->idx;
            UPDATE chunk
            SET embedding = (SELECT (json_item->>'embedding')::VECTOR(1024))
            WHERE id = (SELECT id FROM chunk ORDER BY id LIMIT 1 OFFSET pointer + idx);
        END LOOP;

        -- Update the pointer for the next batch
        pointer := pointer + batch_size;
        RAISE NOTICE '%/% has done.', pointer, record_count;  
    END LOOP;
END;
$$ LANGUAGE plpgsql;

      Étape 4 : Effectuer une recherche multimodale

      L'exemple suivant illustre la recherche multimodale, qui combine la recherche par mots-clés et la recherche basée sur les embeddings. Si vous souhaitez utiliser uniquement la recherche basée sur les embeddings, nous vous recommandons d'appeler directement la fonction rds_ai.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
    -- Retrieve by keyword
    FOR rec IN
        SELECT
            text,
            subquery.score,
            'keyword_based_retrieval' 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('jiebacfg', query)), '&', '|'
                    ))
                ) AS score
            FROM chunk
            WHERE ts_vector_extra @@ to_tsquery(replace(
                text(plainto_tsquery('jiebacfg', 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;

    -- Retrieve by embedding
    FOR rec IN
        SELECT
            *,
            'embedding_based_retrieval' 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;

      Testez la recherche multimodale.

      SELECT * 
FROM multi_retrieve('Tell me about the origin of the PostgreSQL database.') 
ORDER BY method, rank;

      Étape 5 : Réorganiser les résultats (Reranking)

      Recherche multimodale

      Cette étape décrit deux méthodes pour réorganiser les résultats : l'utilisation de la fonction rds_ai.rank et l'utilisation d'une fonction de fusion de rang réciproque (RRF).

      Utiliser la fonction rds_ai.rank

      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 *
    FROM rds_ai.rank(query, result_array)
    ORDER BY score_value DESC
    LIMIT top_n;
END $$ LANGUAGE plpgsql;

SELECT * FROM get_reranked_results('Tell me about the origin of the PostgreSQL database.');

      Utiliser la fonction RRF

      -- Create an aggregate function corresponding to the common RRF function.
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 'embedding_based_retrieval' THEN
            state.vector_score := COALESCE(1 / (60+rank), 0);
        WHEN 'keyword_based_retrieval' 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)'
);

      Appelez la fonction pour réorganiser les résultats.

      SELECT
    ret_chunk,
    rff_function(rank, method) AS score
FROM
    multi_retrieve('Tell me about the origin of the PostgreSQL database.')
GROUP BY
    ret_chunk
ORDER BY
    score DESC;

      Recherche par embedding

      Si vous utilisez la recherche basée sur les embeddings et souhaitez effectuer un reclassement fin avec la fonction rank, exécutez l'instruction SQL suivante :

      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 *
    FROM rds_ai.rank(query, result_array)
    ORDER BY score_value DESC
    LIMIT top_n;
END $$ LANGUAGE plpgsql;

-- Test the function.
SELECT * FROM get_reranked_results('Tell me about the origin of the PostgreSQL database.');

      Étape 6 : Générer la réponse

      Recherche multimodale

      1. Créez la fonction escape_for_json. Lorsque vous appelez la fonction rds_ai.prompt, les paramètres sont assemblés au format JSON et envoyés via HTTP au service Alibaba Cloud Model Studio. Par conséquent, vous devez échapper les caractères spéciaux, tels que les sauts de ligne, dans votre saisie textuelle.

        CREATE OR REPLACE FUNCTION escape_for_json(input TEXT)
RETURNS TEXT AS $$
BEGIN
    RETURN replace(replace(input, '"', '\\"'), E'\n', '\\n');
END;
$$ LANGUAGE plpgsql;

      2. Créez la fonction RAG. Cet exemple utilise la recherche multimodale avec la fonction rds_ai.rank.

        CREATE OR REPLACE FUNCTION rag(query TEXT) RETURNS TEXT AS $$
DECLARE
  prompt_content TEXT;
  result TEXT;
BEGIN
  -- Use the get_reranked_results function to get the reranked results and aggregate them into a single string.
  WITH rank_result AS (
    SELECT string_agg(value, E'\n\n') AS prompt_content_pre 
    FROM get_reranked_results(query)
  )
  -- Generate a prompt from the rank_result query.
  SELECT 
    'Based on the following content:\n' || prompt_content_pre || '\n\nAnswer my question: ' || query
  INTO prompt_content
  FROM rank_result;

  -- Use the rds_ai.prompt function to get the AI model's answer.
  SELECT 
    rds_ai.prompt(escape_for_json(prompt_content))
  INTO result;

  -- Return the result.
  RETURN result;
END;
$$ LANGUAGE plpgsql;

      3. Appelez la fonction RAG pour renvoyer le résultat.

        SELECT rag('Tell me about the origin of the PostgreSQL database.');

      Recherche par embedding

      Pour la recherche basée sur les embeddings, vous pouvez appeler directement la fonction rds_ai.rag.

      SELECT *
FROM rds_ai.rag (
    'Tell me about the origin of the PostgreSQL database.', 
    'public', 
    'chunk', 
    'text', 
    'embedding'
);