Introspection Examples

Purpose: Code examples for using introspection classes
Last Updated: 2024-11-28

Basic Usage

Bootstrap Introspection

import { IntrospectionBootstrap } from '@/lib/codegen/introspection';

const bootstrap = new IntrospectionBootstrap();
await bootstrap.connect();
await bootstrap.loadSchema('graph');

const typeCatalog = bootstrap.getTypeCatalog();
const functionCatalog = bootstrap.getFunctionCatalog();

Using Type Catalog

const catalog = new TypeCatalog(client);
await catalog.loadSchema('graph');

// Get specific type
const nodeType = catalog.getType(oid);

// Get all types in dependency order
const sorted = catalog.topologicalSort();

// Get statistics
const stats = catalog.getStats();

Using Function Catalog

const catalog = new FunctionCatalog(client, typeCatalog);
await catalog.loadSchema('graph');

// Get all functions
const functions = catalog.getFunctions();

// Get specific function
const func = catalog.getFunction('create_resource');

SQL Queries

Base Types Query

SELECT 
  t.oid,
  t.typname,
  t.typnamespace::regnamespace::text as schema_name,
  t.typtype,
  t.typlen,
  t.typbyval
FROM pg_type t
WHERE t.typnamespace = 'pg_catalog'::regnamespace
  AND t.typname IN ('int4', 'int8', 'text', 'bool', ...);

Enum Types Query

SELECT 
  t.oid,
  t.typname,
  array_agg(e.enumlabel ORDER BY e.enumsortorder) as values
FROM pg_type t
JOIN pg_enum e ON t.oid = e.enumtypid
WHERE t.typnamespace = $1::regnamespace
GROUP BY t.oid, t.typname;

Composite Types Query

SELECT 
  t.oid,
  t.typname,
  a.attname,
  a.atttypid,
  a.attnum
FROM pg_type t
JOIN pg_attribute a ON t.typrelid = a.attrelid
WHERE t.typnamespace = $1::regnamespace
  AND t.typtype = 'c'
  AND a.attnum > 0
  AND NOT a.attisdropped
ORDER BY t.oid, a.attnum;

Domain Types Query

SELECT 
  t.oid,
  t.typname,
  t.typbasetype,
  pg_get_constraintdef(c.oid) as constraint_def
FROM pg_type t
LEFT JOIN pg_constraint c ON t.oid = c.contypid
WHERE t.typnamespace = $1::regnamespace
  AND t.typtype = 'd';

Array Types Query

SELECT 
  t.oid,
  t.typname,
  t.typelem as element_oid
FROM pg_type t
WHERE t.typnamespace = $1::regnamespace
  AND t.typelem != 0;

Function Metadata Query

SELECT 
  p.oid,
  p.proname,
  p.pronamespace::regnamespace::text as schema_name,
  p.pronargs,
  p.proargtypes,
  p.proargnames,
  p.proargmodes,
  p.prorettype,
  p.proretset,
  obj_description(p.oid, 'pg_proc') as comment
FROM pg_proc p
WHERE p.pronamespace = $1::regnamespace
  AND p.prokind = 'f'  -- functions only, not procedures
ORDER BY p.proname;

Parameter Information Query

SELECT 
  unnest(p.proargnames) as name,
  unnest(p.proargtypes::oid[]) as type_oid,
  unnest(p.proargmodes) as mode
FROM pg_proc p
WHERE p.oid = $1;

Return Type Information Query

SELECT 
  p.prorettype,
  p.proretset,
  p.prorows,
  t.typtype
FROM pg_proc p
JOIN pg_type t ON p.prorettype = t.oid
WHERE p.oid = $1;

Dependency Resolution

Type Dependencies

class TypeCatalog {
  getDependencies(type: Type): Type[] {
    switch (type.category) {
      case 'composite':
        // Composite depends on all field types
        return type.fields.map(f => this.getType(f.typeOid));
        
      case 'array':
        // Array depends on element type
        return [this.getType(type.elementOid)];
        
      case 'domain':
        // Domain depends on base type
        return [this.getType(type.baseTypeOid)];
        
      case 'range':
        // Range depends on subtype
        return [this.getType(type.subtypeOid)];
        
      default:
        return [];
    }
  }
}

Topological Sort

class TypeCatalog {
  topologicalSort(): Type[] {
    const visited = new Set<number>();
    const result: Type[] = [];
    
    const visit = (type: Type) => {
      if (visited.has(type.oid)) return;
      visited.add(type.oid);
      
      // Visit dependencies first
      for (const dep of this.getDependencies(type)) {
        visit(dep);
      }
      
      result.push(type);
    };
    
    for (const type of this.types.values()) {
      visit(type);
    }
    
    return result;
  }
}

Circular Dependency Detection

class TypeCatalog {
  hasCircularDependency(type: Type): boolean {
    const visited = new Set<number>();
    
    const check = (t: Type, path: Set<number>): boolean => {
      if (path.has(t.oid)) return true; // Circular!
      if (visited.has(t.oid)) return false;
      
      visited.add(t.oid);
      path.add(t.oid);
      
      for (const dep of this.getDependencies(t)) {
        if (check(dep, new Set(path))) return true;
      }
      
      path.delete(t.oid);
      return false;
    };
    
    return check(type, new Set());
  }
}

Performance Optimization

Batch Queries

async loadTypes(oids: number[]): Promise<Type[]> {
  const query = `
    SELECT * FROM pg_type
    WHERE oid = ANY($1::oid[])
  `;
  const result = await client.query(query, [oids]);
  return result.rows.map(row => this.parseType(row));
}

Caching

class TypeCatalog {
  private cache = new Map<number, Type>();
  
  getType(oid: number): Type | undefined {
    if (this.cache.has(oid)) {
      return this.cache.get(oid);
    }
    
    const type = this.loadType(oid);
    if (type) {
      this.cache.set(oid, type);
    }
    return type;
  }
}

Schema Filtering

async loadSchema(schemaName: string) {
  // Only load types from specified schema
  const query = `
    SELECT * FROM pg_type
    WHERE typnamespace = $1::regnamespace
  `;
  await this.executeQuery(query, [schemaName]);
}

Type Metadata

Type Category Enumeration

enum TypeCategory {
  BASE = 'b',        // Base type
  COMPOSITE = 'c',   // Composite (record)
  DOMAIN = 'd',      // Domain (constrained type)
  ENUM = 'e',        // Enum
  PSEUDO = 'p',      // Pseudo-type
  RANGE = 'r'        // Range type
}

Type Interface

interface Type {
  oid: number;
  name: string;
  schema: string;
  category: TypeCategory;
  length: number;
  byValue: boolean;
  // Category-specific fields
  fields?: CompositeField[];      // for composite
  values?: string[];              // for enum
  baseTypeOid?: number;          // for domain
  elementOid?: number;           // for array
  constraint?: string;           // for domain
}

Bootstrap Process

Full Bootstrap Example

class IntrospectionBootstrap {
  async bootstrap(schemaName: string) {
    // 1. Connect to database
    await this.connect();
    
    // 2. Load utility functions
    await this.loadUtilities();
    
    // 3. Load type catalog
    await this.typeCatalog.loadSchema(schemaName);
    
    // 4. Load function catalog
    await this.functionCatalog.loadSchema(schemaName);
    
    // 5. Validate catalogs
    await this.validate();
  }
}

Error Handling

Missing Types

getType(oid: number): Type {
  const type = this.cache.get(oid);
  if (!type) {
    throw new Error(`Type with OID ${oid} not found`);
  }
  return type;
}

Invalid Dependencies

validateDependencies(type: Type): void {
  for (const dep of this.getDependencies(type)) {
    if (!this.hasType(dep.oid)) {
      throw new Error(
        `Type ${type.name} depends on missing type ${dep.oid}`
      );
    }
  }
}

Related Documentation

• Introspection - Introspection overview
• Schema Generation - Using type catalog
• Type System - Type mappings

---

These examples demonstrate how to use the introspection system to discover database schema information.