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"); } }