Azpg ai in database

In-database AI with the azure_ai extension on Flexible Server

The azure_ai extension lets SQL call Azure OpenAI and Azure Cognitive Services directly, so embeddings, LLM generation, and text analytics happen next to the data — no application round-trip. It adds three schemas:

Schema	What it calls	Key functions
azure_ai	config + LLM generation	set_setting, get_setting, generate
azure_openai	Azure OpenAI	create_embeddings
azure_cognitive	Azure Language service	summarize_abstractive/_extractive, analyze_sentiment, detect_language, extract_key_phrases, recognize_entities

This skill is read-write and explicit. Inspecting which schemas/functions exist and whether settings are configured is safe. But every AI call costs money and adds latency, set_setting stores a secret in the database, and bulk embedding writes to your table — so configuration and bulk generation are guarded and run through dry-run-capable scripts. See Safety protocol.

Retrieval/index design (HNSW, DiskANN, distance operators, RAG query shape) belongs to azpg-pgvector-rag; this skill produces the embeddings and text. Deep function reference is in reference.md; auth, cost, RBAC, and rate-limit constraints are in azure-constraints.md.

Live instance context (dynamic injection)

PGCONN is a libpq connection string. All read-only.

• Is azure_ai installed? !psql "$PGCONN" -tAc "SELECT extversion FROM pg_extension WHERE extname='azure_ai';" 2>/dev/null || echo "(not installed — needs allow-listing + CREATE EXTENSION)"
• Is the OpenAI endpoint configured? (prints the endpoint, never the key) !psql "$PGCONN" -tAc "SELECT azure_ai.get_setting('azure_openai.endpoint');" 2>/dev/null || echo "(unset, or you lack azure_ai_settings_manager)"
• Is vector available to store embeddings? !psql "$PGCONN" -tAc "SELECT extversion FROM pg_extension WHERE extname='vector';" 2>/dev/null || echo "(not installed)"

When to use this skill

Trigger on: "generate embeddings for this column", "call Azure OpenAI from SQL", "summarize/translate/score sentiment on rows in the database", "set up azure_ai", "embed my whole table", "use an LLM inside Postgres". If the follow-on task is "now design the ANN index and write the similarity query" hand off to azpg-pgvector-rag.

Decision flow

Steps 1–2 are read/inspect (safe); steps 3–5 are write/mutate + billable (guarded, explicit).

1. Verify prerequisites (safe): azure_ai (and vector if storing) installed; caller has azure_ai_settings_manager. Use scripts/inspect_azure_ai.sql.
2. Confirm the model deployment (safe): the deployment_name argument must be an existing deployment in your Azure OpenAI / Foundry resource.
3. Configure endpoint + key (guarded, stores a secret): via scripts/configure_azure_ai.sh. Prefer Microsoft Entra / managed identity over a subscription key where supported — and note managed identity here means the server's system-assigned identity, not a user-assigned one (see azure-constraints.md).
4. Prototype on ONE row (billable but tiny): run create_embeddings / generate / a azure_cognitive call on a single value and inspect output + dimensions before touching the whole table.
5. Bulk generate (guarded, billable, writes rows): batch with scripts/bulk_embed.sh — small batches, resumable, --dry-run shows the SQL and row count without calling the API.

Core calls (verified syntax)

Configuration (members of azure_ai_settings_manager only — all Azure admin users have it):

SELECT azure_ai.set_setting('azure_openai.endpoint', 'https://<res>.openai.azure.com');
SELECT azure_ai.set_setting('azure_openai.subscription_key', '<key>');
SELECT azure_ai.get_setting('azure_openai.endpoint');   -- readback (key is retrievable too; guard it)

Embeddings — create_embeddings(deployment_name, input [, timeout_ms, throw_on_error]), returns a float array; cast to vector:

-- one value
SELECT azure_openai.create_embeddings('my-embedding-deploy', 'hello world')::vector;

-- store for a whole column (this WRITES and bills per row — see bulk_embed.sh)
UPDATE products
SET   embedding = azure_openai.create_embeddings('my-embedding-deploy', description)::vector
WHERE embedding IS NULL;

LLM generation:

SELECT azure_ai.generate('Summarize this review in one sentence: '|| review_text)
FROM reviews WHERE id = 1;

Azure Cognitive Services (needs azure_cognitive.endpoint + key set):

SELECT azure_cognitive.analyze_sentiment(comment, 'en')  FROM feedback LIMIT 1;
SELECT azure_cognitive.summarize_abstractive(bill_text, 'en') FROM bills LIMIT 1;

See reference.md for full argument lists, batch/array forms, and return-type composites.

Safety protocol

1. Reads run automatically. Listing schemas/functions and get_setting of the endpoint change nothing. Avoid echoing subscription_key in logs.
2. Configuration is a guarded write that stores a secret. set_setting writes the key into a config table. Run it via scripts/configure_azure_ai.sh, which reads the key from an env var and never prints it (even in --dry-run). Prefer managed identity where available.
3. Every AI call bills and adds latency. Always prototype on one row before bulk. State the model + approximate row count so the user consents to the cost.
4. Bulk embedding is a guarded, resumable write. bulk_embed.sh processes in small batches filtered to WHERE <col> IS NULL, so an interrupted run resumes without re-billing done rows. --dry-run prints the statement and the count of rows that would be processed, and calls no API.
5. throw_on_error defaults to true — an API failure raises and rolls back the surrounding transaction. Keep batches transaction-scoped so a mid-batch failure leaves no partial, half-billed state.

Bundled files

• reference.md — every function's arguments, batch/array forms, return composites, and end-to-end embed→index→search recipe (index step handed to azpg-pgvector-rag).
• azure-constraints.md — Entra vs key auth, azure_ai_settings_manager RBAC, cost/rate-limit/throttling behaviour, region and model-deployment requirements.
• scripts/inspect_azure_ai.sql — read-only; extension present, schemas/functions available, endpoints configured.
• scripts/configure_azure_ai.sh — guarded; sets endpoint/key from env vars, secret never printed, --dry-run.
• scripts/bulk_embed.sh — guarded; batched, resumable embedding of a text column, --dry-run, cost preview.
• examples/enrich_with_llm.md — worked example: add embeddings + an LLM-generated summary column to a table, safely.