AI SQL Functions
On this page
VERA Engine 4.5 introduces AI SQL functions that let you call large language models (LLMs) directly from SQL. These functions use the LATERAL TABLE syntax and require a model created with CREATE TEMPORARY MODEL.
Supported Version
VERA Engine 4.5 or later.
Prerequisites
- A model created with
CREATE TEMPORARY MODEL. See CREATE MODEL DDL.
Syntax Pattern
All AI SQL functions follow this pattern:
1SELECT col1, result_col
2FROM source_table,
3LATERAL TABLE(AI_FUNCTION_NAME(
4 MODEL => model_ref,
5 INPUT => input_col
6 [, additional_parameters]
7)) AS T(result_col [, ...]);AI_CLASSIFY
Classifies input text into one of the specified labels.
Syntax:
1LATERAL TABLE(AI_CLASSIFY(
2 MODEL => model_ref,
3 INPUT => text_col,
4 LABELS => ARRAY['label1', 'label2', ...]
5)) AS T(category, confidence)Parameters:
Output columns:
Example:
1SELECT content, category, confidence
2FROM articles,
3LATERAL TABLE(AI_CLASSIFY(
4 MODEL => my_model,
5 INPUT => content,
6 LABELS => ARRAY['sports', 'politics', 'technology']
7)) AS T(category, confidence);AI_SENTIMENT
Analyzes the sentiment of input text.
Syntax:
1LATERAL TABLE(AI_SENTIMENT(
2 MODEL => model_ref,
3 INPUT => text_col
4)) AS T(score, label, confidence)Parameters:
Output columns:
Example:
1SELECT review_text, label, confidence
2FROM reviews,
3LATERAL TABLE(AI_SENTIMENT(
4 MODEL => my_model,
5 INPUT => review_text
6)) AS T(score, label, confidence);AI_EXTRACT
Extracts structured fields from input text based on a JSON schema.
Syntax:
1LATERAL TABLE(AI_EXTRACT(
2 MODEL => model_ref,
3 INPUT => text_col,
4 EXTRACT_SCHEMA => '{"field": "type", ...}'
5)) AS T(extracted_json)Parameters:
Output columns:
Example:
1SELECT body, extracted_json
2FROM emails,
3LATERAL TABLE(AI_EXTRACT(
4 MODEL => my_model,
5 INPUT => body,
6 EXTRACT_SCHEMA => '{"sender_name": "string", "order_id": "string"}'
7)) AS T(extracted_json);AI_SUMMARIZE
Summarizes input text.
Syntax:
1LATERAL TABLE(AI_SUMMARIZE(
2 MODEL => model_ref,
3 INPUT => text_col
4 [, MAX_LENGTH => max_tokens]
5)) AS T(summary)Parameters:
Output columns:
The language of the summary depends on the model's default behavior. To control the output language, include a language instruction in your input, for example CONCAT('Summarize in English: ', text_col).
Example:
1SELECT article_id, summary
2FROM articles,
3LATERAL TABLE(AI_SUMMARIZE(
4 MODEL => my_model,
5 INPUT => CONCAT('Summarize in English: ', content),
6 MAX_LENGTH => 100
7)) AS T(summary);AI_EMBED
Generates a vector embedding for input text.
Syntax:
1LATERAL TABLE(AI_EMBED(
2 MODEL => model_ref,
3 INPUT => text_col
4)) AS T(embedding)Parameters:
Output columns:
AI_EMBED requires a model configured for embeddings, not completions. In your CREATE TEMPORARY MODEL statement, use an embedding model endpoint.
Example:
1SELECT doc_id, embedding
2FROM documents,
3LATERAL TABLE(AI_EMBED(
4 MODEL => my_embed_model,
5 INPUT => content
6)) AS T(embedding);AI_TRANSLATE
Translates input text from a source language to a target language.
Syntax:
1LATERAL TABLE(AI_TRANSLATE(
2 MODEL => model_ref,
3 INPUT => text_col,
4 SOURCE_LANG => source_language,
5 TARGET_LANG => target_language
6)) AS T(translated_text, detected_language)Parameters:
Output columns:
Example:
1SELECT message_id, translated_text, detected_language
2FROM support_messages,
3LATERAL TABLE(AI_TRANSLATE(
4 MODEL => my_model,
5 INPUT => message_body,
6 SOURCE_LANG => 'auto',
7 TARGET_LANG => 'en'
8)) AS T(translated_text, detected_language);AI_MASK
Masks personally identifiable information (PII) or other specified entities in input text.
Syntax:
1LATERAL TABLE(AI_MASK(
2 MODEL => model_ref,
3 INPUT => text_col,
4 MASK_ENTITIES => ARRAY['entity1', 'entity2', ...]
5)) AS T(masked_text, detected_entities)Parameters:
Output columns:
Example:
1SELECT record_id, masked_text
2FROM customer_records,
3LATERAL TABLE(AI_MASK(
4 MODEL => my_model,
5 INPUT => record_text,
6 MASK_ENTITIES => ARRAY['name', 'email', 'phone']
7)) AS T(masked_text, detected_entities);Limitations
- Requires VERA Engine 4.5 or later.
- All AI SQL functions use
LATERAL TABLEsyntax and cannot be used as scalar expressions. AI_EMBEDrequires an embedding model; all other functions require a completions model.- Latency and throughput depend on the model provider's API response time.