Build a Multimodal AI Financial Retrieval System with RAG - Hologres - Alibaba Cloud - Hologres

In the data-driven era, unstructured data (such as text, images, audio, video, and logs), combined with structured and semi-structured data (such as JSON), constitutes the core data assets of an enterprise. This unstructured data, in its raw and diverse forms, holds a wealth of business insights, including user feedback, contract terms, and product defect images. This guide uses a simulated financial scenario to demonstrate how to retrieve and analyze information from PDF documents, such as prospectuses and contracts, helping you make informed operational decisions.

Core capabilities

This best practice demonstrates how to process and retrieve unstructured data from PDF files using the following core capabilities of Hologres:

Object Table: Allows you to read unstructured data, such as PDF, image, and ppt files, from OSS in a tabular format.
AI function: Lets you use standard SQL to call AI functions powered by built-in large language models (LLMs) to build AI services.
- Data transformation: Offers operators for embedding and chunking to convert unstructured data into a storable, structured format. You can create embeddings automatically without relying on external algorithms.
- Data retrieval and analysis: Offers operators like ai_gen and ai_summarize to perform inference, summarization, and translation on data using SQL.
Dynamic Table: Supports incremental refresh to automatically process unstructured data. This approach processes only incremental data, reducing redundant computations and lowering resource consumption.
Vector search: Supports vector search by using standard SQL for similarity searches and scene recognition on unstructured data. You can combine vector and scalar predicates within the same query.
Full-text search: Uses mechanisms such as inverted index and tokenization to efficiently retrieve unstructured data. It supports a variety of flexible search methods, including keyword matching and phrase searching.

Benefits

With these core capabilities, Hologres offers the following benefits for multimodal AI retrieval and analytics:

End-to-end AI data pipeline: Covers the entire workflow, from data embedding and chunking to incremental transformation, retrieval, and analysis. This allows developers to build AI applications as easily as they would with a typical big data system.
Process and analyze unstructured data with standard SQL: Lets you extract and transform unstructured data using only SQL, eliminating the need for specialized programming languages or external systems. This approach simplifies data processing and shortens the learning curve for developers.
Accurate, flexible, and intelligent retrieval: Build hybrid search pipelines that combine keyword, semantic, and multimodal searches to cover a full range of scenarios, from precise queries to intent understanding. You can also integrate AI functions to deeply understand user intent, semantic association, and contextual inference, enabling more intelligent retrieval.
Secure, in-place analytics: Analyze data where it resides (in-place analytics) without exporting it to external systems. This approach integrates seamlessly with Hologres security features to effectively protect your data.

This guide demonstrates how to use these core capabilities in Hologres to process and retrieve unstructured data, helping you build an enterprise-grade multimodal AI data platform, break down data silos, and unlock the full value of your data.

Workflow

The workflow is as follows:

Prepare the dataset.
Upload the PDF files in the financial dataset to OSS.
Transform PDF data.
Use an Object Table to read the metadata of the PDF files. Then, create a Dynamic Table with incremental refresh to perform embedding and chunking on the data. Create a vector index and a full-text index on the Dynamic Table to accelerate subsequent searches.
Use the ai_embed operator to create an embedding for a natural language question. Then, use hybrid search to retrieve and rank results from full-text and vector searches. Finally, an LLM uses its inference capability to generate the most relevant answer.

Before you begin

Data preparation
This topic uses 80 corporate prospectus documents from the PDF folder in the public finance dataset on ModelScope.

Environment preparation

Create an instance of Hologres V4.0 or later and create a database.
Purchase AI resources.
This topic uses a single large-96core-512GB-384GB node as an example.

Deploy models. The following models are deployed with the specified resource allocations:

Parameter	Category	Description	vCPUs	Memory	GPU	Replicas
to_doc	ds4sd/docling-models	Converts PDF files into documents.	20	100 GB	1 card (48 GB)	1
chunk	recursive-character-text-splitter	Chunks large PDF files.	15	30 GB	0 cards (0 GB)	1
pdf_embed	BAAI/bge-base-zh-v1.5	Creates document embeddings.	7	30 GB	1 card (96 GB)	1
llm	Qwen/Qwen3-32B	Uses an LLM to perform inference on the retrieved document content based on a prompt.	7	30 GB	1 card (96 GB)	1

Note

The resources for these models are allocated by default.

Procedure

Download the PDF files and upload them to OSS.
1. Download the 80 prospectus documents (PDFs) from the Bosera-JM 14B Challenge Dataset for Finance.
2. Log on to the OSS console, create a bucket, and upload the downloaded PDF files to the bucket. For more information about the upload operation, see Simple upload.
Grant permissions to accounts.
1. Log on to the Resource Access Management (RAM) console and create an Alibaba Cloud RAM role with OSS read permissions.
  
  We recommend granting the AliyunOSSReadOnlyAccess permission.
2. Add logon and Hologres access permissions to the RAM role.
  - Alibaba Cloud account (primary account)
    
    Update the RAM role trust policy. Update the following parameters:
    - Action: Set to sts:AssumeRole.
    - Service: Set to hologres.aliyuncs.com.
```
{
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "RAM": [
          "acs:ram::1866xxxx:root"
        ],
        "Service": [
          "hologres.aliyuncs.com"
        ]
      }
    }
  ],
  "Version": "1"
}
```
  - RAM user (sub-account)
    1. Grant permissions to the RAM user.
      1. On the Access Control > Permission Model page, click Create permission policy and select Script editor mode to create a policy. For details, see Create a custom policy.
        
        Hologres uses this policy to determine whether the RAM user has permission to create the corresponding RAM role. Use the following policy document:
        { "Version": "1", "Statement": [ { "Effect": "Allow", "Action": "hologram:GrantAssumeRole", "Resource": "<RoleARN>" } ] }
      2. On the Identity management > User page, click Add permissions in the Actions column for the target RAM user to assign the policy created above. For details, see Manage RAM user permissions.
    2. Grant permissions to the created RAM role.
      
      Update the RAM role trust policy. Update the following parameters:
      - Action: Set to sts:AssumeRole.
      - Service: Set to hologres.aliyuncs.com.
```
{
  "Statement": [
    {
      "Action": "sts:AssumeRole",
      "Effect": "Allow",
      "Principal": {
        "RAM": [
          "acs:ram::1866xxxx:root"
        ],
        "Service": [
          "hologres.aliyuncs.com"
        ]
      }
    }
  ],
  "Version": "1"
}
```
Perform embedding and chunking on the PDF files.
Create an Object Table and a Dynamic Table to read and process the PDF metadata. To simplify this multi-step process, Hologres provides a stored procedure that has the following features:
- Creates an Object Table to retrieve the PDF metadata.
- Creates a destination Dynamic Table in incremental refresh mode to store the processed data. A vector index and a full-text index are set on this table. Auto-refresh is disabled, requiring you to refresh the table manually.
- During the refresh of the Dynamic Table, data is embedded and chunked by using the ai_embed and ai_chunk operators.
The following code is the stored procedure:
```
CALL create_rag_corpus_from_oss(
    oss_path => 'oss://xxxx/bs_challenge_financial_14b_dataset/pdf',
    oss_endpoint => 'oss-cn-hangzhou-internal.aliyuncs.com',
    oss_role_arn => 'acs:ram::186xxxx:role/xxxx',
    corpus_table => 'public.dt_bs_challenge_financial'
);
```
Refresh the result table.
Manually refresh the Object Table and Dynamic Table created by the stored procedure to complete the data transformation. This step is encapsulated in the stored procedure for PDF processing, which performs the following actions:
- Refreshes the Object Table to obtain the PDF metadata.
- Refreshes the Dynamic Table to perform PDF embedding and chunking.
The following code is used to call the stored procedure:
```
CALL refresh_rag_corpus_table(
    corpus_table => 'public.dt_bs_challenge_financial'
);
```

Retrieve PDF data.

After the data is processed, you can retrieve it by using methods such as vector search and full-text search based on your business scenario. For example, you can query a company's performance trend from its prospectus to determine whether the future outlook is pessimistic or optimistic, which can inform subsequent investment decisions.

Vector search

To simplify vector search, Hologres encapsulates processes like question embedding, prompt construction, and LLM-based answer generation into a vector search function. You can directly call this function to perform vector retrieval.

-- Vector-only retrieval + AI reranking
SELECT qa_vector_search_retrieval(
  question => 'By what percentage did the operating income and net profit of Goke Microelectronics increase year-over-year in 2014, 2015, and 2016 during the reporting period?',
  corpus_table => 'dt_bs_challenge_financial',
  prompt => 'Please analyze whether the following performance trend is pessimistic or optimistic and provide reasons: ${question}\n\n Reference information:\n\n ${context}'
)

The following result is returned:

qa_retrieval
---------
"Based on the information provided, the analysis of the performance trend of Goke Microelectronics leads to the following conclusions:

### I. Performance Trend Analysis: Pessimistic

#### 1. **Sluggish revenue growth**
- Revenue in 2014 increased by **15.13%** year-over-year, but in 2015, it **decreased by 5.21%**. Data for 2016 is not provided, but it is clear that the revenue growth trend saw a significant decline in 2015.
- The compound annual growth rate (CAGR) of revenue from 2012 to 2014 was only **4.47%**, indicating slow business expansion.

#### 2. **Continuous decline in net profit growth**
- Net profit grew by **5.43%** in 2014 but **decreased by 3.29%** in 2015.
- After non-recurring gains and losses are deducted, the net profit attributable to parent company shareholders decreased by **3.14%** in 2014 and further reduced by **5.60%** in 2015. This indicates that the profitability of the company's main business is continuously deteriorating.
- The CAGR of net profit after non-recurring gains and losses are deducted from 2012 to 2014 was **-4.38%**. This is much lower than the revenue growth, indicating that the main business is not profitable and growth relies on non-recurring gains and losses.

#### 3. **High proportion of non-recurring gains and losses**
- During the reporting period, non-recurring gains and losses accounted for a high proportion of net profit: **17.54%**, **10.25%**, and **8.06%** in 2014, 2013, and 2012, respectively. This shows that a portion of the company's profit comes from non-recurring factors such as policy support and government subsidies, rather than from the sustained growth of its core business.
- Relying on non-recurring gains and losses to maintain profit growth is not conducive to the company's long-term stable development.

#### 4. **Decline in return on equity (ROE)**
- The weighted average ROE increased from **18.10%** in 2014 to **24.82%** in 2015, and then to **28.23%** in 2016. Although the data seems to show growth, this indicator is calculated based on net profit after non-recurring gains and losses are deducted, and the net profit itself is declining. Therefore, this growth may be related to changes in the capital structure rather than a substantial improvement in profitability.

### II. Summary
1. **Sluggish main business growth**: Both revenue and net profit growth show a downward trend, especially the decline in net profit, which indicates weakening profitability.
2. **High reliance on non-recurring gains and losses**: Non-recurring gains and losses account for a high proportion of the company's profit, which indicates that the main business is not sufficiently profitable and the sustainability of the company's performance is questionable.
3. **Intense market competition**: The market for industrial PCs, displays, and power supplies that the company purchases is highly competitive, with stable prices and squeezed profit margins.
4. **Industry environment impact**: Fluctuations in stainless steel market prices and raw material prices may have a certain impact on the company's operating performance. Although the company has taken measures to reduce the impact, this still requires attention in the long run.

### III. Conclusion
Overall, the performance trend of Goke Microelectronics is **pessimistic**. The company's main business growth is sluggish, net profit is continuously declining, and it relies heavily on non-recurring gains and losses. The sustainability of its future profitability is questionable. The company needs to strengthen the competitiveness of its core business, optimize its cost structure, and improve the profitability of its main business to achieve long-term stable development."

Full-text search

For ease of use in full-text search, Hologres encapsulates processes such as question embedding, prompt construction, and answer generation by an LLM into a full-text search function. You can directly call this function to perform full-text retrieval:

-- Full-text search retrieval
SELECT qa_text_search_retrieval(
    question => 'By what percentage did the operating income and net profit of Goke Microelectronics increase year-over-year in 2014, 2015, and 2016 during the reporting period?',
    corpus_table => 'dt_bs_challenge_financial',
    prompt => 'Please analyze whether the following performance trend is pessimistic or optimistic and provide reasons: ${question}\n\n Reference information:\n\n ${context}'
);

The following result is returned:

qa_text_search_retrieval
----------------
"Based on the information provided, the overall performance trend of Goke Microelectronics in 2014, 2015, and 2016 is **pessimistic**. The specific reasons are as follows:

### 1. **Sluggish revenue growth**
- The revenue growth rate in 2014 was **15.13%**, but in 2015, it turned to **-5.21%**, which indicates negative growth.
- The compound annual growth rate (CAGR) of revenue from 2012 to 2014 was only **4.47%**, which indicates that the company's revenue growth was slow and its business development was not strong.
- The revenue forecast for the first half of 2015 was nearly the same as that for the same period in 2014, but the net profit for the first half of 2015 **slightly decreased** compared with the same period of the previous year, which indicates a decline in profitability.

### 2. **Poor growth in net profit and net profit excluding non-recurring items**
- The net profit growth rate in 2014 was **5.43%**, but it decreased to **-3.29%** in 2015, which means net profit declined.
- The growth rate of net profit after non-recurring gains and losses are deducted was **-3.14%** in 2014 and further decreased to **-5.60%** in 2015, which indicates that the profitability of the company's main business continued to decline.
- The CAGR of net profit excluding non-recurring items from 2012 to 2014 was **-4.38%**, which is significantly lower than the CAGR of revenue. This indicates that the company's profit quality is not high and its main business profitability is weak.

### 3. **Fluctuations in cash flow from operating activities**
- In 2014, the proportion of cash received from the sale of goods and provision of services to operating income decreased compared with the previous two years. This was mainly due to the **cross-period collection** of some revenue-recognized items, which indicates problems in the company's cash flow management.
- In 2013, the proportion of cash paid for the purchase of goods and services to operating costs was relatively high. This was mainly because **raw materials were purchased and production was completed** in that year, but some costs were not carried over until 2014, which resulted in a lower proportion in 2014. This reflects that the company's procurement and production pace was not stable.

### 4. **Investment and profitability indicators**
- The weighted average return on equity (ROE) was **18.10%** in 2014, rose to **24.82%** in 2015, and further increased to **28.23%** in 2016. Although there was an improvement, the increase in ROE may have been mainly attributed to **financial leverage** rather than an improvement in core business profitability.
- Considering the continuous decline in net profit and net profit excluding non-recurring items, the increase in ROE does not fully reflect an improvement in the company's operational quality.

### 5. **Performance forecast for the first half of 2015**
- The estimated operating revenue for the first half of 2015 is **CNY 85.05 million to CNY 103.95 million**, which is similar to the **CNY 101.2735 million** for the same period in 2014. However, the estimated net profit is **CNY 23.40 million to CNY 28.60 million**, which is lower than the **CNY 29.1266 million** for the same period in 2014. This indicates a decline in the company's profitability.

### Summary
In summary, the performance trend of Goke Microelectronics from 2014 to 2016 is **pessimistic**. Although ROE has improved, the sluggish revenue growth, continuous decline in net profit and net profit excluding non-recurring items, and large fluctuations in cash flow from operational activities indicate that the company's main business profitability is weak and its operational quality needs to be improved."

Hybrid search

For hybrid search scenarios that combine vector and full-text search with ranking, Hologres provides a hybrid search function with ranking. This function has the following features:

Retrieves the top 20 answers based on a vector search for the question.
Retrieves the top 20 answers based on a full-text search for the question.
Uses the ai_rank operator to rerank the combined results from both searches.
Uses the ai_gen operator and an LLM to generate a final answer based on a prompt and the top-ranked results.

-- Hybrid search (full-text and vector) + AI reranking
SELECT qa_hybrid_retrieval(
    question => 'By what percentage did the operating income and net profit of Goke Microelectronics increase year-over-year in 2014, 2015, and 2016 during the reporting period?',
    corpus_table => 'dt_bs_challenge_financial',
    prompt => 'Please analyze whether the following performance trend is pessimistic or optimistic and provide reasons: ${question}\n\n Reference information:\n\n ${context}'
);

The following result is returned:

qa_hybrid_retrieval
---
"Based on the information provided, we can analyze the performance trend of Goke Microelectronics and determine whether it is pessimistic or optimistic as follows:

---

### I. **Operating Revenue Trend Analysis**
1. **2012-2014 Compound Annual Growth Rate (CAGR)**:
   - The CAGR of operating income was **4.47%**, which indicates that the company's revenue growth was relatively stable.
   - In 2014, operating income was **CNY 181.5406 million**, an increase of **15.13%** from 2013.
   - In 2015, operating income **decreased by 5.21%** year-over-year, which indicates negative growth.

2. **Conclusion**:
   - Revenue growth recovered in 2014 but saw a significant decline in 2015, which indicates that the company's business expansion has encountered some resistance.

---

### II. **Net Profit Trend Analysis**
1. **2012-2014 CAGR**:
   - The CAGR of net profit after non-recurring gains and losses are deducted was **-4.38%**, which is lower than the CAGR of operating income. This indicates a decline in the company's profitability.
   - In 2014, the net profit excluding non-recurring items was **CNY 42,731,071.18**, a decrease of **3.14%** from 2013.
   - In 2015, the net profit excluding non-recurring items further **decreased by 5.60%** year-over-year.

2. **Impact of Non-recurring Gains and Losses**:
   - The proportion of non-recurring gains and losses to net profit was **17.54%**, **10.25%**, and **8.06%** in 2014, 2013, and 2012, respectively, which indicates an upward trend.
   - The increase in non-recurring gains and losses mainly came from government subsidies and income from wealth management products, rather than sustained growth from the main business.

3. **Conclusion**:
   - The continuous two-year decline in net profit excluding non-recurring items indicates that the profitability of the company's main business is weakening, and performance growth relies on non-recurring gains and losses. This is a worrying signal.

---

### III. **Cash Flow and Operational Stability**
1. **Cash Flow from Operating Activities**:
   - In 2014, operating income was **CNY 181.5406 million**, but the cash received from the sale of goods and provision of services was not clearly stated, which makes it impossible to determine whether the cash flow is healthy.
   - During the reporting period, the company's bank deposits were **CNY 130.6338 million**, **CNY 41.5254 million**, and **CNY 98.6461 million**, respectively. The liquidity fluctuated significantly, but the main customers, suppliers, and business model remained stable.

2. **Conclusion**:
   - Although the company's cash flow fluctuates, its stable customer and supplier base and business model provide a certain guarantee for future development.

---

### IV. **Performance Forecast for the First Half of 2015**
- From January to June 2015, the estimated operating income is **CNY 85.05 million to CNY 103.95 million**, a significant increase from the **CNY 46.4119 million** for the same period in 2014.
- However, from January to March 2015, the net profit decreased by **48.26%** year-over-year, mainly because the projects with recognized revenue had lower gross profit margins.

---

### V. **Comprehensive Analysis and Judgment**
1. **Optimistic Factors**:
   - Operating income grew rapidly in 2014, reaching **15.13%**.
   - Operating income is expected to grow significantly in the first half of 2015, which indicates that the company may be gradually recovering.
   - The stability of major customers, suppliers, and the business model provides a good operational foundation for the company.

2. **Pessimistic Factors**:
   - In 2015, operating revenue **decreased by 5.21%** year-over-year, and net profit also declined.
   - The continuous two-year decline in net profit excluding non-recurring items indicates insufficient profitability of the main business.
   - The proportion of non-recurring gains and losses is increasing, and performance growth relies on government subsidies and income from wealth management products, which indicates a lack of internal growth momentum.
   - The net profit from January to March 2015 dropped sharply by **48.26%**, which indicates significant short-term performance fluctuations.

---

### **Final Conclusion: Overall Trend is Pessimistic**
- Although the company's operating income recovered in 2014 and is expected to grow in the first half of 2015, the **continuous decline in net profit excluding non-recurring items**, **reliance on non-recurring gains and losses for net profit growth**, and **large short-term performance fluctuations** indicate that the company's current performance growth lacks sustainability and stability.
- Therefore, from a long-term perspective, the company's performance trend is **pessimistic**. You need to pay attention to the improvement of its main business profitability and its reliance on non-recurring gains and losses.

---

### **Recommendations**
1. Pay attention to whether the profitability of the company's main business can be improved in the future.
2. Reduce reliance on non-recurring gains and losses and enhance internal growth momentum.
3. Stabilize customer and supplier relationships, optimize the business structure, and increase gross profit margins."

Hybrid search with RRF

After retrieving results using vector and full-text search, you can rank them using Reciprocal Rank Fusion (RRF). For ease of use, Hologres provides a hybrid search function with RRF ranking (see the appendix below for the detailed definition). This function performs the following actions:

Retrieves the top 20 answers based on a vector search for the question.
Retrieves the top 20 answers based on a full-text search for the question.
Calculates RRF scores for the results from both searches and returns the top N results.
Uses the ai_gen operator and an LLM to generate a final answer based on a prompt and the top-ranked results.

-- Hybrid search (full-text and vector) + RRF reranking
SELECT qa_hybrid_retrieval_rrf(
    question => 'By what percentage did the operating income and net profit of Goke Microelectronics increase year-over-year in 2014, 2015, and 2016 during the reporting period?',
    corpus_table => 'dt_bs_challenge_financial',
    prompt => 'Please analyze whether the following performance trend is pessimistic or optimistic and provide reasons: ${question}\n\n Reference information:\n\n ${context}'
);

The following result is returned:

qa_hybrid_retrieval_rrf
------------------
"Based on the information provided, the analysis of the performance trend of Goke Microelectronics leads to the following conclusions:

### **Performance Trend Judgment: Pessimistic**
#### **The reasons are as follows:**

1. **Net profit growth is lower than revenue growth:**
   - The provided information indicates that the company's **compound annual growth rate (CAGR) of operating income from 2012 to 2014 was 4.47%**, which suggests that the overall business growth was relatively stable.
   - However, the **CAGR of net profit attributable to parent company shareholders after non-recurring gains and losses are deducted was -4.38%**, which is significantly lower than the revenue growth rate. This indicates that while revenue was growing, the company's profitability did not improve in sync and even declined. This could be due to factors such as rising costs, declining gross profit margins, or a reduction in non-recurring gains and losses.

2. **Significant fluctuations in net profit:**
   - The net profit from January to March 2015 decreased by 48.26% year-over-year. The main reason was the lower gross profit margin of the projects with recognized revenue (for example, the Wuxi Metro Line 1 project was mainly based on module outsourcing). This indicates that the company's short-term performance is susceptible to changes in its business structure, which shows a certain degree of instability.

3. **Decline in gross profit margin and profitability:**
   - It was mentioned that the "contribution of main business profit to the company's net profit" slightly decreased in 2014 compared with 2013, and 2013 was lower than 2012. This suggests that the profitability of the company's core business may be weakening, possibly due to a combination of increased market competition, rising costs, or changes in the product structure.

4. **Projected profit decline in the first half of 2015:**
   - The estimated operating revenue for the first half of 2015 is from CNY 85.05 million to CNY 103.95 million, which is similar to that in the same period of 2014. However, the estimated net profit is from CNY 23.40 million to CNY 28.60 million, which is lower than the CNY 29.1266 million in the same period of 2014. This indicates that the company's profitability is further declining and it may be facing certain operational pressures.

5. **Sluggish business growth:**
   - Although revenue growth is stable, the decline in net profit indicates that the quality of the company's business growth is not high and has not been effectively converted into profit. This may affect investors' confidence in the company's future development.

### **Summary:**
The overall performance trend of Goke Microelectronics is **pessimistic**. Although operating revenue has maintained stable growth, the growth of net profit has significantly lagged or even shown negative growth. This indicates that the company's profitability is declining, the quality of its business development is not high, and there is a risk of short-term performance fluctuations. If the company cannot effectively increase its gross profit margin, control costs, or optimize its product structure, its future performance may continue to be under pressure."

Appendix: Procedure definitions

The definitions of the stored procedures used in this topic are provided for your reference.

Note

The following stored procedures and functions are provided as examples. You may need to modify them for your specific environment.

PDF processing procedure

Create an Object Table and a Dynamic Table

CREATE OR REPLACE PROCEDURE create_rag_corpus_from_oss(
    oss_path TEXT,
    oss_endpoint TEXT,
    oss_role_arn TEXT,
    corpus_table TEXT,
    embedding_model TEXT DEFAULT NULL,
    parse_document_model TEXT DEFAULT NULL,
    chunk_model TEXT DEFAULT NULL,
    chunk_size INT DEFAULT 300,
    chunk_overlap INT DEFAULT 50,
    overwrite BOOLEAN DEFAULT FALSE
)
AS $$
DECLARE
    corpus_schema TEXT;
    corpus_name TEXT;
    obj_table_name TEXT;
    full_corpus_ident TEXT;
    full_obj_ident TEXT;
    embed_expr TEXT;
    chunk_expr TEXT;
    parse_expr TEXT;
    embedding_dims INT;
BEGIN
    -- 1. Split the schema and table names.
    IF position('.' in corpus_table) > 0 THEN
        corpus_schema := split_part(corpus_table, '.', 1);
        corpus_name   := split_part(corpus_table, '.', 2);
    ELSE
        corpus_schema := 'public';
        corpus_name   := corpus_table;
    END IF;

    obj_table_name := corpus_name || '_obj_table';

    full_corpus_ident := format('%I.%I', corpus_schema, corpus_name);
    full_obj_ident    := format('%I.%I', corpus_schema, obj_table_name);
    
    -- 2. If overwrite is set to true, drop the existing tables and indexes first.
    IF overwrite THEN
        DECLARE
            dyn_table_exists BOOLEAN;
            rec RECORD;
        BEGIN
            -- Check whether the dynamic table exists.
            SELECT EXISTS (
                SELECT 1
                FROM pg_class c
                JOIN pg_namespace n ON n.oid = c.relnamespace
                WHERE c.relname = corpus_name
                AND n.nspname = corpus_schema
            )
            INTO dyn_table_exists;

            IF dyn_table_exists THEN
                -- 2.1 Disable auto-refresh for the dynamic table.
                -- RAISE NOTICE 'Disabling auto refresh for %', full_corpus_ident;
                -- EXECUTE format('ALTER TABLE IF EXISTS %s SET (auto_refresh_enable=false)', full_corpus_ident);

                -- 2.2 Find and cancel RUNNING refresh jobs.
                FOR rec IN
                    EXECUTE format(
                        $f$
                        SELECT query_job_id
                            FROM hologres.hg_dynamic_table_refresh_log(%L)
                            WHERE status = 'RUNNING';
                        $f$,
                        corpus_table
                    )
                LOOP
                    RAISE NOTICE 'Found running refresh job: %', rec.query_job_id;
                    IF hologres.hg_internal_cancel_query_job(rec.query_job_id::bigint) THEN
                        RAISE NOTICE 'Job % canceled successfully.', rec.query_job_id;
                    ELSE
                        RAISE NOTICE 'Failed to cancel job %.', rec.query_job_id;
                    END IF;
                END LOOP;

                -- 2.3 Drop the Dynamic Table.
                EXECUTE format('DROP TABLE IF EXISTS %s;', full_corpus_ident);
            ELSE
                RAISE NOTICE 'Dynamic table % does not exist, skip cancel job and drop.', full_corpus_ident;
            END IF;

            -- 2.4 Drop the Object Table regardless.
            EXECUTE format('DROP OBJECT TABLE IF EXISTS %s;', full_obj_ident);
        END;
    END IF;

    -- 3. Create an Object Table.
    RAISE NOTICE 'Creating object table: %', obj_table_name;
    EXECUTE format(
        $f$
        CREATE OBJECT TABLE %s
        WITH (
            path = %L,
            oss_endpoint = %L,
            role_arn = %L
        );
        $f$,
        full_obj_ident,
        oss_path,
        oss_endpoint,
        oss_role_arn
    );

    COMMIT;

    -- 4. Refresh the Object Table.
    RAISE NOTICE 'Refreshing object table: %', obj_table_name;
    EXECUTE format('REFRESH OBJECT TABLE %s;', full_obj_ident);

    COMMIT;

    -- 5. Select a document parsing model.
    IF parse_document_model IS NULL OR length(trim(parse_document_model)) = 0 THEN
        parse_expr := 'ai_parse_document(file, ''auto'', ''markdown'')';
    ELSE
        parse_expr := format(
            'ai_parse_document(%L, file, ''auto'', ''markdown'')',
            parse_document_model
        );
    END IF;

    -- 6. Select a chunking model.
    IF chunk_model IS NULL OR length(trim(chunk_model)) = 0 THEN
        chunk_expr := format('ai_chunk(doc, %s, %s)', chunk_size, chunk_overlap);
    ELSE
        chunk_expr := format(
            'ai_chunk(%L, doc, %s, %s)',
            chunk_model,
            chunk_size,
            chunk_overlap
        );
    END IF;

    -- 7. Select an embedding model.
    IF embedding_model IS NULL OR length(trim(embedding_model)) = 0 THEN
        embed_expr := 'ai_embed(chunk)';

        EXECUTE 'SELECT array_length(ai_embed(''dummy''), 1)'
        INTO embedding_dims;
    ELSE
        embed_expr := format('ai_embed(%L, chunk)', embedding_model);

        EXECUTE format(
            'SELECT array_length(ai_embed(%L, ''dummy''), 1)',
            embedding_model
        )
        INTO embedding_dims;
    END IF;

    RAISE NOTICE 'Embedding dimension: %', embedding_dims;

    -- 8. Create a Dynamic Table for RAG output.
    RAISE NOTICE 'Creating dynamic table: %', corpus_name;
    EXECUTE format(
        $f$
        CREATE DYNAMIC TABLE %s(
            CHECK(array_ndims(embedding_vector) = 1 AND array_length(embedding_vector, 1) = %s)
        )
        WITH (
            vectors = '{
              "embedding_vector": {
                "algorithm": "HGraph",
                "distance_method": "Cosine",
                "builder_params": {
                "base_quantization_type": "sq8_uniform",
                "max_degree": 64,
                "ef_construction": 400,
                "precise_quantization_type": "fp32",
                "use_reorder": true
                }
              }
            }',
            auto_refresh_mode = 'incremental',
            freshness = '5 minutes',
            auto_refresh_enable = 'false'
        ) AS
        WITH parsed_doc AS (
            SELECT object_uri,
                   etag,
                   %s AS doc
              FROM %s
        ),
        chunked_doc AS (
            SELECT object_uri,
                   etag,
                   unnest(%s) AS chunk
              FROM parsed_doc
        )
        SELECT
            object_uri,
            etag,
            chunk,
            %s AS embedding_vector
          FROM chunked_doc;
        $f$,
        full_corpus_ident,
        embedding_dims,
        parse_expr,
        full_obj_ident,
        chunk_expr,
        embed_expr
    );
    COMMIT;

    -- 9. Create a full-text index. The index name is table_name || '_fulltext_idx'.
    EXECUTE format(
        'CREATE INDEX %I ON %s USING FULLTEXT (chunk);',
        corpus_name || '_fulltext_idx',
        full_corpus_ident
    );

    RAISE NOTICE '';
    RAISE NOTICE 'RAG corpus created successfully for table: %', corpus_table;
    RAISE NOTICE '    Vector index: %.embedding_vector', corpus_table;
    RAISE NOTICE '    Full-text index: %.chunk', corpus_table;
END;
$$ LANGUAGE plpgsql;

Stored procedure to refresh the Object Table and Dynamic Table

CREATE OR REPLACE PROCEDURE refresh_rag_corpus_table(
    corpus_table TEXT
)
AS $$
DECLARE
    corpus_schema TEXT;
    corpus_name   TEXT;
    obj_table_name TEXT;
    full_corpus_ident TEXT;
    full_obj_ident    TEXT;
BEGIN
    -- 1. Parse the schema and table names.
    IF position('.' in corpus_table) > 0 THEN
        corpus_schema := split_part(corpus_table, '.', 1);
        corpus_name   := split_part(corpus_table, '.', 2);
    ELSE
        corpus_schema := 'public';
        corpus_name   := corpus_table;
    END IF;

    obj_table_name := corpus_name || '_obj_table';

    full_corpus_ident := format('%I.%I', corpus_schema, corpus_name);
    full_obj_ident    := format('%I.%I', corpus_schema, obj_table_name);

    -- 2. Refresh the Object Table.
    RAISE NOTICE 'Refreshing Object Table: %', obj_table_name;
    EXECUTE format('REFRESH OBJECT TABLE %s;', full_obj_ident);

    -- 3. Refresh the Dynamic Table.
    RAISE NOTICE 'Refreshing Dynamic Table: %', corpus_name;
    EXECUTE format('REFRESH TABLE %s;', full_corpus_ident);

    RAISE NOTICE 'Refresh complete for corpus table %', corpus_table;
END;
$$ LANGUAGE plpgsql;

Stored procedure to drop the Object Table and Dynamic Table

CREATE OR REPLACE PROCEDURE drop_rag_corpus_table(
    corpus_table TEXT
)
AS $$
DECLARE
    corpus_schema TEXT;
    corpus_name   TEXT;
    obj_table_name TEXT;
    full_corpus_ident TEXT;
    full_obj_ident    TEXT;
    rec RECORD;
BEGIN
    -- 1. Parse the schema and table names.
    IF position('.' in corpus_table) > 0 THEN
        corpus_schema := split_part(corpus_table, '.', 1);
        corpus_name   := split_part(corpus_table, '.', 2);
    ELSE
        corpus_schema := 'public';
        corpus_name   := corpus_table;
    END IF;

    obj_table_name := corpus_name || '_obj_table';

    full_corpus_ident := format('%I.%I', corpus_schema, corpus_name);
    full_obj_ident    := format('%I.%I', corpus_schema, obj_table_name);

    -- 2. Drop the tables.
    -- 2.1 Disable auto-refresh for the dynamic table.
    -- RAISE NOTICE 'Disabling auto refresh for %', full_corpus_ident;
    -- EXECUTE format('ALTER TABLE IF EXISTS %s SET (auto_refresh_enable=false)', full_corpus_ident);

    -- 2.2 Find and cancel RUNNING refresh jobs.
    FOR rec IN
        EXECUTE format(
            $f$
            SELECT query_job_id
                FROM hologres.hg_dynamic_table_refresh_log(%L)
                WHERE status = 'RUNNING';
            $f$,
            corpus_table
        )
    LOOP
        RAISE NOTICE 'Found running refresh job: %', rec.query_job_id;
        IF hologres.hg_internal_cancel_query_job(rec.query_job_id::bigint) THEN
            RAISE NOTICE 'Job % canceled successfully.', rec.query_job_id;
        ELSE
            RAISE NOTICE 'Failed to cancel job %.', rec.query_job_id;
        END IF;
    END LOOP;

    -- 2.3 Drop the Dynamic Table.
    RAISE NOTICE 'Dropping Dynamic Table: %', corpus_name;
    EXECUTE format('DROP TABLE IF EXISTS %s;', full_corpus_ident);

    -- 2.4 Drop the Object Table.
    RAISE NOTICE 'Dropping Object Table: %', obj_table_name;
    EXECUTE format('DROP OBJECT TABLE IF EXISTS %s;', full_obj_ident);

    RAISE NOTICE 'Drop complete for corpus: %', corpus_table;
END;
$$ LANGUAGE plpgsql;

Vector search function

-- RAG Q&A with vector-only retrieval
CREATE OR REPLACE FUNCTION qa_vector_search_retrieval(
    question TEXT,
    corpus_table TEXT,
    embedding_model TEXT DEFAULT NULL,
    llm_model TEXT DEFAULT NULL,
    ranking_model TEXT DEFAULT NULL,
    prompt TEXT DEFAULT 'Please answer the following question in ${language} based on the reference information.\n\n Question: ${question}\n\n Reference information:\n\n ${context}',
    language TEXT DEFAULT 'Chinese',
    vector_recall_count INT DEFAULT 20,
    rerank_recall_count INT DEFAULT 5,
    vector_col TEXT DEFAULT 'embedding_vector'
)
RETURNS TEXT AS
$$
DECLARE
    final_answer TEXT;
    sql TEXT;
    embedding_expr TEXT;
    ai_rank_expr TEXT;
    ai_gen_expr TEXT;
    embedding_model_valid BOOLEAN;
    llm_model_valid BOOLEAN;
    ranking_model_valid BOOLEAN;
BEGIN
    embedding_model_valid := (embedding_model IS NOT NULL AND trim(embedding_model) != '');
    llm_model_valid := (llm_model IS NOT NULL AND trim(llm_model) != '');
    ranking_model_valid := (ranking_model IS NOT NULL AND trim(ranking_model) != '');

    IF embedding_model_valid THEN
        embedding_expr := 'ai_embed(' || quote_literal(embedding_model) || ', ' || quote_literal(question) || ')';
    ELSE
        embedding_expr := 'ai_embed(' || quote_literal(question) || ')';
    END IF;

    IF ranking_model_valid THEN
        ai_rank_expr := 'ai_rank(' || quote_literal(ranking_model) || ', ' || quote_literal(question) || ', chunk)';
    ELSE
        ai_rank_expr := 'ai_rank(' || quote_literal(question) || ', chunk)';
    END IF;

    IF llm_model_valid THEN
        ai_gen_expr := 'ai_gen(' || quote_literal(llm_model) ||
            ', replace(replace(replace(' || quote_literal(prompt) ||
               ', ''${question}'', ' || quote_literal(question) || '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || ') )';
    ELSE
        ai_gen_expr := 'ai_gen(replace(replace(replace(' || quote_literal(prompt) ||
               ', ''${question}'', ' || quote_literal(question) || '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || '))';
    END IF;

    sql := '
      WITH
        embedding_recall AS (
            SELECT
              chunk,
              approx_cosine_distance(' || vector_col || ', ' || embedding_expr || ') AS distance
            FROM
              ' || corpus_table || '
            ORDER BY
              distance DESC
            LIMIT ' || vector_recall_count || '
        ),
        rerank AS (
            SELECT
              chunk,
              ' || ai_rank_expr || ' AS score
            FROM
              embedding_recall
            ORDER BY
              score DESC
            LIMIT ' || rerank_recall_count || '
        ),
        concat_top_chunks AS (
            SELECT string_agg(chunk, E''\n\n----\n\n'') AS merged_chunks FROM rerank
        )
      SELECT ' || ai_gen_expr || '
      FROM concat_top_chunks;
    ';

    EXECUTE sql INTO final_answer;
    RETURN final_answer;
END;
$$ LANGUAGE plpgsql;

Full-text search function

CREATE OR REPLACE FUNCTION qa_text_search_retrieval(
    question TEXT,
    corpus_table TEXT,
    llm_model TEXT DEFAULT NULL,
    ranking_model TEXT DEFAULT NULL,
    prompt TEXT DEFAULT 'Please answer the following question in ${language} based on the reference information.\n\n Question: ${question}\n\n Reference information:\n\n ${context}',
    language TEXT DEFAULT 'Chinese',
    text_search_recall_count INT DEFAULT 20,
    rerank_recall_count INT DEFAULT 5,
    text_search_col TEXT DEFAULT 'chunk'
)
RETURNS TEXT AS
$$
DECLARE
    final_answer TEXT;
    sql TEXT;
    ai_rank_expr TEXT;
    ai_gen_expr TEXT;
    llm_model_valid BOOLEAN;
    ranking_model_valid BOOLEAN;
BEGIN
    llm_model_valid     := (llm_model IS NOT NULL AND trim(llm_model) != '');
    ranking_model_valid := (ranking_model IS NOT NULL AND trim(ranking_model) != '');

    IF ranking_model_valid THEN
        ai_rank_expr := 'ai_rank(' || quote_literal(ranking_model) || ', ' || quote_literal(question) || ', chunk)';
    ELSE
        ai_rank_expr := 'ai_rank(' || quote_literal(question) || ', chunk)';
    END IF;

    IF llm_model_valid THEN
        ai_gen_expr := 'ai_gen(' || quote_literal(llm_model) ||
            ', replace(replace(replace(' || quote_literal(prompt) ||
               ', ''${question}'', ' || quote_literal(question) ||
               '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || ') )';
    ELSE
        ai_gen_expr := 'ai_gen(replace(replace(replace(' || quote_literal(prompt) ||
               ', ''${question}'', ' || quote_literal(question) ||
               '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || '))';
    END IF;

    sql := '
      WITH
        text_search_recall AS (
            SELECT
              chunk
            FROM
              ' || corpus_table || '
            ORDER BY
              text_search(' || text_search_col || ', ' || quote_literal(question) || ') DESC
            LIMIT ' || text_search_recall_count || '
        ),
        rerank AS (
            SELECT
              chunk,
              ' || ai_rank_expr || ' AS score
            FROM
              text_search_recall
            ORDER BY
              score DESC
            LIMIT ' || rerank_recall_count || '
        ),
        concat_top_chunks AS (
            SELECT string_agg(chunk, E''\n\n----\n\n'') AS merged_chunks FROM rerank
        )
      SELECT ' || ai_gen_expr || '
      FROM concat_top_chunks;
    ';

    EXECUTE sql INTO final_answer;
    RETURN final_answer;
END;
$$ LANGUAGE plpgsql;

Hybrid search with ranking

CREATE OR REPLACE FUNCTION qa_hybrid_retrieval(
    question TEXT,
    corpus_table TEXT,
    embedding_model TEXT DEFAULT NULL,
    llm_model TEXT DEFAULT NULL,
    ranking_model TEXT DEFAULT NULL,
    prompt TEXT DEFAULT 'Please answer the following question in ${language} based on the reference information.\n\n Question: ${question}\n\n Reference information:\n\n ${context}',
    language TEXT DEFAULT 'Chinese',
    text_search_recall_count INT DEFAULT 20,
    vector_recall_count INT DEFAULT 20,
    rerank_recall_count INT DEFAULT 5,
    vector_col TEXT DEFAULT 'embedding_vector',
    text_search_col TEXT DEFAULT 'chunk'
)
RETURNS TEXT AS
$$
DECLARE
    final_answer TEXT;
    sql TEXT;
    embedding_expr TEXT;
    ai_rank_expr TEXT;
    ai_gen_expr TEXT;
    embedding_model_valid BOOLEAN;
    llm_model_valid BOOLEAN;
    ranking_model_valid BOOLEAN;
BEGIN
    embedding_model_valid := (embedding_model IS NOT NULL AND trim(embedding_model) != '');
    llm_model_valid       := (llm_model IS NOT NULL AND trim(llm_model) != '');
    ranking_model_valid   := (ranking_model IS NOT NULL AND trim(ranking_model) != '');

    IF embedding_model_valid THEN
        embedding_expr := 'ai_embed(' || quote_literal(embedding_model) || ', ' || quote_literal(question) || ')';
    ELSE
        embedding_expr := 'ai_embed(' || quote_literal(question) || ')';
    END IF;

    IF ranking_model_valid THEN
        ai_rank_expr := 'ai_rank(' || quote_literal(ranking_model) || ', ' || quote_literal(question) || ', chunk)';
    ELSE
        ai_rank_expr := 'ai_rank(' || quote_literal(question) || ', chunk)';
    END IF;

    IF llm_model_valid THEN
        ai_gen_expr := 'ai_gen(' || quote_literal(llm_model) ||
            ', replace(replace(replace(' || quote_literal(prompt) ||
               ', ''${question}'', ' || quote_literal(question) || '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || ') )';
    ELSE
        ai_gen_expr := 'ai_gen(replace(replace(replace(' || quote_literal(prompt) ||
               ', ''${question}'', ' || quote_literal(question) || '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || '))';
    END IF;

    sql := '
      WITH
        embedding_recall AS (
            SELECT
              chunk
            FROM
              ' || corpus_table || '
            ORDER BY
              approx_cosine_distance(' || vector_col || ', ' || embedding_expr || ') DESC
            LIMIT ' || vector_recall_count || '
        ),
        text_search_recall AS (
            SELECT
              chunk
            FROM
              ' || corpus_table || '
            ORDER BY
              text_search(' || text_search_col || ', ' || quote_literal(question) || ') DESC
            LIMIT ' || text_search_recall_count || '
        ),
        union_recall AS (
            SELECT chunk FROM embedding_recall
            UNION
            SELECT chunk FROM text_search_recall
        ),
        rerank AS (
            SELECT
              chunk,
              ' || ai_rank_expr || ' AS score
            FROM
              union_recall
            ORDER BY
              score DESC
            LIMIT ' || rerank_recall_count || '
        ),
        concat_top_chunks AS (
            SELECT string_agg(chunk, E''\n\n----\n\n'') AS merged_chunks FROM rerank
        )
      SELECT ' || ai_gen_expr || '
      FROM concat_top_chunks;
    ';

    EXECUTE sql INTO final_answer;
    RETURN final_answer;
END;
$$ LANGUAGE plpgsql;

Hybrid search with RRF

CREATE OR REPLACE FUNCTION qa_hybrid_retrieval_rrf(
    question TEXT,
    corpus_table TEXT,
    embedding_model TEXT DEFAULT NULL,
    llm_model TEXT DEFAULT NULL,
    ranking_model TEXT DEFAULT NULL,
    prompt TEXT DEFAULT 'Please answer the following question in ${language} based on the reference information.\n\n Question: ${question}\n\n Reference information:\n\n ${context}',
    language TEXT DEFAULT 'Chinese',
    text_search_recall_count INT DEFAULT 20,
    vector_recall_count INT DEFAULT 20,
    rerank_recall_count INT DEFAULT 5,
    rrf_k INT DEFAULT 60,
    vector_col TEXT DEFAULT 'embedding_vector',
    text_search_col TEXT DEFAULT 'chunk'
)
RETURNS TEXT AS
$$
DECLARE
    final_answer TEXT;
    sql TEXT;
    embedding_expr TEXT;
    ai_rank_expr TEXT;
    ai_gen_expr TEXT;
    embedding_model_valid BOOLEAN;
    llm_model_valid BOOLEAN;
    ranking_model_valid BOOLEAN;
BEGIN
    embedding_model_valid := (embedding_model IS NOT NULL AND trim(embedding_model) <> '');
    llm_model_valid       := (llm_model IS NOT NULL AND trim(llm_model) <> '');
    ranking_model_valid   := (ranking_model IS NOT NULL AND trim(ranking_model) <> '');

    IF embedding_model_valid THEN
        embedding_expr := 'ai_embed(' || quote_literal(embedding_model) || ', ' || quote_literal(question) || ')';
    ELSE
        embedding_expr := 'ai_embed(' || quote_literal(question) || ')';
    END IF;

    IF ranking_model_valid THEN
        ai_rank_expr := 'ai_rank(' || quote_literal(ranking_model) || ', ' || quote_literal(question) || ', chunk)';
    ELSE
        ai_rank_expr := 'ai_rank(' || quote_literal(question) || ', chunk)';
    END IF;

    IF llm_model_valid THEN
        ai_gen_expr := 'ai_gen(' || quote_literal(llm_model) ||
            ', replace(replace(replace(' || quote_literal(prompt) ||
                ', ''${question}'', ' || quote_literal(question) || '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || ') )';
    ELSE
        ai_gen_expr := 'ai_gen(replace(replace(replace(' || quote_literal(prompt) ||
                ', ''${question}'', ' || quote_literal(question) || '), ''${context}'', merged_chunks), ''${language}'', ' || quote_literal(language) || '))';
    END IF;

    sql := '
      WITH embedding_recall AS (
        SELECT
          chunk,
          vec_score,
          ROW_NUMBER() OVER (ORDER BY vec_score DESC) AS rank_vec
        FROM (
          SELECT
            chunk,
            approx_cosine_distance(' || vector_col || ', ' || embedding_expr || ') AS vec_score
          FROM
            ' || corpus_table || '
        ) t
        ORDER BY vec_score DESC
        LIMIT ' || vector_recall_count || '
      ),
      text_search_recall AS (
        SELECT
          chunk,
          text_score,
          ROW_NUMBER() OVER (ORDER BY text_score DESC) AS rank_text
        FROM (
          SELECT
            chunk,
            text_search(' || text_search_col || ', ' || quote_literal(question) || ') AS text_score
          FROM
            ' || corpus_table || '
        ) ts
        WHERE text_score > 0
        ORDER BY text_score DESC
        LIMIT ' || text_search_recall_count || '
      ),
      rrf_scores AS (
        SELECT
          chunk,
          SUM(1.0 / (' || rrf_k || ' + rank_val)) AS rrf_score
        FROM (
          SELECT chunk, rank_vec AS rank_val FROM embedding_recall
          UNION ALL
          SELECT chunk, rank_text AS rank_val FROM text_search_recall
        ) sub
        GROUP BY chunk
      ),
      top_chunks AS (
        SELECT chunk
        FROM rrf_scores
        ORDER BY rrf_score DESC
        LIMIT ' || rerank_recall_count || '
      ),
      concat_top_chunks AS (
        SELECT string_agg(chunk, E''\n\n----\n\n'') AS merged_chunks FROM top_chunks
      )
      SELECT ' || ai_gen_expr || '
      FROM concat_top_chunks;
    ';

    EXECUTE sql INTO final_answer;
    RETURN final_answer;
END;
$$ LANGUAGE plpgsql;