API Reference

This page provides complete technical reference for the pg_ai_query extension API.

Extension Information

SQL API

CREATE EXTENSION

CREATE EXTENSION [IF NOT EXISTS] pg_ai_query [WITH] [SCHEMA schema_name];

Creates the extension and installs all functions.

Example:

CREATE EXTENSION IF NOT EXISTS pg_ai_query;

DROP EXTENSION

DROP EXTENSION [IF EXISTS] pg_ai_query [CASCADE | RESTRICT];

Removes the extension and all its functions.

Example:

DROP EXTENSION IF EXISTS pg_ai_query CASCADE;

Core Functions API

generate_query()

Signature:

generate_query(
    natural_language_query text,
    api_key text DEFAULT NULL,
    provider text DEFAULT 'auto'
) RETURNS text

Parameters:

Returns:

• Type: text
• Format: Valid PostgreSQL SQL query
• Constraints: Always includes LIMIT clause for SELECT statements

Exceptions:

• EXTERNAL_ROUTINE_EXCEPTION: AI API communication failures
• INVALID_PARAMETER_VALUE: Invalid provider or malformed input
• CONFIGURATION_FILE_ERROR: Configuration issues

Example:

SELECT generate_query('show top 10 customers by revenue');

get_database_tables()

Signature:

get_database_tables() RETURNS text

Parameters: None

Returns:

• Type: text
• Format: JSON array
• Content: Table metadata objects

JSON Schema:

{
  "type": "array",
  "items": {
    "type": "object",
    "properties": {
      "table_name": {"type": "string"},
      "schema_name": {"type": "string"},
      "table_type": {"type": "string"},
      "estimated_rows": {"type": "integer"},
      "table_size": {"type": "string"}
    },
    "required": ["table_name", "schema_name", "table_type"]
  }
}

Exceptions:

• EXTERNAL_ROUTINE_EXCEPTION: Database introspection failures

Example:

SELECT get_database_tables();

get_table_details()

Signature:

get_table_details(
    table_name text,
    schema_name text DEFAULT 'public'
) RETURNS text

Parameters:

Returns:

• Type: text
• Format: JSON object
• Content: Detailed table structure information

JSON Schema:

{
  "type": "object",
  "properties": {
    "table_name": {"type": "string"},
    "schema_name": {"type": "string"},
    "columns": {
      "type": "array",
      "items": {
        "type": "object",
        "properties": {
          "column_name": {"type": "string"},
          "data_type": {"type": "string"},
          "is_nullable": {"type": "boolean"},
          "column_default": {"type": ["string", "null"]},
          "character_maximum_length": {"type": ["integer", "null"]},
          "numeric_precision": {"type": ["integer", "null"]},
          "is_primary_key": {"type": "boolean"},
          "is_unique": {"type": "boolean"}
        }
      }
    },
    "constraints": {"type": "array"},
    "foreign_keys": {"type": "array"},
    "indexes": {"type": "array"}
  }
}

Exceptions:

• INVALID_PARAMETER_VALUE: Table does not exist
• EXTERNAL_ROUTINE_EXCEPTION: Database introspection failures

Example:

SELECT get_table_details('users', 'public');

Configuration API

Configuration File Location

The extension reads configuration from:

~/.pg_ai.config

Configuration Structure

[general]
# General settings
log_level = "INFO" | "DEBUG" | "WARNING" | "ERROR"
enable_logging = true | false
enable_postgresql_elog = true | false
request_timeout_ms = <integer>
max_retries = <integer>

[query]
# Query generation settings
enforce_limit = true | false
default_limit = <integer>

[openai]
# OpenAI provider settings
api_key = "<api_key_string>"
default_model = "gpt-4o" | "gpt-4" | "gpt-3.5-turbo"

[anthropic]
# Anthropic provider settings
api_key = "<api_key_string>"
default_model = "claude-3-5-sonnet-20241022"

Configuration Validation Rules

Error Codes

Extension Error Codes

AI Provider Error Codes

OpenAI Errors

Anthropic Errors

Internal APIs

Schema Discovery Engine

The extension uses these internal functions for schema analysis:

-- Internal function signatures (not callable by users)
_pg_ai_analyze_schema() RETURNS schema_info;
_pg_ai_get_table_relationships() RETURNS relationship_map;
_pg_ai_validate_query(query text) RETURNS boolean;

Query Generation Pipeline

1. Input Validation: Validates natural language input
2. Schema Discovery: Analyzes database structure
3. Context Building: Creates AI prompt with schema context
4. AI Request: Sends request to configured provider
5. Response Parsing: Extracts SQL from AI response
6. Query Validation: Validates generated SQL for safety
7. Result Return: Returns validated SQL query

Security Validation

The extension performs these security checks:

• SQL Injection Prevention: Sanitizes all inputs
• System Table Protection: Blocks access to pg_* and information_schema
• DDL Restriction: Can generate DDL but with warnings
• Privilege Respect: Honors PostgreSQL permission system

Performance Characteristics

Memory Usage

Network Requirements

Caching Behavior

• Schema Information: Cached per PostgreSQL session
• Configuration: Cached until session restart
• AI Responses: Not cached (each request is fresh)

Extension Metadata

System Catalog Integration

The extension registers these entries:

-- Extension registration
SELECT * FROM pg_extension WHERE extname = 'pg_ai_query';

-- Function registration
SELECT * FROM pg_proc WHERE proname LIKE '%generate%';

-- Dependencies
SELECT * FROM pg_depend WHERE refobjid IN (
    SELECT oid FROM pg_extension WHERE extname = 'pg_ai_query'
);

Version Information

-- Check extension version
SELECT extversion FROM pg_extension WHERE extname = 'pg_ai_query';

-- Function definitions
\df+ generate_query
\df+ get_database_tables
\df+ get_table_details

Compatibility

PostgreSQL Versions

Operating Systems

AI Provider Compatibility

This completes the technical API reference for the pg_ai_query extension.