Advanced SQL function support for Selecto.

This module extends the existing function support in Selecto.Builder.Sql.Select with additional advanced SQL functions including window functions, array operations, string manipulation, and mathematical functions.

function-categories

Function Categories

string-functions

String Functions

• substr/3 - Extract substring
• trim/1, ltrim/1, rtrim/1 - String trimming
• upper/1, lower/1 - Case conversion
• length/1 - String length
• position/2 - Find substring position
• replace/3 - String replacement
• split_part/3 - Split and extract part

mathematical-functions

Mathematical Functions

• abs/1 - Absolute value
• ceil/1, floor/1 - Rounding functions
• round/1, round/2 - Rounding with precision
• power/2 - Exponentiation
• sqrt/1 - Square root
• mod/2 - Modulo operation
• random/0 - Random number generation

date-time-functions

Date/Time Functions

• now/0 - Current timestamp
• date_trunc/2 - Truncate to date part
• interval/1 - Time intervals
• age/1, age/2 - Date arithmetic
• date_part/2 - Enhanced extract functionality

array-functions

Array Functions

• array_agg/1 - Array aggregation
• array_length/1 - Array length
• array_to_string/2 - Array to string conversion
• string_to_array/2 - String to array conversion
• unnest/1 - Array expansion
• array_cat/2 - Array concatenation

window-functions

Window Functions

• row_number/0 - Row numbering
• rank/0 - Ranking with gaps
• dense_rank/0 - Dense ranking
• lag/1, lag/2 - Previous row values
• lead/1, lead/2 - Next row values
• first_value/1, last_value/1 - Window boundaries
• ntile/1 - Percentile groups

conditional-functions

Conditional Functions

• Enhanced case expressions
• decode/3+ - Oracle-style conditional
• iif/3 - Simple if-then-else

usage-examples

Usage Examples

# String functions
{:substr, "description", 1, 50}
{:trim, "name"}
{:upper, "category"}

# Math functions
{:round, "price", 2}
{:power, "base", 2}

# Window functions
{:window, {:row_number}, over: [partition_by: ["category"], order_by: ["price"]]}
{:window, {:lag, "price"}, over: [partition_by: ["product_id"], order_by: ["date"]]}

# Array functions
{:array_agg, "tag_name", over: [partition_by: ["product_id"]]}
{:unnest, "tags"}