Building Ai Applications with the Tencentdb Ai Plug-In

Last updated: 2025-08-14 17:17:37
Building Ai Applications with the Tencentdb Ai Plug-In
Last updated: 2025-08-14 17:17:37
This document needs to add the tencentdb_ai plug-in. Refer to Using the Tencentdb_ai Plug-In to Call a Large Model for the usage.
Integrating DeepSeek-V3 for Chat Implementation
1. Create the tencentdb_ai plug-in.
damoxing=> CREATE EXTENSION tencentdb_ai CASCADE;
NOTICE:  installing required extension "pgcrypto"
CREATE EXTENSION
damoxing=> SELECT * FROM pg_extension;
  oid  |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
 14275 | plpgsql      |       10 |           11 | f              | 1.0        |           | 
 16631 | pgcrypto     |    16615 |         2200 | t              | 1.3        |           | 
 16668 | tencentdb_ai |    16615 |         2200 | t              | 1.0        | {16670}   | {""}
(3 rows)
2. Add the DeepSeek-V3 model.
damoxing=> SELECT tencentdb_ai.add_model('deepseek-v3', '2024-05-22', 'ap-guangzhou', '$.Response.Choices[*].Message.Content');
 add_model 
-----------
 
(1 row)
﻿
damoxing=> SELECT tencentdb_ai.update_model_attr('deepseek-v3', 'SecretId', 'AKID**************');
 update_model_attr 
-------------------
 
(1 row)
﻿
damoxing=> SELECT tencentdb_ai.update_model_attr('deepseek-v3', 'SecretKey', '*******************');
 update_model_attr 
-------------------
 
(1 row)
﻿
3. Prepare cloud database PostgreSQL objects.
damoxing=> CREATE TABLE chat_logs (
    id SERIAL PRIMARY KEY,
    user_input TEXT,
    system_response TEXT,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE
damoxing=>
4. Call a large model chat.
Create a stored procedure call for the deepseek-v3 API interface chat:
damoxing=> CREATE OR REPLACE FUNCTION chat_with_deepseek(user_input TEXT)
RETURNS TEXT AS $$
DECLARE
    system_response TEXT;
BEGIN
        SELECT tencentdb_ai.chat_completions('deepseek-v3', user_input) INTO system_response;
    INSERT INTO chat_logs (user_input, system_response)
    VALUES (user_input, system_response);
﻿
    RETURN system_response;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
damoxing=> 
Invoke stored procedure chat:
damoxing=> SELECT chat_with_deepseek('hi,deepseek');
                                                                                                   chat_with_deepseek                   
                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------
 "Hello! It seems like you're trying to reach DeepSeek, but I’m not sure what you’re looking for. Could you clarify? Are you referring t
o a specific tool, service, or something else? Let me know how I can assist!"
(1 row)
﻿
damoxing=> 
5. Query chat results.
damoxing=> SELECT * FROM chat_logs ORDER BY timestamp DESC;
 id |  user_input  |                                                                                                     system_response
                                                                                                     |         timestamp          
----+--------------+--------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------+----------------------------
  3 | hi,deepseek | "Hello! It seems like you're trying to reach DeepSeek, but I’m not sure what you’re looking for. Could you clarify?
 Are you referring to a specific tool, service, or something else? Let me know how I can assist!" | 2025-02-18 16:02:39.145322
(1 row)
﻿
damoxing=> 
Integrating Lke-Text-Embedding-V1 for Text Retrieval
1. Create the tencentdb_ai plug-in.
damoxing_lke_embdding=> CREATE EXTENSION tencentdb_ai CASCADE;
NOTICE:  installing required extension "pgcrypto"
CREATE EXTENSION
damoxing_lke_embdding=> SELECT * FROM pg_extension;
  oid  |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
 14275 | plpgsql      |       10 |           11 | f              | 1.0        |           | 
 16631 | pgcrypto     |    16615 |         2200 | t              | 1.3        |           | 
 16668 | tencentdb_ai |    16615 |         2200 | t              | 1.0        | {16670}   | {""}
(3 rows)
2. Add the lke-text-embedding-v1 model.
damoxing_lke_embdding=> SELECT tencentdb_ai.add_model('lke-text-embedding-v1', '2024-05-22', 'ap-guangzhou', NULL);
-[ RECORD 1 ]
add_model | 
﻿
damoxing_lke_embdding=> SELECT tencentdb_ai.update_model_attr('lke-text-embedding-v1', 'SecretId', 'AKID**************');
-[ RECORD 1 ]-----+-
update_model_attr | 
﻿
damoxing_lke_embdding=> SELECT tencentdb_ai.update_model_attr('lke-text-embedding-v1', 'SecretKey', '*******************');
-[ RECORD 1 ]-----+-
update_model_attr | 
﻿
damoxing_lke_embdding=> 
3. Prepare TencentDB for PostgreSQL objects.
damoxing_lke_embdding=> CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding FLOAT8[]
);
CREATE TABLE
damoxing_lke_embdding=> 
4. Create a stored procedure to generate embedded vectors.
damoxing_lke_embdding=> CREATE OR REPLACE FUNCTION generate_embedding(doc_contents TEXT[])
RETURNS FLOAT8[][] AS $$
DECLARE
    embeddings FLOAT8[][] := '{}'; 
    embedding FLOAT8[];
    doc_content TEXT;
BEGIN
        SELECT * from tencentdb_ai.get_embedding('lke-text-embedding-v1', doc_contents) INTO embedding;
    RETURN embedding;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
damoxing_lke_embdding=>
5. Create a stored procedure to insert documents and its embedded vectors.
damoxing_lke_embdding=> CREATE OR REPLACE FUNCTION insert_documents(doc_contents TEXT[])
RETURNS VOID AS $$
DECLARE
    embedding FLOAT8[];
    doc_content TEXT;
BEGIN
        doc_content := doc_contents[0];
        embedding :=  generate_embedding(doc_contents);
        INSERT INTO documents (content, embedding)
        VALUES (doc_contents, embedding);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
damoxing_lke_embdding=> 
6. Create a stored procedure for cosine similarity calculation.
damoxing_lke_embdding=> CREATE OR REPLACE FUNCTION cosine_similarity(vec1 FLOAT8[], vec2 FLOAT8[])
RETURNS FLOAT8 AS $$
DECLARE
    dot_product FLOAT8 := 0;
    norm1 FLOAT8 := 0;
    norm2 FLOAT8 := 0;
    similarity FLOAT8;
BEGIN
    FOR i IN 1..array_length(vec1, 1) LOOP
        dot_product := dot_product + vec1[i] * vec2[i];
        norm1 := norm1 + vec1[i] * vec1[i];
        norm2 := norm2 + vec2[i] * vec2[i];
    END LOOP;
﻿
    similarity := dot_product / (sqrt(norm1) * sqrt(norm2));
    RETURN similarity;
END;
$$ LANGUAGE plpgsql;
damoxing_lke_embdding=> 
7. Create a stored procedure for text retrieval.
damoxing_lke_embdding=> CREATE OR REPLACE FUNCTION search_similar_documents(query TEXT, limit_count INT)
RETURNS TABLE(id INT, content TEXT, similarity FLOAT8) AS $$
DECLARE
    query_embedding FLOAT8[];
BEGIN
    query_embedding := generate_embedding(ARRAY[query]);
﻿
    RETURN QUERY
    SELECT d.id, d.content, cosine_similarity(d.embedding, query_embedding) AS similarity
    FROM documents d
    ORDER BY similarity DESC
    LIMIT limit_count;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
damoxing_lke_embdding=> 
8. Insert documents and its embedded vectors.
damoxing_lke_embdding=> SELECT insert_documents(ARRAY['This is a sample document.']);
-[ RECORD 1 ]----+-
insert_documents | 
﻿
damoxing_lke_embdding=> SELECT insert_documents(ARRAY['Another example of a document.']);
-[ RECORD 1 ]----+-
insert_documents | 
﻿
damoxing_lke_embdding=> SELECT insert_documents(ARRAY['This document is about PostgreSQL and text embeddings.']);
-[ RECORD 1 ]----+-
insert_documents | 
﻿
damoxing_lke_embdding=> SELECT insert_documents(ARRAY['Deep learning models can generate text embeddings.']);
-[ RECORD 1 ]----+-
insert_documents | 
﻿
damoxing_lke_embdding=> 
9. Perform text retrieval.
damoxing_lke_embdding=> SELECT * FROM search_similar_documents('PostgreSQL text embeddings', 5);
-[ RECORD 1 ]----------------------------------------------------------
id         | 3
content    | {"This document is about PostgreSQL and text embeddings."}
similarity | 0.8649945496222797
-[ RECORD 2 ]----------------------------------------------------------
id         | 4
content    | {"Deep learning models can generate text embeddings."}
similarity | 0.6824638514797066
-[ RECORD 3 ]----------------------------------------------------------
id         | 1
content    | {"This is a sample document."}
similarity | 0.66412244051794
-[ RECORD 4 ]----------------------------------------------------------
id         | 2
content    | {"Another example of a document."}
similarity | 0.6142928906219256
﻿
damoxing_lke_embdding=>
Integrating Lke-Reranker-Base to Implement RAG
1. Create the tencentdb_ai plug-in.
damoxing_rerank=> CREATE EXTENSION tencentdb_ai CASCADE;
NOTICE:  installing required extension "pgcrypto"
CREATE EXTENSION
damoxing=> SELECT * FROM pg_extension;
  oid  |   extname    | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
-------+--------------+----------+--------------+----------------+------------+-----------+--------------
 14275 | plpgsql      |       10 |           11 | f              | 1.0        |           | 
 16631 | pgcrypto     |    16615 |         2200 | t              | 1.3        |           | 
 16668 | tencentdb_ai |    16615 |         2200 | t              | 1.0        | {16670}   | {""}
(3 rows)
2. Add the lke-reranker-base and lke-text-embedding-v1 models.
damoxing_rerank=> SELECT tencentdb_ai.add_model('lke-reranker-base', '2024-05-22', 'ap-guangzhou', '$.Response.ScoreList');
 add_model 
-----------
 
(1 row)
﻿
damoxing_rerank=> SELECT tencentdb_ai.update_model_attr('lke-reranker-base', 'SecretId', 'AKID**************');
 update_model_attr 
-------------------
 
(1 row)
﻿
damoxing_rerank=> SELECT tencentdb_ai.update_model_attr('lke-reranker-base', 'SecretKey', '*******************');
 update_model_attr 
-------------------
 
(1 row)
﻿
damoxing_rerank=> SELECT tencentdb_ai.add_model('lke-text-embedding-v1', '2024-05-22', 'ap-guangzhou', NULL);
 add_model 
-----------
 
(1 row)
﻿
damoxing_rerank=> SELECT tencentdb_ai.update_model_attr('lke-text-embedding-v1', 'SecretId', 'AKID**************');
 update_model_attr 
-------------------
 
(1 row)
﻿
damoxing_rerank=> SELECT tencentdb_ai.update_model_attr('lke-text-embedding-v1', 'SecretKey', '*******************');
 update_model_attr 
-------------------
 
(1 row)
3. Prepare TencentDB for PostgreSQL objects.
damoxing_rerank=> CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT,
    embedding FLOAT8[]
);
CREATE TABLE
damoxing_rerank=> 
4. Create a stored procedure to generate embedded vectors.
damoxing_rerank=> CREATE OR REPLACE FUNCTION generate_embedding(doc_contents TEXT[])
RETURNS FLOAT8[][] AS $$
DECLARE
    embeddings FLOAT8[][] := '{}'; 
    embedding FLOAT8[];
    doc_content TEXT;
BEGIN
        SELECT * from tencentdb_ai.get_embedding('lke-text-embedding-v1', doc_contents) INTO embedding;
    RETURN embedding;
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
5. Create a stored procedure to insert documents and its embedded vectors.
damoxing_rerank=> CREATE OR REPLACE FUNCTION insert_documents(doc_contents TEXT[])
RETURNS VOID AS $$
DECLARE
    embedding FLOAT8[];
    doc_content TEXT;
BEGIN
        doc_content := doc_contents[0];
        embedding :=  generate_embedding(doc_contents);
        INSERT INTO documents (content, embedding)
        VALUES (doc_contents, embedding);
END;
$$ LANGUAGE plpgsql;
CREATE FUNCTION
6. Create a stored procedure for recall and large model rearrange.
damoxing_rerank=> CREATE OR REPLACE FUNCTION retrieve_and_rerank(query TEXT)
RETURNS TABLE (id INT, content TEXT, score FLOAT8) AS $$
DECLARE
    keyword_results RECORD;
    vector_results RECORD;
    query_vector VECTOR; 
    rerank_content TEXT[];
    rerank_ids INT[];
    rerank_scores my_record;
    i INT;
BEGIN
    -- Generate query vector
    SELECT generate_embedding(ARRAY[query])::vector INTO query_vector;
    
    -- Initialize reorder content array
    rerank_content := ARRAY[]::TEXT[];
    rerank_ids := ARRAY[]::INT[];
    
    -- keyword recall
    FOR keyword_results IN
        SELECT d.id, d.content, d.embedding
        FROM documents d
        WHERE to_tsvector('english', d.content) @@ plainto_tsquery('english', query)
    LOOP
        -vector recall
        FOR vector_results IN
            SELECT d.id, d.content, d.embedding
            FROM documents d
            WHERE d.id = keyword_results.id
            ORDER BY d.embedding <-> query_vector
            LIMIT 10
        LOOP
            rerank_content := array_append(rerank_content, vector_results.content);
            rerank_ids := array_append(rerank_ids, vector_results.id);
        END LOOP;
    END LOOP;
    
    -- Large model reorder
    
    SELECT *  FROM tencentdb_ai.run_rerank('lke-reranker-base', query, rerank_content) INTO rerank_scores; 
    RETURN QUERY
    select text_field, numeric_field from rerank_scores;
END;
$$ LANGUAGE plpgsql;
7. Invoke stored procedure.
damoxing_rerank=>SELECT insert_documents(ARRAY['This is the first document.']);
-[ RECORD 1 ]----+-
insert_documents |
damoxing_rerank=> SELECT * FROM retrieve_and_rerank('document');
("{""This is the first document.""}",-1.5107422)
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
Feedback
tencent cloud

Integrating DeepSeek-V3 for Chat Implementation

Integrating Lke-Text-Embedding-V1 for Text Retrieval

Integrating Lke-Reranker-Base to Implement RAG
