Build Efficient SQL Functions Across Databases

This skill develops efficient, maintainable, performant SQL functions across PostgreSQL, SQL Server, MySQL, and Oracle. It distinguishes four function types - scalar (single-value calculations), table-valued (parameterized views returning result sets), aggregate (summarizing multiple rows), and window (calculations across related rows) - and applies performance principles: minimizing function calls in WHERE/JOIN conditions, using IMMUTABLE/DETERMINISTIC where applicable, preferring set-based operations over cursors and loops, and inlining simple functions for better execution plans.

For PostgreSQL it covers scalar functions with exception handling (e.g. a calculate_age function marked IMMUTABLE that returns NULL on error) and table-valued functions built on recursive CTEs (e.g. a sales-hierarchy function walking a manager/employee tree to a configurable depth limit). For SQL Server it covers inline table-valued functions with SCHEMABINDING (preferred for performance, as in a product-by-category filter) and multi-statement functions implementing business logic (e.g. a customer-tier discount calculator branching PLATINUM/GOLD/SILVER/BRONZE tiers by yearly order total, applying 15%/10%/5%/0% discounts).

CREATE OR REPLACE FUNCTION safe_division(
    numerator NUMERIC,
    denominator NUMERIC,
    default_value NUMERIC DEFAULT 0
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    result NUMERIC;
BEGIN
    IF denominator = 0 OR denominator IS NULL THEN
        INSERT INTO function_logs (function_name, message, created_at)
        VALUES ('safe_division', 'Division by zero attempted', NOW());
        RETURN default_value;
    END IF;
    
    result := numerator / denominator;
    RETURN result;
    
EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO function_logs (function_name, message, error_code, created_at)
        VALUES ('safe_division', SQLERRM, SQLSTATE, NOW());
        RETURN default_value;
END;
$$;

For advanced patterns it covers dynamic SQL with security (using SECURITY DEFINER, validating a table name against a hardcoded whitelist before building a parameterized query with format() and EXECUTE ... USING) and error handling with logging to a function_logs table, as in the safe-division example above.

Use it when writing or reviewing SQL functions for calculations, parameterized views, aggregations, or window calculations, needing platform-correct patterns (PostgreSQL plpgsql/IMMUTABLE vs. SQL Server SCHEMABINDING/inline table-valued functions), safe dynamic SQL, or robust error handling and logging.

Best practices to follow: use RETURNS TABLE over multi-statement table functions in SQL Server, add SCHEMABINDING where possible, specify IMMUTABLE/STABLE/VOLATILE correctly in PostgreSQL, avoid SELECT * in function definitions, use SECURITY DEFINER judiciously with full input validation, use parameterized queries for any dynamic SQL, document parameter constraints, version-control function changes with migration scripts, and abstract database-specific features behind consistent interfaces for cross-platform deployments.

Input is a function requirement (calculation, parameterized view, aggregation, or dynamic query) and target database platform. Output is a complete function definition in that platform's dialect - PostgreSQL plpgsql with appropriate volatility markers and exception blocks, or SQL Server T-SQL with SCHEMABINDING and business logic - including input validation, error handling, and logging where relevant.

Database developers writing SQL functions who need platform-correct, performant, and secure patterns across PostgreSQL, SQL Server, MySQL, or Oracle, including safe dynamic SQL and structured error handling.