| public static MinimumPgVersion ( NpgsqlConnection conn, string minVersion, string ignoreText = null ) : void | ||
| conn | NpgsqlConnection | |
| minVersion | string | |
| ignoreText | string | |
| return | void | |
public void DataTypeName_is_populated()
{
// On reading the source code for PostgreSQL9.3beta1, the only time that the
// datatypename field is populated is when using domain types. So here we'll
// create a domain that simply does not allow NULLs then try and cast NULL
// to it.
const string dropDomain = @"DROP DOMAIN IF EXISTS public.intnotnull";
const string createDomain = @"CREATE DOMAIN public.intnotnull AS INT NOT NULL";
const string castStatement = @"SELECT CAST(NULL AS public.intnotnull)";
using var conn = OpenConnection();
TestUtil.MinimumPgVersion(conn, "9.3.0", "5 error fields haven't been added yet");
try
{
var command = new NpgsqlCommand(dropDomain, conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(createDomain, conn);
command.ExecuteNonQuery();
command = new NpgsqlCommand(castStatement, conn);
//Cause the NOT NULL violation
command.ExecuteNonQuery();
}
catch (PostgresException ex)
{
Assert.AreEqual("public", ex.SchemaName);
Assert.AreEqual("intnotnull", ex.DataTypeName);
}
}
public void TableParameters()
{
using (var conn = OpenConnection())
{
TestUtil.MinimumPgVersion(conn, "9.2.0");
// This function returns record because of the two Out (InOut & Out) parameters
conn.ExecuteNonQuery(@"
CREATE FUNCTION pg_temp.func(IN in1 INT) RETURNS TABLE(t1 INT, t2 INT) AS
'SELECT in1,in1+1' LANGUAGE 'sql';
");
var cmd = new NpgsqlCommand("pg_temp.func", conn)
{
CommandType = CommandType.StoredProcedure
};
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.Output));
cmd.Parameters[0].Value = 5;
cmd.ExecuteNonQuery();
Assert.That(cmd.Parameters[1].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(6));
}
}
public void DeriveParametersFunctionReturningSetofRecord()
{
using (var conn = OpenConnection())
{
TestUtil.MinimumPgVersion(conn, "9.2.0");
// This function returns record because of the two Out (InOut & Out) parameters
conn.ExecuteNonQuery(@"
CREATE TABLE pg_temp.foo (fooid int, foosubid int, fooname text);
INSERT INTO pg_temp.foo VALUES
(1, 1, 'Joe'),
(1, 2, 'Ed'),
(2, 1, 'Mary');
CREATE FUNCTION pg_temp.getfoo(int, OUT fooid int, OUT foosubid int, OUT fooname text) RETURNS SETOF record AS $$
SELECT * FROM pg_temp.foo WHERE pg_temp.foo.fooid = $1 ORDER BY pg_temp.foo.foosubid;
$$ LANGUAGE SQL;
");
var cmd = new NpgsqlCommand("pg_temp.getfoo", conn)
{
CommandType = CommandType.StoredProcedure
};
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(4));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[3].Direction, Is.EqualTo(ParameterDirection.Output));
cmd.Parameters[0].Value = 1;
cmd.ExecuteNonQuery();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(1));
}
}
public void NamedParameters()
{
using (var conn = OpenConnection())
{
TestUtil.MinimumPgVersion(conn, "9.4.0", "make_timestamp was introduced in 9.4");
using (var command = new NpgsqlCommand("make_timestamp", conn))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("year", 2015);
command.Parameters.AddWithValue("month", 8);
command.Parameters.AddWithValue("mday", 1);
command.Parameters.AddWithValue("hour", 2);
command.Parameters.AddWithValue("min", 3);
command.Parameters.AddWithValue("sec", 4);
var dt = (DateTime)command.ExecuteScalar();
Assert.AreEqual(new DateTime(2015, 8, 1, 2, 3, 4), dt);
command.Parameters[0].Value = 2014;
command.Parameters[0].ParameterName = ""; // 2014 will be sent as a positional parameter
dt = (DateTime)command.ExecuteScalar();
Assert.AreEqual(new DateTime(2014, 8, 1, 2, 3, 4), dt);
}
}
}
public void DeriveFunctionParameters_FunctionReturningSetofTypeWithDroppedColumn()
{
using (var conn = OpenConnection())
{
TestUtil.MinimumPgVersion(conn, "9.2.0");
conn.ExecuteNonQuery(@"
CREATE TABLE pg_temp.test (id serial PRIMARY KEY, t1 text, t2 text);
CREATE FUNCTION pg_temp.test_func() RETURNS SETOF test AS $$
SELECT * FROM test
$$LANGUAGE SQL;
ALTER TABLE test DROP t2;
");
var cmd = new NpgsqlCommand("pg_temp.test_func", conn)
{
CommandType = CommandType.StoredProcedure
};
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
}
}
public void DeriveTextCommandParameters_Domain()
{
using (var conn = OpenConnection())
{
TestUtil.MinimumPgVersion(conn, "11.0", "Arrays of domains and domains over arrays were introduced in PostgreSQL 11");
conn.ExecuteNonQuery("CREATE DOMAIN pg_temp.posint AS integer CHECK (VALUE > 0);" +
"CREATE DOMAIN pg_temp.int_array AS int[] CHECK(array_length(VALUE, 1) = 2);");
conn.ReloadTypes();
var cmd = new NpgsqlCommand("SELECT :a::posint, :b::posint[], :c::int_array", conn);
var val = 23;
var arrayVal = new[] { 7, 42 };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(3));
Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("a"));
Assert.That(cmd.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(cmd.Parameters[0].DataTypeName, Does.EndWith("posint"));
Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("b"));
Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer | NpgsqlDbType.Array));
Assert.That(cmd.Parameters[1].DataTypeName, Does.EndWith("posint[]"));
Assert.That(cmd.Parameters[2].ParameterName, Is.EqualTo("c"));
Assert.That(cmd.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer | NpgsqlDbType.Array));
Assert.That(cmd.Parameters[2].DataTypeName, Does.EndWith("int_array"));
cmd.Parameters[0].Value = val;
cmd.Parameters[1].Value = arrayVal;
cmd.Parameters[2].Value = arrayVal;
using (var reader = cmd.ExecuteRecord())
{
Assert.That(reader.GetFieldValue <int>(0), Is.EqualTo(val));
Assert.That(reader.GetFieldValue <int[]>(1), Is.EqualTo(arrayVal));
Assert.That(reader.GetFieldValue <int[]>(2), Is.EqualTo(arrayVal));
}
}
}
public void Void()
{
using var conn = OpenConnection();
TestUtil.MinimumPgVersion(conn, "9.1.0", "no binary output function available for type void before 9.1.0");
var command = new NpgsqlCommand("pg_sleep", conn);
command.Parameters.AddWithValue(0);
command.CommandType = CommandType.StoredProcedure;
command.ExecuteNonQuery();
}
public void Column_name_exception_field_is_populated()
{
using var conn = OpenConnection();
TestUtil.MinimumPgVersion(conn, "9.3.0", "5 error fields haven't been added yet");
conn.ExecuteNonQuery("CREATE TEMP TABLE notnullviolation (id INT NOT NULL)");
try
{
conn.ExecuteNonQuery("INSERT INTO notnullviolation (id) VALUES(NULL)");
}
catch (PostgresException ex)
{
Assert.That(ex.SchemaName, Does.StartWith("pg_temp"));
Assert.That(ex.TableName, Is.EqualTo("notnullviolation"));
Assert.That(ex.ColumnName, Is.EqualTo("id"));
}
}
public void NpgsqlDbTypeExtension()
{
using (var conn = OpenConnection())
{
TestUtil.MinimumPgVersion(conn, "9.1.0", "HSTORE data type not yet introduced");
conn.ExecuteNonQuery(@"CREATE EXTENSION IF NOT EXISTS hstore");
conn.ReloadTypes();
using (var cmd = new NpgsqlCommand("SELECT NULL::HSTORE", conn))
using (var reader = cmd.ExecuteReader(CommandBehavior.SchemaOnly))
{
var columns = reader.GetColumnSchema();
// The full datatype name for PostGIS is public.geometry (unlike int4 which is in pg_catalog).
Assert.That(columns[0].NpgsqlDbType, Is.EqualTo(NpgsqlTypes.NpgsqlDbType.Hstore));
}
}
}
public async Task Multirange()
{
await using (var conn = await OpenConnectionAsync())
TestUtil.MinimumPgVersion(conn, "14.0", "Multirange types were introduced in PostgreSQL 14");
var databaseInfo = await GetDatabaseInfo();
var intMultirange = databaseInfo.MultirangeTypes.Single(a => a.Name == "int4multirange");
Assert.That(intMultirange.DisplayName, Is.EqualTo("int4multirange"));
Assert.That(intMultirange.Namespace, Is.EqualTo("pg_catalog"));
Assert.That(intMultirange.FullName, Is.EqualTo("pg_catalog.int4multirange"));
var intRange = databaseInfo.RangeTypes.Single(a => a.Name == "int4range");
Assert.That(intMultirange.Subrange, Is.SameAs(intRange));
Assert.That(intRange.Multirange, Is.SameAs(intMultirange));
}
public void Exception_fields_are_populated()
{
using var conn = OpenConnection();
TestUtil.MinimumPgVersion(conn, "9.3.0", "5 error fields haven't been added yet");
conn.ExecuteNonQuery("CREATE TEMP TABLE uniqueviolation (id INT NOT NULL, CONSTRAINT uniqueviolation_pkey PRIMARY KEY (id))");
conn.ExecuteNonQuery("INSERT INTO uniqueviolation (id) VALUES(1)");
try
{
conn.ExecuteNonQuery("INSERT INTO uniqueviolation (id) VALUES(1)");
}
catch (PostgresException ex)
{
Assert.That(ex.ColumnName, Is.Null, "ColumnName should not be populated for unique violations");
Assert.That(ex.TableName, Is.EqualTo("uniqueviolation"));
Assert.That(ex.SchemaName, Does.StartWith("pg_temp"));
Assert.That(ex.ConstraintName, Is.EqualTo("uniqueviolation_pkey"));
Assert.That(ex.DataTypeName, Is.Null, "DataTypeName should not be populated for unique violations");
}
}
