Function Reference

This page provides complete reference documentation for all functions provided by the pg_ai_query extension.

Main Functions

generate_query()

Generates SQL queries from natural language descriptions with automatic schema discovery.

Signature

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

Parameters

Returns

• Type: text
• Content: A valid PostgreSQL SQL query

Examples

-- Basic usage
SELECT generate_query('show all users');

-- With custom API key
SELECT generate_query(
    'find users created in the last week',
    'sk-your-api-key-here'
);

-- With specific provider
SELECT generate_query(
    'calculate monthly revenue by product category',
    NULL,
    'openai'
);

-- Complex query
SELECT generate_query('show top 10 customers by total order value with their contact information');

Behavior

• Schema Discovery: Automatically analyzes your database schema to understand table structures and relationships
• Safety Limits: Always adds LIMIT clauses to SELECT queries (configurable)
• Query Validation: Validates generated queries for safety and correctness
• Error Handling: Returns descriptive error messages for invalid requests

Supported Query Types

explain_query()

Analyzes query performance using EXPLAIN ANALYZE and provides AI-powered optimization insights.

Signature

explain_query(
    query_text text,
    api_key text DEFAULT NULL,
    provider text DEFAULT 'auto'
) RETURNS text

Parameters

Returns

• Type: text
• Content: Detailed performance analysis and optimization recommendations

Examples

-- Basic usage
SELECT explain_query('SELECT * FROM users WHERE active = true');

-- Complex query analysis
SELECT explain_query('
    SELECT u.username, COUNT(o.id) as order_count
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.username
    ORDER BY order_count DESC
    LIMIT 100
');

-- With specific provider
SELECT explain_query(
    'SELECT * FROM products WHERE price > 100',
    'your-api-key',
    'anthropic'
);

-- Integration with generate_query
WITH generated AS (
    SELECT generate_query('find recent high-value orders') as query
)
SELECT explain_query((SELECT query FROM generated));

Output Structure

The function returns structured text analysis containing:

• Query Overview: Brief description of query purpose
• Performance Summary: Execution time, cost estimates, rows processed
• Execution Plan Analysis: Breakdown of PostgreSQL’s execution strategy
• Performance Issues: Identified bottlenecks and inefficiencies
• Optimization Suggestions: Specific recommendations for improvement
• Index Recommendations: Suggested indexes with CREATE statements

Supported Query Types

Behavior

• Actual Execution: Uses EXPLAIN ANALYZE which executes the query
• Performance Metrics: Provides real execution times and row counts
• AI Analysis: Processes execution plan through AI for insights
• Security: Only allows read-only query types
• Error Handling: Graceful handling of invalid queries

Performance Considerations

• Query Execution: The function actually runs your query
• Analysis Time: AI processing adds 1-3 seconds
• Resource Usage: Same as running the query manually
• Best for: Development, testing, and performance optimization

get_database_tables()

Returns metadata about all user tables in the database.

Signature

get_database_tables() RETURNS text

Parameters

None.

Returns

• Type: text (JSON format)
• Content: Array of table metadata objects

JSON Structure

[
  {
    "table_name": "users",
    "schema_name": "public",
    "table_type": "BASE TABLE",
    "estimated_rows": 1500,
    "table_size": "128 kB"
  },
  {
    "table_name": "orders",
    "schema_name": "public",
    "table_type": "BASE TABLE",
    "estimated_rows": 5000,
    "table_size": "512 kB"
  }
]

Example Usage

-- Get all tables
SELECT get_database_tables();

-- Pretty print with formatting
SELECT jsonb_pretty(get_database_tables()::jsonb);

-- Extract specific information
SELECT
    table_name,
    estimated_rows
FROM jsonb_to_recordset(get_database_tables()::jsonb)
AS x(table_name text, estimated_rows int);

get_table_details()

Returns detailed information about a specific table including columns, constraints, and relationships.

Signature

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

Parameters

Returns

• Type: text (JSON format)
• Content: Detailed table information object

JSON Structure

{
  "table_name": "users",
  "schema_name": "public",
  "columns": [
    {
      "column_name": "id",
      "data_type": "integer",
      "is_nullable": false,
      "column_default": "nextval('users_id_seq'::regclass)",
      "is_primary_key": true
    },
    {
      "column_name": "email",
      "data_type": "character varying",
      "character_maximum_length": 150,
      "is_nullable": false,
      "column_default": null,
      "is_unique": true
    }
  ],
  "constraints": [
    {
      "constraint_name": "users_pkey",
      "constraint_type": "PRIMARY KEY",
      "column_names": ["id"]
    },
    {
      "constraint_name": "users_email_key",
      "constraint_type": "UNIQUE",
      "column_names": ["email"]
    }
  ],
  "foreign_keys": [],
  "indexes": [
    {
      "index_name": "users_pkey",
      "columns": ["id"],
      "is_unique": true,
      "is_primary": true
    }
  ]
}

Example Usage

-- Basic usage
SELECT get_table_details('users');

-- Specific schema
SELECT get_table_details('orders', 'sales');

-- Extract column information
SELECT
    column_name,
    data_type,
    is_nullable
FROM jsonb_to_recordset(
    (get_table_details('users')::jsonb)->'columns'
) AS x(column_name text, data_type text, is_nullable boolean);

-- Find all foreign keys
SELECT
    constraint_name,
    column_names
FROM jsonb_to_recordset(
    (get_table_details('orders')::jsonb)->'foreign_keys'
) AS x(constraint_name text, column_names text[]);

Utility Functions

Schema Discovery Process

The extension uses these internal processes when analyzing your database:

1. Table Discovery: Identifies all user tables (excludes system catalogs)
2. Column Analysis: Examines data types, constraints, and nullable fields
3. Relationship Mapping: Discovers foreign key relationships between tables
4. Index Analysis: Identifies indexes that might optimize query performance
5. Statistics Gathering: Collects row counts and table sizes for optimization

Internal Validation

All functions perform these validations:

• Permission Checks: Ensures access to only authorized tables
• Schema Validation: Verifies table and schema existence
• SQL Injection Prevention: Sanitizes all inputs
• Query Safety: Prevents generation of harmful operations

Error Codes and Messages

Common Error Conditions

Debugging Functions

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

-- Verify function availability
\df generate_query
\df get_database_tables
\df get_table_details

-- Test basic functionality
SELECT generate_query('SELECT 1');

Performance Characteristics

Query Generation Performance

Memory Usage

• Schema Analysis: ~1MB per 100 tables
• Query Generation: ~5-10MB per request
• Caching: Schema information cached in session

Network Requirements

• API Requests: HTTPS to OpenAI/Anthropic APIs
• Bandwidth: ~1-10KB per query generation request
• Latency: Typically 1-5 seconds per request

Function Security

Access Control

• Functions execute with caller’s privileges
• No privilege escalation
• Respects PostgreSQL’s standard permission system

Data Protection

• Never sends actual data to AI providers
• Only schema metadata is transmitted
• Generated queries can be reviewed before execution

API Security

• API keys transmitted over HTTPS only
• Keys stored securely in configuration files
• No logging of API keys

Next Steps

• See Examples for practical usage patterns
• Review Best Practices for production usage
• Check API Reference for complete technical details