Functions

The Function class in Weasel.Postgresql.Functions represents a PL/pgSQL (or SQL) function as a schema object with full delta detection support.

Creating a Function

The simplest way to define a function is with Function.ForSql(), which parses the identifier from the SQL body:

var function = Function.ForSql(@"
CREATE OR REPLACE FUNCTION public.calculate_tax(amount decimal, rate decimal)
RETURNS decimal
LANGUAGE sql
AS $$
SELECT amount * rate;
$$;
");

^{snippet source | anchor}

Or construct directly with an identifier and body:

var function = new Function(
    new DbObjectName("public", "calculate_tax"),
    @"CREATE OR REPLACE FUNCTION public.calculate_tax(amount decimal, rate decimal)
RETURNS decimal
LANGUAGE sql
AS $$
SELECT amount * rate;
$$;"
);

^{snippet source | anchor}

Function Delta Detection

Weasel compares the expected function body against the actual definition stored in pg_proc to detect changes.

var dataSource = new NpgsqlDataSourceBuilder("Host=localhost;Database=mydb").Build();
var function = Function.ForSql(@"
CREATE OR REPLACE FUNCTION public.calculate_tax(amount decimal, rate decimal)
RETURNS decimal
LANGUAGE sql
AS $$
SELECT amount * rate;
$$;
");

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

// Fetch the existing function from the database
var existing = await function.FetchExistingAsync(conn);

// Compute the delta
var delta = await function.FindDeltaAsync(conn);
// delta.Difference: None, Create, or Update

^{snippet source | anchor}

The FunctionDelta class handles body comparison and generates the appropriate CREATE or DROP statements.

Removing a Function

Mark a function for removal during migration:

var removed = Function.ForRemoval("public.old_function");

^{snippet source | anchor}

Generating DDL

var function = Function.ForSql(@"
CREATE OR REPLACE FUNCTION public.calculate_tax(amount decimal, rate decimal)
RETURNS decimal
LANGUAGE sql
AS $$
SELECT amount * rate;
$$;
");

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

// CREATE FUNCTION statement
function.WriteCreateStatement(migrator, writer);

// DROP FUNCTION statement
function.WriteDropStatement(migrator, writer);

^{snippet source | anchor}

The drop statement is generated automatically from the function signature, including parameter types for proper overload resolution.

SQL Templates

Functions support template substitution for schema, function name, and signature placeholders:

var function = Function.ForSql(@"
CREATE OR REPLACE FUNCTION public.calculate_tax(amount decimal, rate decimal)
RETURNS decimal
LANGUAGE sql
AS $$
SELECT amount * rate;
$$;
");

string result = function.BuildTemplate(
    "GRANT EXECUTE ON FUNCTION {SIGNATURE} TO app_user;");

^{snippet source | anchor}

Available placeholders: {SCHEMA}, {FUNCTION}, {SIGNATURE}.