RAG Functions - SQL Reference
Built-in Retrieval-Augmented Generation (RAG) directly in SQL queries.
Overview
SynapCores provides native RAG functions that allow you to ask natural language questions about your data and receive AI-generated insights—all within SQL.
Syntax
RAG Function
RAG(question TEXT, tables ARRAY, [options JSON]) -> TEXT
RAG_FROM_QUERY Function
RAG_FROM_QUERY(question TEXT, query TEXT, [options JSON]) -> TEXT
Basic Examples
Simple Query
SELECT RAG('What are the top products?', ARRAY['products']);
Multiple Tables
SELECT RAG(
'Show customer purchase patterns',
ARRAY['customers', 'orders', 'order_items']
);
Custom Options
SELECT RAG(
'Analyze sales trends',
ARRAY['sales'],
'{"temperature": 0.3, "max_tokens": 500}'::JSON
);
Query Results
SELECT RAG_FROM_QUERY(
'Summarize these statistics',
'SELECT region, SUM(revenue) FROM sales GROUP BY region'
);
Options Reference
| Option | Type | Default | Description |
|---|---|---|---|
system_prompt |
string | (default) | Custom system prompt |
max_tokens |
integer | 500 | Max response tokens |
temperature |
float | 0.7 | LLM creativity (0.0-1.0) |
sample_size |
integer | 50 | Rows per table to sample |
model |
string | (provider default) | LLM model name |
include_schema |
boolean | true | Include table schemas |
semantic_search |
boolean | false | Use vector embeddings |
semantic_threshold |
float | 0.7 | Similarity threshold |
Industry Use Cases
Finance - Fraud Detection
SELECT RAG(
'Identify suspicious transaction patterns',
ARRAY['transactions', 'accounts'],
'{"temperature": 0.1, "sample_size": 200}'::JSON
);
Healthcare - Patient Risk Assessment
SELECT RAG(
'What are the key risk factors for this patient?',
ARRAY['patient_vitals', 'medications', 'lab_results'],
'{"system_prompt": "You are a clinical decision support system"}'::JSON
);
E-Commerce - Product Recommendations
SELECT RAG(
'Which products should we recommend to high-value customers?',
ARRAY['customers', 'purchases', 'products']
);
Logistics - Route Optimization
SELECT RAG(
'Which routes are experiencing delays?',
ARRAY['shipments', 'routes', 'carriers'],
'{"sample_size": 150}'::JSON
);
Common Patterns
Business Analysis
SELECT RAG(
'Identify high-value customers at risk of churn',
ARRAY['customers', 'subscriptions', 'support_tickets'],
'{
"system_prompt": "You are a customer success analyst",
"temperature": 0.2
}'::JSON
);
Data Quality Check
SELECT RAG_FROM_QUERY(
'Are there any data quality issues?',
'SELECT * FROM orders WHERE amount < 0 OR customer_id IS NULL'
);
Trend Analysis
SELECT RAG_FROM_QUERY(
'What trends do you see?',
'SELECT DATE_TRUNC(''month'', date) as month,
COUNT(*) as count,
AVG(value) as avg_value
FROM metrics
GROUP BY month
ORDER BY month'
);