Not really sure how to approach array params, e.g. int[] for a function. Maybe we’re using the wrong method. Tried SqlList<T> and Select<T>
Example:
SELECT * FROM f_service_stack_function_int_array( int[] );
If you:
var sql = "SELECT * FROM f_service_stack_function_int_array(@paramValue)";
db.SqlList<MyFuncModel>(sql, new { paramValue = { 123, 456 } });
OrmLite expands the @paramValue array into individual integers so function call is:
// wrong
SELECT * FROM f_service_stack_function_int_array( integer, integer )
// should be
SELECT * FROM f_service_stack_function_int_array( int[] )
You could ARRAY[@paramValue], e.g.
var sql = "SELECT * FROM f_service_stack_function_int_array(ARRAY[@paramValue])";
db.SqlList<MyFuncModel>(sql, new { paramValue = { 123, 456 } });
You could also cast as array ARRAY[@paramValue]::int[], e.g.
var sql = "SELECT * FROM f_service_stack_function_int_array(ARRAY[@paramValue]::int[])";
db.SqlList<MyFuncModel>(sql, new { paramValue = { 123, 456 } });
HOWEVER it doesn’t work when the array param is null.
See tests below.
private const string DropFunctionArray = "DROP FUNCTION IF EXISTS f_service_stack_function_{0}_array({0});";
private const string WhitelistFuncAndVarNamePattern = "[^0-9a-zA-Z]+";
public class ServiceStackTypeFunctionResultIntArray
{
public int Id { get; set; }
// [CustomField("int[]")]
public int[] Val { get; set; }
}
[Test]
public void Can_execute_function_with_int_array_param()
{
using (var db = OpenDbConnection())
{
const string pgTypeToTest = "int[]";
// remove [] from type, for func name.
var pgFuncName = Regex.Replace(pgTypeToTest, WhitelistFuncAndVarNamePattern, "");
int[] testVal = { 123, 456 };
// if function already exists drop before create (can't change result)
db.ExecuteSql(DropFunctionArray.Fmt(pgFuncName, pgTypeToTest));
db.ExecuteSql(CreateFunctionArray.Fmt(pgFuncName, pgTypeToTest));
db.GetLastSql().Print();
// WORKS: ARRAY[@paramValue]::int[]
// var sql = "SELECT * FROM f_service_stack_function_{0}_array(ARRAY[@paramValue]::int[]);".Fmt(pgFuncName);
// WORKS 2: ARRAY[@paramValue] (no cast)
// var sql = "SELECT * FROM f_service_stack_function_{0}_array(ARRAY[@paramValue]);".Fmt(pgFuncName);
// Doesn't work: @paramValue
// MessageText: Npgsql.PostgresException : 42883: function f_service_stack_function_int_array(integer, integer) does not exist
var sql = "SELECT * FROM f_service_stack_function_{0}_array(@paramValue);".Fmt(pgFuncName);
var rows = db.SqlList<ServiceStackTypeFunctionResultIntArray>(
sql,
new
{
paramValue = testVal
});
db.GetLastSql().Print();
Assert.That(rows.Count, Is.EqualTo((10)));
Assert.That(rows[0].Val, Is.EqualTo(testVal));
db.ExecuteSql(DropFunctionArray.Fmt(pgFuncName, pgTypeToTest));
}
}
[Test]
public void Can_execute_function_with_int_array_param_as_null()
{
using (var db = OpenDbConnection())
{
const string pgTypeToTest = "int[]";
// remove [] from type, for func name.
var pgFuncName = Regex.Replace(pgTypeToTest, WhitelistFuncAndVarNamePattern, "");
int[] testVal = null;
// if function already exists drop before create (can't change result)
db.ExecuteSql(DropFunctionArray.Fmt(pgFuncName, pgTypeToTest));
db.ExecuteSql(CreateFunctionArray.Fmt(pgFuncName, pgTypeToTest));
db.GetLastSql().Print();
var sql = "SELECT * FROM f_service_stack_function_{0}_array(@paramValue);".Fmt(pgFuncName);
var rows = db.SqlList<ServiceStackTypeFunctionResultIntArray>(
sql,
new
{
paramValue = testVal
});
db.GetLastSql().Print();
Assert.That(rows.Count, Is.EqualTo((10)));
Assert.That(rows[0].Val, Is.Null);
db.ExecuteSql(DropFunctionArray.Fmt(pgFuncName, pgTypeToTest));
}
}
Makes it much easier! THANKS