GitHub - JigsawStack/postgres-llm: A dynamic Postgres trigger function that runs an LLM request directly in your database for use cases like translation, classification, summarization and more

Read the full tutorial: https://interfaze.ai/blog/run-llms-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

Async execution - non-blocking, model runs async
Use any Chat completion API compatible LLM
Reference context from any column within the same row
Automatically update multiple target columns
Built-in retry logic with configurable max attempts

Postgres requirements:

Quick Start

1. Run `init.sql`

Copy the Postgres setup code from init.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
AFTER INSERT OR UPDATE OF review_text ON user_reviews
FOR EACH ROW
WHEN (NEW.review_text IS NOT NULL)
EXECUTE FUNCTION llm.call('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 *;

The INSERT returns immediately. The sentiment column will be populated asynchronously by the pg_cron worker within seconds.

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
llm.call('<prompt>','<target_column>');

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

-- Multiple columns
llm.call('<prompt>','<target_column_1>','<target_column_2>','<target_column_3>');

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

The function uses 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.

The trigger enqueues a job into llm.queue and returns immediately.

DROP TRIGGER IF EXISTS <trigger_name> ON <table_name>;
CREATE TRIGGER <trigger_name>
AFTER INSERT OR UPDATE OF <column_name> ON <table_name>
FOR EACH ROW
WHEN (NEW.<column_name> IS NOT NULL)
EXECUTE FUNCTION llm.call('<prompt with {column} placeholders>','<target_column>');
-- or with multiple target columns:
EXECUTE FUNCTION llm.call('<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
AFTER INSERT OR UPDATE OF user_review_og ON user_reviews
FOR EACH ROW
WHEN (NEW.user_review_og IS NOT NULL)
EXECUTE FUNCTION llm.call('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
AFTER INSERT OR UPDATE OF user_review_og ON user_reviews
FOR EACH ROW
WHEN (NEW.user_review_og IS NOT NULL)
EXECUTE FUNCTION llm.call('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
AFTER INSERT OR UPDATE OF full_name ON user_reviews
FOR EACH ROW
WHEN (NEW.full_name IS NOT NULL)
EXECUTE FUNCTION llm.call('Give a summary background on {full_name}.','user_background');

Image vision OCR

CREATE TRIGGER vision_ocr
AFTER INSERT OR UPDATE OF attached_image_url ON user_reviews
FOR EACH ROW
WHEN (NEW.attached_image_url IS NOT NULL)
EXECUTE FUNCTION llm.call('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
AFTER INSERT OR UPDATE OF user_review_og ON user_reviews
FOR EACH ROW
WHEN (NEW.user_review_og IS NOT NULL)
EXECUTE FUNCTION llm.call(
    'Analyze the sentiment and translate the following review to Spanish. Text: {user_review_og}',
    'emotion',
    'user_review_es'
);