sql-utilities/docs/SqlUtilities.PostgreSql.md

# Strata.SqlTools.PostgreSql

**PostgreSQL SQL Query Analysis and Breakdown**

---

## Overview

The `Strata.SqlTools.PostgreSql` package provides comprehensive support for parsing, analyzing, and manipulating PostgreSQL SQL queries. It extends the core `Strata.SqlTools` library with PostgreSQL-specific syntax support, including positional parameters (`$1`, `$2`) and named parameters (`:param`).

### Key Features

- ✅ **PostgreSQL Syntax Support** - Full support for PostgreSQL SQL dialect
- ✅ **Positional Parameters** - `$1`, `$2`, `$3` parameter syntax
- ✅ **Named Parameters** - `:parameter` and `@parameter` syntax
- ✅ **Query Breakdown** - Parse SELECT statements into component clauses
- ✅ **Query Collections** - Batch analysis with parameter usage reports
- ✅ **Statement Parsing** - Token-based SQL parsing with PostgreSQL extensions
- ✅ **Expression System** - Type-safe expression trees for query building
- ✅ **Mermaid Diagrams** - Visual query structure and flow diagrams

---

## Installation

```bash
dotnet add package Strata.SqlTools.PostgreSql
```

**Dependencies:**
- `Strata.SqlTools` (core functionality)
- .NET 8.0+

---

## Quick Start

### Basic Query Breakdown

```csharp
using Strata.SqlTools.Breakdowns.PostgreSql;

string sql = @"
    SELECT id, name, email, age
    FROM users
    WHERE age > $1
      AND is_active = $2
    ORDER BY name ASC
";

var breakdown = new QueryBreakdown(sql);

Console.WriteLine($"SELECT: {breakdown.SelectClause}");
Console.WriteLine($"FROM: {breakdown.FromClause}");
Console.WriteLine($"WHERE: {breakdown.WhereClause}");
Console.WriteLine($"ORDER BY: {breakdown.OrderByClause}");

// Access parameters
var parameters = breakdown.GetParameters();
foreach (var param in parameters)
{
    Console.WriteLine($"Parameter: {param.Name}");
}
```

**Output:**
```
SELECT: id, name, email, age
FROM: users
WHERE: age > $1 AND is_active = $2
ORDER BY: name ASC
Parameter: $1
Parameter: $2
```

---

## PostgreSQL-Specific Features

### Positional Parameters ($n)

PostgreSQL uses `$1`, `$2`, etc. for positional parameters:

```csharp
string sql = @"
    SELECT * FROM orders
    WHERE customer_id = $1
      AND order_date > $2
      AND status = $3
";

var breakdown = new QueryBreakdown(sql);

// Add parameter values
breakdown.AddParameter("$1", 12345);
breakdown.AddParameter("$2", DateTime.Now.AddDays(-30));
breakdown.AddParameter("$3", "Pending");

// Get SQL with parameters
string fullSql = breakdown.GetSql();
```

### Named Parameters (:param or @param)

PostgreSQL also supports named parameters:

```csharp
string sql = @"
    SELECT * FROM products
    WHERE price > :min_price
      AND category = :category
      AND in_stock = @stock_flag
";

var breakdown = new QueryBreakdown(sql);

breakdown.AddParameter(":min_price", 99.99m);
breakdown.AddParameter(":category", "Electronics");
breakdown.AddParameter("@stock_flag", true);
```

### Parameter Dictionary

Get all parameters as a dictionary:

```csharp
var breakdown = new QueryBreakdown(sql);
breakdown.AddParameter("$1", 100);
breakdown.AddParameter("$2", "Active");

var paramDict = breakdown.GetParameterDictionary();
foreach (var (name, value) in paramDict)
{
    Console.WriteLine($"{name} = {value}");
}
// Output:
// $1 = 100
// $2 = Active
```

---

## QueryBreakdownCollection

Analyze multiple queries and generate comprehensive reports.

### Basic Usage

```csharp
using Strata.SqlTools.Breakdowns.PostgreSql;

var collection = new QueryBreakdownCollection();

// Add multiple queries
collection.Add(new QueryBreakdown(@"
    SELECT id, name FROM users WHERE age > $1
"));

collection.Add(new QueryBreakdown(@"
    SELECT * FROM orders WHERE user_id = $1 AND status = $2
"));

collection.Add(new QueryBreakdown(@"
    SELECT product_name, price FROM products WHERE category = :category
"));

// Get summaries
var summaries = collection.GetQuerySummaries();
foreach (var summary in summaries)
{
    Console.WriteLine(summary);
}
```

### Parameter Usage Report

The `GetParameterUsageReport()` method provides detailed information about parameter usage across all queries:

```csharp
var report = collection.GetParameterUsageReport();

Console.WriteLine($"Total Queries: {report.TotalQueries}");
Console.WriteLine($"Total Parameters: {report.TotalParameters}");
Console.WriteLine($"Unique Parameters: {report.UniqueParameterNames.Count}");

Console.WriteLine("\nPositional Parameters:");
foreach (var (param, count) in report.PositionalParameterUsage)
{
    Console.WriteLine($"  {param}: used {count} times");
}

Console.WriteLine("\nNamed Parameters:");
foreach (var (param, count) in report.NamedParameterUsage)
{
    Console.WriteLine($"  {param}: used {count} times");
}
```

**Example Output:**
```
Total Queries: 3
Total Parameters: 4
Unique Parameters: 3

Positional Parameters:
  $1: used 2 times
  $2: used 1 times

Named Parameters:
  :category: used 1 times
```

### Collection Analysis Methods

```csharp
var collection = new QueryBreakdownCollection();
// ... add queries ...

// Get total selected columns across all queries
int totalColumns = collection.GetTotalSelectedColumns();

// Get all unique table references
var tables = collection.GetUniqueTableReferences();
Console.WriteLine($"Tables: {string.Join(", ", tables)}");

// Get query summaries
var summaries = collection.GetQuerySummaries();
```

---

## Markdown Visualization

The `Strata.SqlTools.Markdown` package includes PostgreSQL-specific generators.

### Installation

```bash
dotnet add package Strata.SqlTools.Markdown
```

### QueryBreakdownGenerator

Generate Mermaid diagrams for individual queries:

```csharp
using Strata.SqlTools.Markdown.PostgreSql;

var breakdown = new QueryBreakdown(@"
    SELECT u.id, u.name, o.total
    FROM users u
    JOIN orders o ON u.id = o.user_id
    WHERE u.age > $1
    ORDER BY o.total DESC
");

var generator = new QueryBreakdownGenerator();

// Generate flowchart diagram
string diagram = generator.GenerateMermaidDiagram(breakdown, "User Orders Query");
```

**Example Output:**

```mermaid
flowchart TD
    Start([Start]) --> Select[SELECT u.id, u.name, o.total]
    Select --> From[FROM users u]
    From --> Join[JOIN orders o]
    Join --> Where[WHERE u.age > $1]
    Where --> OrderBy[ORDER BY o.total DESC]
    OrderBy --> End([End])
```

### SqlStatementGenerator

Generate sequence and ER diagrams:

```csharp
var sqlGenerator = new SqlStatementGenerator();

// Sequence diagram showing query execution
string sequenceDiagram = sqlGenerator.GenerateSequenceDiagram(
    breakdown,
    "Query Execution Flow"
);

// Entity-relationship diagram
string erDiagram = sqlGenerator.GenerateEntityRelationshipDiagram(
    breakdown,
    "Database Schema"
);
```

### QueryBreakdownCollectionGenerator

Generate visualizations for collections of queries:

```csharp
using Strata.SqlTools.Markdown.PostgreSql;

var collection = new QueryBreakdownCollection();
// ... add queries ...

var collectionGenerator = new QueryBreakdownCollectionGenerator();

// Generate summary with all query diagrams
string summary = collectionGenerator.GenerateCollectionSummary(
    collection,
    "Database Queries"
);

// Generate parameter usage visualization
string paramDiagram = collectionGenerator.GenerateParameterUsageDiagram(
    collection,
    "Parameter Analysis"
);

// Generate table reference diagram
string tableDiagram = collectionGenerator.GenerateTableReferenceDiagram(
    collection,
    "Table Dependencies"
);
```

---

## Statement Parsing

### StatementParser

Utilities for normalizing and cleaning SQL statements:

```csharp
using Strata.SqlTools.Statements.PostgreSql;

string sql = @"
    -- This is a comment
    SELECT /* inline comment */ id, name
    FROM users
    WHERE age > 21;
";

// Remove comments
string cleaned = StatementParser.RemoveComments(sql);

// Normalize whitespace
string normalized = StatementParser.NormalizeWhitespace(sql);
```

### StatementReader

Token-based SQL parsing:

```csharp
using Strata.SqlTools.Statements.PostgreSql;
using Strata.SqlTools.Enums.SQL;

var reader = new StatementReader(sql);

while (reader.Read())
{
    Console.WriteLine($"Token: {reader.TokenType}, Value: '{reader.TokenValue}'");
}
```

**Example Output:**
```
Token: Keyword, Value: 'SELECT'
Token: Identifier, Value: 'id'
Token: Symbol, Value: ','
Token: Identifier, Value: 'name'
Token: Keyword, Value: 'FROM'
Token: Identifier, Value: 'users'
...
```

---

## Advanced Usage

### Building Queries Programmatically

```csharp
var breakdown = new QueryBreakdown("*", "users");

// Add WHERE clauses
breakdown.AddWhereClause("age > $1");
breakdown.AddWhereClause("is_active = $2", "AND");

// Add ORDER BY
breakdown.OrderByClause = "name ASC, created_date DESC";

// Add GROUP BY
breakdown.GroupByClause = "department";
breakdown.HavingClause = "COUNT(*) > 5";

// Add parameters
breakdown.AddParameter("$1", 21);
breakdown.AddParameter("$2", true);

// Generate SQL
string sql = breakdown.GetSql();
Console.WriteLine(sql);
```

**Output:**
```sql
SELECT *
FROM users
WHERE age > $1 AND is_active = $2
GROUP BY department
HAVING COUNT(*) > 5
ORDER BY name ASC, created_date DESC
```

### Cloning and Modifying Queries

```csharp
var original = new QueryBreakdown(@"
    SELECT * FROM users WHERE age > $1
");

// Clone the query
var clone = (QueryBreakdown)original.Clone();

// Modify the clone
clone.AddWhereClause("email IS NOT NULL", "AND");
clone.SelectClause = "id, name, email";

// Original remains unchanged
Console.WriteLine(original.GetSql());
Console.WriteLine(clone.GetSql());
```

### Merging Queries

```csharp
var query1 = new QueryBreakdown("id, name", "users");
query1.AddWhereClause("age > $1");

var query2 = new QueryBreakdown("*", "users");
query2.AddWhereClause("is_active = $1");

// Merge query2 into query1
query1.Merge(query2);

// Result includes WHERE clauses from both
Console.WriteLine(query1.GetSql());
```

---

## Common Table Expressions (CTEs)

PostgreSQL supports WITH clauses:

```csharp
var mainQuery = new QueryBreakdown("*", "filtered_users");

// Define a CTE
var cteQuery = new QueryBreakdown("id, name, age", "users");
cteQuery.AddWhereClause("age >= $1");

// Add CTE to main query
mainQuery.AddWithClause("filtered_users", cteQuery);

// Generate SQL
string sql = mainQuery.GetSql();
Console.WriteLine(sql);
```

**Output:**
```sql
WITH filtered_users AS (
    SELECT id, name, age
    FROM users
    WHERE age >= $1
)
SELECT *
FROM filtered_users
```

---

## Parameter Best Practices

### 1. Use Positional Parameters for Simple Queries

```csharp
// Good: Simple, sequential positional parameters
var query = new QueryBreakdown(@"
    SELECT * FROM users
    WHERE age > $1 AND department = $2
");
query.AddParameter("$1", 21);
query.AddParameter("$2", "Engineering");
```

### 2. Use Named Parameters for Complex Queries

```csharp
// Good: Named parameters for clarity
var query = new QueryBreakdown(@"
    SELECT * FROM orders
    WHERE customer_id = :customer_id
      AND order_date BETWEEN :start_date AND :end_date
      AND status = :status
");

query.AddParameter(":customer_id", customerId);
query.AddParameter(":start_date", startDate);
query.AddParameter(":end_date", endDate);
query.AddParameter(":status", "Pending");
```

### 3. Validate Parameter Count

```csharp
var breakdown = new QueryBreakdown(sql);
var parameters = breakdown.GetParameters();

// Ensure all parameters have values
foreach (var param in parameters)
{
    if (!breakdown.GetParameterDictionary().ContainsKey(param.Name))
    {
        throw new InvalidOperationException($"Missing value for parameter: {param.Name}");
    }
}
```

---

## Testing

### Unit Testing with PostgreSQL Queries

```csharp
[Test]
public void QueryBreakdown_PostgreSqlSyntax_ParsesCorrectly()
{
    var sql = @"
        SELECT id, name
        FROM users
        WHERE age > $1
          AND status = $2
    ";

    var breakdown = new QueryBreakdown(sql);

    Assert.That(breakdown.SelectClause, Is.EqualTo("id, name"));
    Assert.That(breakdown.FromClause, Is.EqualTo("users"));
    Assert.That(breakdown.WhereClause, Does.Contain("$1"));
    Assert.That(breakdown.WhereClause, Does.Contain("$2"));
}

[Test]
public void ParameterUsageReport_MultipleQueries_CountsCorrectly()
{
    var collection = new QueryBreakdownCollection();
    collection.Add(new QueryBreakdown("SELECT * FROM users WHERE id = $1"));
    collection.Add(new QueryBreakdown("SELECT * FROM orders WHERE user_id = $1"));

    var report = collection.GetParameterUsageReport();

    Assert.That(report.TotalQueries, Is.EqualTo(2));
    Assert.That(report.PositionalParameterUsage["$1"], Is.EqualTo(2));
}
```

---

## PostgreSQL-Specific SQL Features

### Array Support

```csharp
var breakdown = new QueryBreakdown(@"
    SELECT * FROM users
    WHERE tags && $1::text[]
");

breakdown.AddParameter("$1", new[] { "admin", "moderator" });
```

### JSON/JSONB Operators

```csharp
var breakdown = new QueryBreakdown(@"
    SELECT data->'name' as name
    FROM documents
    WHERE data @> $1::jsonb
");

breakdown.AddParameter("$1", "{\"status\": \"active\"}");
```

### RETURNING Clause

```csharp
// INSERT with RETURNING
var breakdown = new QueryBreakdown(@"
    INSERT INTO users (name, email)
    VALUES ($1, $2)
    RETURNING id, created_at
");

breakdown.AddParameter("$1", "John Doe");
breakdown.AddParameter("$2", "john@example.com");
```

---

## Related Documentation

- [SqlUtilities.Core.md](SqlUtilities.Core.md) - Core library documentation
- [SqlUtilities.SqlServer.md](SqlUtilities.SqlServer.md) - SQL Server comparison
- [SqlUtilities.Snowflake.md](SqlUtilities.Snowflake.md) - Snowflake comparison

---

## API Reference

### Key Classes

| Class | Purpose |
|-------|---------|
| `QueryBreakdown` | Parse and manipulate PostgreSQL SELECT queries |
| `QueryBreakdownCollection` | Manage collections of queries with analysis |
| `StatementParser` | SQL parsing utilities |
| `StatementReader` | Token-based SQL reader |
| `StatementExpressionParser` | Parse SQL into expression trees |

### Namespaces

- `Strata.SqlTools.Breakdowns.PostgreSql` - Query breakdown classes
- `Strata.SqlTools.Statements.PostgreSql` - Statement parsing
- `Strata.SqlTools.Visitors.PostgreSql` - SQL visitor patterns
- `Strata.SqlTools.ExpressionFactory.PostgreSql` - Expression factories
- `Strata.SqlTools.Markdown.PostgreSql` - Markdown generators

---

**Version**: 1.0.0
**Last Updated**: February 2026
**Package**: Strata.SqlTools.PostgreSql