Text-to-SQL v7

Text-to-SQL turns a natural-language question, such as "Which customers spent the most last quarter?", into a correct query against your own schema. A semantic KB makes this reliable: rather than guessing at table and column names, the question is first grounded in the schema the KB indexed.

Two complementary paths build on the search functions:

• Agent-driven. An AIDB agent calls the KB's search functions as tools to discover the relevant schema, then generates and runs SQL. Best for open-ended, conversational analytics.
• Application-driven. Your application calls aidb.search_semantic_aliases() and aidb.execute_semantic_alias() to resolve a question to a reviewed, parameterized query. Best for recurring questions that need deterministic, governed answers.

Agents as the text-to-SQL engine

The semantic KB search functions are registered as native agent tools. An agent given these tools can, on its own, find the tables and columns a question maps to and then write SQL grounded in them. The tools it draws on:

All the search tools are read-only, so an agent can explore schema freely without any risk of a write.

The workflow

   "Which customers        ┌───────────────────────┐
    spent the most   ─────▶│  agent (pg_agent)     │
    last quarter?"         └───────────┬───────────┘
                                       │ 1. discover schema
                                       ▼
                           ┌───────────────────────┐
                           │  semantic_kb_search   │  schema entities
                           │  (+ get_metadata, …)  │  + matching aliases
                           └───────────┬───────────┘
                                       │ 2. generate SQL grounded in the results
                                       ▼
                           ┌───────────────────────┐
                           │  run_sql_query        │  execute read-only SQL
                           └───────────┬───────────┘
                                       ▼
                                   answer rows

1. Discover. The agent calls semantic_kb_search (and the narrower functions as needed) to retrieve the exact tables, columns, and comments the question maps to, plus any semantic aliases that already match, which appear in the results with source_type = 'alias'.

2. Generate, grounded. The agent writes SQL against the retrieved definitions. Generating against real schema context (actual column names, types, and comments) is far more accurate than generating against table names alone.

3. Run. The agent executes the SQL with run_sql_query and returns the rows.

To build such an agent, give it the semantic KB tools when you create it. See Creating agents and the tool catalog.

When to reach for aliases instead

Aliases are the deterministic counterpart to agent generation. A semantic alias is a reviewed, parameterized SELECT, so it returns dependable results with no per-request generation to verify. Use aliases when:

• The same question recurs and deserves one canonical query.
• You need governed execution, where an alias runs as a single read-only SELECT and can run under a least-privilege role via execute_role.

Aliases surface inside semantic_kb_search results, so an agent can see that a curated query exists for a question. Executing an alias, though, is done directly through aidb.execute_semantic_alias(). The alias functions are intentionally not agent tools.

How this compares to raw generation

• Grounded, not guessed. The model sees the actual definitions and comments of the relevant relations, so it references columns that exist, with the right types.
• Trusted answers accrue. Every question you capture as an alias is one that no longer needs generation, shrinking the surface where a model might generate wrong SQL.
• Read-only by construction. Schema-search tools and aliases are all read-only, so the discovery and answer paths can't mutate data.

Next steps

• Walk through the whole flow on a sample schema in the example.
• Review the search functions in Searching a semantic KB and the alias functions in Semantic aliases.