Text-to-SQL
===========

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 :ref:`Searching a semantic knowledge base <Searching a semantic knowledge base>`  :

- **Agent-driven.** An :ref:`Agents reference <Agents reference>`  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 :ref:`Finding an alias <Finding an alias>`  and
  :ref:`Running an alias <Running an 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 :ref:`agent tools <Knowledge bases reference>` 
. 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:

.. csv-table::
  :header: Tool,Role in text-to-SQL
  :widths: 10,30
  :align: left
  :class: longtable

  :ref:`AI pipelines <Searching a semantic knowledge base>` ,One ranked list of the schema entities (and any matching aliases) relevant to the question.
  :ref:`AI pipelines <Searching a semantic knowledge base>`  /  :ref:`AI pipelines <Searching a semantic knowledge base>`  /  :ref:`AI pipelines <Searching a semantic knowledge base>` ,"Narrower schema lookups when the agent needs only tables, only columns, or lean definitions."
  :ref:`AI pipelines <Searching a semantic knowledge base>` ,Finds entities through the intent captured in `COMMENT ON` text.
  `run_sql_query`,Runs the read-only SQL the agent generates and returns the rows.

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 :ref:`Semantic aliases <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 :ref:`Managing agents <Managing agents>`  and the :ref:`tool catalog <Knowledge bases reference>`  .

When to reach for aliases instead
---------------------------------

Aliases are the deterministic counterpart to agent generation. A
:ref:`Semantic aliases <Semantic aliases>`  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 :ref:`Examples <Examples>`  .

- Review the search functions in :ref:`Searching a semantic knowledge base <Searching a semantic knowledge base>`  and the alias functions
  in :ref:`Semantic aliases <Semantic aliases>`  .