tests/test_tablefunction.ps1
|
# Test CreateTableFunction implementation (table-valued UDFs) # Tests no-parameter and parameterized table functions # Load module from parent directory Import-Module PaperinikDB -Force $conn = New-DuckDBConnection -Path ':MEMORY:' $conn.Open() Write-Host 'Connection opened' -ForegroundColor Green Write-Host "`n=== Test 1: Simple table function with static data ===" -ForegroundColor Cyan # Use ordered hashtable to preserve column order $conn.CreateTableFunction( 'get_users', { @( [PSCustomObject]@{ id = 1; name = 'Alice'; active = $true } [PSCustomObject]@{ id = 2; name = 'Bob'; active = $false } [PSCustomObject]@{ id = 3; name = 'Charlie'; active = $true } ) }, [ordered]@{ 'id' = [int]; 'name' = [string]; 'active' = [bool] } ) Write-Host "Table function 'get_users' registered" -ForegroundColor Green $result = $conn.sql("SELECT * FROM get_users()") Write-Host "All users:" $result | Format-Table -AutoSize $result = $conn.sql("SELECT name FROM get_users() WHERE active = true") Write-Host "Active users:" $result | Format-Table -AutoSize Write-Host "`n=== Test 2: Table function returning environment variables ===" -ForegroundColor Cyan $conn.CreateTableFunction( 'get_env_vars', { [System.Environment]::GetEnvironmentVariables().GetEnumerator() | ForEach-Object { [PSCustomObject]@{ name = [string]$_.Key; value = [string]$_.Value } } }, [ordered]@{ 'name' = [string]; 'value' = [string] } ) Write-Host "Table function 'get_env_vars' registered" -ForegroundColor Green $result = $conn.sql("SELECT * FROM get_env_vars() WHERE name LIKE '%USER%' LIMIT 5") Write-Host "Environment variables containing 'USER':" $result | Format-Table -AutoSize Write-Host "`n=== Test 3: Table function with parameter ===" -ForegroundColor Cyan $conn.CreateTableFunction( 'range_table', { param([int]$n) 1..$n | ForEach-Object { [PSCustomObject]@{ num = $_; squared = $_ * $_; is_even = ($_ % 2) -eq 0 } } }, [ordered]@{ 'num' = [int]; 'squared' = [int]; 'is_even' = [bool] }, @([int]) ) Write-Host "Table function 'range_table' registered" -ForegroundColor Green $result = $conn.sql("SELECT * FROM range_table(5)") Write-Host "Numbers 1-5 with squares:" $result | Format-Table -AutoSize $result = $conn.sql("SELECT num, squared FROM range_table(10) WHERE is_even = true") Write-Host "Even numbers 1-10:" $result | Format-Table -AutoSize Write-Host "`n=== All tests completed! ===" -ForegroundColor Green $conn.CloseDB() |