-- Custom composite type
CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip_code TEXT,
    country TEXT DEFAULT 'US'
);

-- Table using the type
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
address address,
created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Complex function with polymorphic types
CREATE OR REPLACE FUNCTION process_data(
    input_data ANYARRAY,
    config JSONB DEFAULT '{}',
    debug_mode BOOLEAN DEFAULT FALSE
) RETURNS TABLE (
    processed_items BIGINT[],
    metadata JSONB,
    processing_time INTERVAL
) AS $$
BEGIN
    -- Function implementation
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Complex partial index
CREATE INDEX idx_active_users_email
ON users (LOWER(email))
WHERE status = 'active'
AND last_login > NOW() - INTERVAL '30 days'
AND deleted_at IS NULL;

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Error example
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
    -- Missing comma here
    name TEXT NOT NULL
);

// Efficient parsing with memory management
func ParseMigrations(files []string) ([]*ParseTree, error) {
    parser := pg_query.NewParser()
    defer parser.Cleanup() // Free C memory

    var trees []*ParseTree

    for _, file := range files {
        content, err := os.ReadFile(file)
        if err != nil {
            return nil, err
        }

        // Parse with automatic memory management
        tree, err := parser.Parse(string(content))
        if err != nil {
            return nil, fmt.Errorf("parse error in %s: %w", file, err)
        }

        trees = append(trees, tree)
    }

    return trees, nil

}

    </div>

    ## INTEGRATION EXAMPLES

    ### Real-World Parsing Scenarios

    <div className="space-y-6 my-8">
    <div className="brutalist-card p-6">
    <h4 className="font-black uppercase mb-3">SUPABASE MIGRATIONS</h4>
    <div className="brutalist-code text-sm">
    ```sql

\ - Supabase auth schema.

CREATE TABLE IF NOT EXISTS auth.users (
id UUID NOT NULL PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
email TEXT,
phone TEXT,
raw_user_meta_data JSONB DEFAULT '',
email_confirmed_at TIMESTAMPTZ,
phone_confirmed_at TIMESTAMPTZ
);

\ - Row Level Security.

ALTER TABLE auth.users ENABLE ROW LEVEL SECURITY;

\ - RLS Policy.

CREATE POLICY "Users can view their own profile"

ON auth.users FOR SELECT

USING (auth.uid() = id);



- ☑ Correctly parses auth schema, RLS, and policy functions





- COMPLEX DATABASE MIGRATION



```sql
-- Multi-step schema evolution
CREATE TYPE user_role AS ENUM ('admin', 'user', 'moderator');

CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT UNIQUE NOT NULL,
role user_role DEFAULT 'user',
settings JSONB DEFAULT '{}',
created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Generated column
ALTER TABLE users
ADD COLUMN email_domain TEXT
GENERATED ALWAYS AS (split_part(email, '@', 2)) STORED;

-- Partial index
CREATE INDEX idx_users_admin
ON users (email)
WHERE role = 'admin';

-- Trigger function
CREATE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;

$$
LANGUAGE plpgsql;

-- Trigger
CREATE TRIGGER users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION update_updated_at();

// How pgsquash uses the parser
type MigrationAnalyzer struct {
    parser *pg_query.Parser
    tracker *ObjectTracker
}

func (ma \*MigrationAnalyzer) AnalyzeMigration(sql string) (\*AnalysisResult, error) {
// Parse SQL into AST
tree, err := ma.parser.Parse(sql)
if err != nil {
return nil, err
}

    // Extract objects and relationships
    objects := ma.extractObjects(tree)
    dependencies := ma.buildDependencyGraph(objects)

    // Track object lifecycle
    ma.tracker.UpdateObjects(objects)

    // Generate consolidation opportunities
    opportunities := ma.findConsolidationOpportunities(dependencies)

    return &AnalysisResult{
        Objects: objects,
        Dependencies: dependencies,
        Opportunities: opportunities,
    }, nil

}

    </div>

    ## TROUBLESHOOTING

    ### Common Parser Issues

    <div className="brutalist-card-lg section-accent-warning p-8 my-8">
    <h3 className="text-2xl font-black uppercase mb-6">PARSER TROUBLESHOOTING</h3>

    <div className="space-y-6">
    <div>
    <h4 className="font-black uppercase mb-3">ENCODING ISSUES</h4>
    <div className="brutalist-card-sm p-4">
    <p className="font-bold text-sm mb-2">Problem:</p>
    <p className="text-xs font-bold">Non-UTF8 characters causing parse failures</p>
    <p className="font-bold text-sm mb-2 mt-2">Solution:</p>
    <p className="text-xs font-bold">Ensure all migration files are UTF-8 encoded</p>
    </div>
    - </div>

    - <div>
    - <h4 className="font-black uppercase mb-3">VERSION INCOMPATIBILITY</h4>
    - <div className="brutalist-card-sm p-4">
    - <p className="font-bold text-sm mb-2">Problem:</p>
    - <p className="text-xs font-bold">Using PostgreSQL 17 features with older parser</p>
    - <p className="font-bold text-sm mb-2 mt-2">Solution:</p>
    - <p className="text-xs font-bold">Update capysquash to latest version for newest syntax support</p>
    - </div>
    - </div>

    - <div>
    - <h4 className="font-black uppercase mb-3">MEMORY LIMITS</h4>
    - <div className="brutalist-card-sm p-4">
    - <p className="font-bold text-sm mb-2">Problem:</p>
    - <p className="text-xs font-bold">Large migration files causing memory issues</p>
    - <p className="font-bold text-sm mb-2 mt-2">Solution:</p>
    - <p className="text-xs font-bold">Use streaming mode for files > 10MB</p>
    - </div>
    - </div>
    - </div>
    </div>

    ## NEXT STEPS

    - [Dependency Resolution Algorithms](/docs/advanced/dependency-resolution-algorithms) - How we build dependency graphs
    - [Migration Optimization Strategies](/docs/advanced/migration-optimization-strategies) - Advanced consolidation techniques
    - [SQL Parsing Internals](/docs/advanced/sql-parsing) - Deep dive into parsing implementation
    - [Architecture Deep Dive](/docs/advanced/architecture-deep-dive) - Complete system overview

    ---

    <div className="brutalist-card-lg category-announcement p-8 my-8">
    <h3 className="text-2xl font-black uppercase mb-4">KEY TAKEAWAY</h3>
    <p className="font-bold text-lg">
    By using PostgreSQL's actual parser, pgsquash achieves <strong>100% accuracy</strong> and <strong>future-proof compatibility</strong> - no custom parser can match this level of reliability.
    </p>
    </div>
    $$.