Views

Weasel supports both standard views and materialized views via the Weasel.Postgresql.Views namespace.

Standard Views

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE is_active = true");

^{snippet source | anchor}

Views can be placed in a specific schema:

var view = new View(
    new DbObjectName("reporting", "monthly_totals"),
    "SELECT date_trunc('month', created_at) AS month, SUM(amount) FROM orders GROUP BY 1");

^{snippet source | anchor}

Move a view to a different schema after creation:

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE is_active = true");
view.MoveToSchema("analytics");

^{snippet source | anchor}

Materialized Views

Materialized views store query results physically and support custom access methods.

var matView = new MaterializedView("product_stats",
    "SELECT product_id, COUNT(*) as order_count, SUM(amount) as total FROM orders GROUP BY product_id");

// Optionally specify a custom access method (e.g., columnar)
matView.UseAccessMethod("columnar");

^{snippet source | anchor}

Delta Detection

Weasel fetches the existing view definition from pg_catalog and compares it against the expected SQL. Since PostgreSQL does not support ALTER VIEW for changing the query, changes result in a drop-and-recreate.

var dataSource = new NpgsqlDataSourceBuilder("Host=localhost;Database=mydb").Build();
var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE is_active = true");

await using var conn = dataSource.CreateConnection();
await conn.OpenAsync();

// Check existence
bool exists = await view.ExistsInDatabaseAsync(conn);

^{snippet source | anchor}

When used within a PostgresqlDatabase, delta detection runs automatically during ApplyAllConfiguredChangesToDatabaseAsync().

Generating DDL

The WriteCreateStatement method emits both a DROP and CREATE statement to ensure idempotent application.

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE is_active = true");

var migrator = new PostgresqlMigrator();
var writer = new StringWriter();

view.WriteCreateStatement(migrator, writer);
// DROP VIEW IF EXISTS public.active_users;
// CREATE VIEW public.active_users AS SELECT id, name, email FROM users WHERE is_active = true;

^{snippet source | anchor}

You can also generate the SQL inline for diagnostics:

var view = new View("active_users",
    "SELECT id, name, email FROM users WHERE is_active = true");

string sql = view.ToBasicCreateViewSql();

^{snippet source | anchor}