Postgres LLM

Read the full tutorial: https://interfaze.ai/blog/introducing-postgres-llm-run-ai-natively-inside-postgres

A dynamic Postgres trigger function that runs an LLM request directly in your database for use cases like translation, classification, summarization and more.

Features

• Use any OpenAI chat completion API compatible LLM
• Reference context from any column within the same row
• Automatically update one or more target columns with the LLM result
• Uses OpenAI-compatible JSON schema structured output for reliable responses
• Supports commons use cases: Translation, Sentiment Analysis, Image Analysis, Web Search, etc.

Postgres requirements:

• http extension
• hstore extension

Quick Start

1. Create call_llm function

• Copy the Postgres function code from call_llm.sql
• Replace the API_KEY, BASE_URL and MODEL_NAME with your provider of choice. Defaults to Interfaze.
• Execute it in your database.

2. Add a trigger

Example table schema:

CREATE TABLE public.user_reviews (
  id bigint generated by default as identity not null,
  created_at timestamp with time zone not null default now(),
  user_id uuid not null default gen_random_uuid (),
  review_text text not null,
  sentiment text null,
  constraint user_reviews_pkey primary key (id)
);

Example trigger for sentiment analysis:

DROP TRIGGER IF EXISTS analyze_sentiment ON user_reviews;
CREATE TRIGGER analyze_sentiment
BEFORE INSERT OR UPDATE OF review_text ON user_reviews
FOR EACH ROW
WHEN (NEW.review_text IS NOT NULL)
EXECUTE FUNCTION call_llm('Analyze the sentiment of this text and respond with only "positive", "negative", or "neutral". return value in lowercase. Text: {review_text}','sentiment');

3. Insert a new row:

INSERT INTO user_reviews (review_text) VALUES ('I love this hackathon, I can build anything I want!!!!') RETURNING *;

Call LLM function parameters

Parameter	Description	Required
prompt	The prompt to use for the LLM. Supports {column_name} placeholders that get replaced with values from the row.	Yes
target_column(s)	One or more columns to update with the LLM result. Pass additional column names as extra arguments.	Yes

-- Single column
call_llm('<prompt>','<target_column>');

call_llm('Extract all text from this image: {image_url}','image_description');

-- Multiple columns
call_llm('<prompt>','<target_column_1>','<target_column_2>','<target_column_3>');

call_llm('Analyze the sentiment and translate the following review to Spanish. Text: {user_review_og}','emotion','user_review_es');

The function uses OpenAI JSON schema structured output (response_format with type: json_schema) to ensure the LLM returns a valid JSON object with exactly the specified target column names as keys.

Trigger Format

This trigger will be executed only if an insert or change happens to a specific column in a specific table and if that column is not null.

DROP TRIGGER IF EXISTS <trigger_name> ON <table_name>;
CREATE TRIGGER <trigger_name>
BEFORE INSERT OR UPDATE OF <column_name> ON <table_name>
FOR EACH ROW
WHEN (NEW.<column_name> IS NOT NULL)
EXECUTE FUNCTION call_llm('<prompt with {column} placeholders>','<target_column>');
-- or with multiple target columns:
EXECUTE FUNCTION call_llm('<prompt with {column} placeholders>','<target_column_1>','<target_column_2>');

Example use cases:

All examples are based on this example schema in user_reviews.sql table.

User generated translations

CREATE TRIGGER translate_es
BEFORE INSERT OR UPDATE OF user_review_og ON user_reviews
FOR EACH ROW
WHEN (NEW.user_review_og IS NOT NULL)
EXECUTE FUNCTION call_llm('Translate the following text to spanish (es). Only return the spanish text with no additional text. Text: {user_review_og}','user_review_es');

Sentiment analysis

CREATE TRIGGER analyze_sentiment
BEFORE INSERT OR UPDATE OF user_review_og ON user_reviews
FOR EACH ROW
WHEN (NEW.user_review_og IS NOT NULL)
EXECUTE FUNCTION call_llm('Analyze the sentiment of this text and respond with only "positive", "negative", or "neutral". return value in lowercase. Text: {user_review_og}','emotion');

Web search

CREATE TRIGGER background_search
BEFORE INSERT OR UPDATE OF full_name ON user_reviews
FOR EACH ROW
WHEN (NEW.full_name IS NOT NULL)
EXECUTE FUNCTION call_llm('Give a summary background on {full_name}.','user_background');

Image vision OCR

CREATE TRIGGER vision_ocr
BEFORE INSERT OR UPDATE OF attached_image_url ON user_reviews
FOR EACH ROW
WHEN (NEW.attached_image_url IS NOT NULL)
EXECUTE FUNCTION call_llm('Extract all text from this image: {attached_image_url}','image_description');

Multi-column: Sentiment analysis + Translation

A single trigger can populate multiple columns at once from one LLM call:

CREATE TRIGGER analyze_and_translate
BEFORE INSERT OR UPDATE OF user_review_og ON user_reviews
FOR EACH ROW
WHEN (NEW.user_review_og IS NOT NULL)
EXECUTE FUNCTION call_llm(
    'Analyze the sentiment and translate the following review to Spanish. Text: {user_review_og}',
    'emotion',
    'user_review_es'
);

Todo

• Build this as a native Postgres extension
• Improve insert performance with better async support
• Add retry logic on error
• Structured output support