public void TestInvaildAliasExprUsage(
[IncludeDataSources(TestProvName.AllPostgreSQL)]
string context)
{
using (var db = GetDataContext(context))
{
var query =
from c in db.FromSql <int>($"select {1} {Sql.AliasExpr()}")
select c;
Assert.Throws <Npgsql.PostgresException>(() => query.ToArray());
}
}
public void TestUnnest(
// `with ordinality` added to pgsql 9.4
[IncludeDataSources(true, TestProvName.AllPostgreSQL95Plus)]
string context)
{
using (var db = GetDataContext(context))
{
var query =
from c in db.SelectQuery(() => Sql.Expr <int[]>("ARRAY[1,2]::int[]"))
from s in db.FromSql <UnnestEnvelope <int> >($"unnest({c}) with ordinality {Sql.AliasExpr()} (value, index)")
select s.Value;
var result = query.ToArray();
var expected = new [] { 1, 2 };
AreEqual(expected, result);
}
}
static Expression <Func <IDataContext, TValue[], IQueryable <UnnestEnvelope <TValue> > > > UnnestWithOrdinalityImpl <TValue>()
=> (db, member) => db.FromSql <UnnestEnvelope <TValue> >($"unnest({member}) with ordinality {Sql.AliasExpr()} (value, index)");
static IQueryable <UnnestEnvelope <TValue> > UnnestWithOrdinality <TValue>(IDataContext db, TValue[] member)
=> db.FromSql <UnnestEnvelope <TValue> >($"unnest({member}) with ordinality {Sql.AliasExpr()} (value, index)");
public void TestScalarSubquery(
[IncludeDataSources(true, TestProvName.AllPostgreSQL93Plus)]
string context)
{
using (var db = GetDataContext(context))
{
// ::text hint needed for pgsql < 10
var query =
from c in db.SelectQuery(() => "hello world")
from s in db.FromSql <string>($"regexp_split_to_table({c}::text, E'\\\\s+') {Sql.AliasExpr()}")
select s;
var result = query.ToArray();
var expected = new[] { "hello", "world" };
AreEqual(expected, result);
}
}
static Expression <Func <IDataContext, T[], IQueryable <Ordinality <T> > > > UnnestWithOrdinalityImpl <T>()
{
return((dc, array) => dc.FromSql <Ordinality <T> >($"UNNEST({array}) WITH ORDINALITY {Sql.AliasExpr()}(value, idx)"));
}