Skip to content

Commit 23ad727

Browse files
bbowyersmythroji
bbowyersmyth
and
roji
authored
Support MERGE statement result count (#4562)
* Support MERGE command result count * More MERGE command types * Statement * Do nothing test * Update test/Npgsql.Tests/BatchTests.cs Co-authored-by: Shay Rojansky <roji@roji.org> * Add RecordsAffected test. Combined StatementType tests * Condition RecordsAffected test Co-authored-by: Shay Rojansky <roji@roji.org>
1 parent c9794af commit 23ad727

7 files changed

Lines changed: 82 additions & 7 deletions

File tree

‎src/Npgsql/BackendMessages/CommandCompleteMessage.cs‎

Lines changed: 12 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -54,10 +54,18 @@ internal CommandCompleteMessage Load(NpgsqlReadBuffer buf, int len)
5454
return this;
5555

5656
case (byte)'M':
57-
if (!AreEqual(bytes, i, "MOVE "))
57+
if (AreEqual(bytes, i, "MERGE "))
58+
{
59+
StatementType = StatementType.Merge;
60+
i += 6;
61+
}
62+
else if (AreEqual(bytes, i, "MOVE "))
63+
{
64+
StatementType = StatementType.Move;
65+
i += 5;
66+
}
67+
else
5868
goto default;
59-
StatementType = StatementType.Move;
60-
i += 5;
6169
Rows = ParseNumber(bytes, ref i);
6270
return this;
6371

@@ -105,4 +113,4 @@ static ulong ParseNumber(byte[] bytes, ref int pos)
105113
}
106114

107115
public BackendMessageCode Code => BackendMessageCode.CommandComplete;
108-
}
116+
}

‎src/Npgsql/Common.cs‎

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -74,6 +74,7 @@ public enum StatementType
7474
Move,
7575
Fetch,
7676
Copy,
77-
Other
77+
Other,
78+
Merge
7879
#pragma warning restore 1591
79-
}
80+
}

‎src/Npgsql/NpgsqlBatchCommand.cs‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,7 @@ public override int RecordsAffected
5353
case StatementType.Delete:
5454
case StatementType.Copy:
5555
case StatementType.Move:
56+
case StatementType.Merge:
5657
return Rows > int.MaxValue
5758
? throw new OverflowException($"The number of records affected exceeds int.MaxValue. Use {nameof(Rows)}.")
5859
: (int)Rows;
@@ -226,4 +227,4 @@ internal void ApplyCommandComplete(CommandCompleteMessage msg)
226227
/// Returns the <see cref="CommandText"/>.
227228
/// </summary>
228229
public override string ToString() => CommandText;
229-
}
230+
}

‎src/Npgsql/NpgsqlDataReader.cs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -734,6 +734,7 @@ internal void ProcessMessage(IBackendMessage msg)
734734
case StatementType.Delete:
735735
case StatementType.Copy:
736736
case StatementType.Move:
737+
case StatementType.Merge:
737738
if (!_recordsAffected.HasValue)
738739
_recordsAffected = 0;
739740
_recordsAffected += completed.Rows;

‎src/Npgsql/PublicAPI.Shipped.txt‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1123,6 +1123,7 @@ Npgsql.StatementType.CreateTableAs = 5 -> Npgsql.StatementType
11231123
Npgsql.StatementType.Delete = 3 -> Npgsql.StatementType
11241124
Npgsql.StatementType.Fetch = 7 -> Npgsql.StatementType
11251125
Npgsql.StatementType.Insert = 2 -> Npgsql.StatementType
1126+
Npgsql.StatementType.Merge = 10 -> Npgsql.StatementType
11261127
Npgsql.StatementType.Move = 6 -> Npgsql.StatementType
11271128
Npgsql.StatementType.Other = 9 -> Npgsql.StatementType
11281129
Npgsql.StatementType.Select = 1 -> Npgsql.StatementType

‎test/Npgsql.Tests/BatchTests.cs‎

Lines changed: 54 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
using Npgsql.Tests.Support;
2+
using Npgsql.Util;
23
using NUnit.Framework;
34
using System;
45
using System.Collections.Generic;
@@ -136,6 +137,52 @@ public async Task RecordsAffected_and_Rows()
136137
Assert.That(command.Rows, Is.EqualTo(2));
137138
}
138139

140+
[Test]
141+
public async Task Merge_RecordsAffected_and_Rows()
142+
{
143+
await using var conn = await OpenConnectionAsync();
144+
145+
MinimumPgVersion(conn, "15.0", "MERGE statement was introduced in PostgreSQL 15");
146+
147+
await using var _ = await CreateTempTable(conn, "name TEXT", out var table);
148+
149+
await using var batch = new NpgsqlBatch(conn)
150+
{
151+
BatchCommands =
152+
{
153+
new($"INSERT INTO {table} (name) VALUES ('a'), ('b')"),
154+
new($"MERGE INTO {table} S USING (SELECT 'b' as name) T ON T.name = S.name WHEN MATCHED THEN UPDATE SET name = 'c'"),
155+
new($"MERGE INTO {table} S USING (SELECT 'b' as name) T ON T.name = S.name WHEN NOT MATCHED THEN INSERT (name) VALUES ('b')"),
156+
new($"MERGE INTO {table} S USING (SELECT 'b' as name) T ON T.name = S.name WHEN MATCHED THEN DELETE"),
157+
new($"MERGE INTO {table} S USING (SELECT 'b' as name) T ON T.name = S.name WHEN NOT MATCHED THEN DO NOTHING")
158+
}
159+
};
160+
await using var reader = await batch.ExecuteReaderAsync(Behavior);
161+
162+
// Consume MERGE result set to parse the CommandComplete
163+
await reader.CloseAsync();
164+
165+
var command = batch.BatchCommands[0];
166+
Assert.That(command.RecordsAffected, Is.EqualTo(2));
167+
Assert.That(command.Rows, Is.EqualTo(2));
168+
169+
command = batch.BatchCommands[1];
170+
Assert.That(command.RecordsAffected, Is.EqualTo(1));
171+
Assert.That(command.Rows, Is.EqualTo(1));
172+
173+
command = batch.BatchCommands[2];
174+
Assert.That(command.RecordsAffected, Is.EqualTo(1));
175+
Assert.That(command.Rows, Is.EqualTo(1));
176+
177+
command = batch.BatchCommands[3];
178+
Assert.That(command.RecordsAffected, Is.EqualTo(1));
179+
Assert.That(command.Rows, Is.EqualTo(1));
180+
181+
command = batch.BatchCommands[4];
182+
Assert.That(command.RecordsAffected, Is.EqualTo(0));
183+
Assert.That(command.Rows, Is.EqualTo(0));
184+
}
185+
139186
[Test]
140187
public async Task NpgsqlBatchCommand_StatementType()
141188
{
@@ -155,6 +202,10 @@ public async Task NpgsqlBatchCommand_StatementType()
155202
new("COMMIT")
156203
}
157204
};
205+
206+
if (conn.PostgreSqlVersion.IsGreaterOrEqual(15))
207+
batch.BatchCommands.Add(new($"MERGE INTO {table} S USING (SELECT 'b' as name) T ON T.name = S.name WHEN NOT MATCHED THEN DO NOTHING"));
208+
158209
await using var reader = await batch.ExecuteReaderAsync(Behavior);
159210

160211
// Consume SELECT result set to parse the CommandComplete
@@ -167,6 +218,9 @@ public async Task NpgsqlBatchCommand_StatementType()
167218
Assert.That(batch.BatchCommands[4].StatementType, Is.EqualTo(StatementType.Select));
168219
Assert.That(batch.BatchCommands[5].StatementType, Is.EqualTo(StatementType.Delete));
169220
Assert.That(batch.BatchCommands[6].StatementType, Is.EqualTo(StatementType.Other));
221+
222+
if (conn.PostgreSqlVersion.IsGreaterOrEqual(15))
223+
Assert.That(batch.BatchCommands[7].StatementType, Is.EqualTo(StatementType.Merge));
170224
}
171225

172226
[Test]

‎test/Npgsql.Tests/ReaderTests.cs‎

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -14,6 +14,7 @@
1414
using Npgsql.PostgresTypes;
1515
using Npgsql.Tests.Support;
1616
using Npgsql.TypeMapping;
17+
using Npgsql.Util;
1718
using NpgsqlTypes;
1819
using NUnit.Framework;
1920
using static Npgsql.Tests.TestUtil;
@@ -151,6 +152,14 @@ public async Task RecordsAffected()
151152
reader = await cmd.ExecuteReaderAsync(Behavior);
152153
reader.Close();
153154
Assert.That(reader.RecordsAffected, Is.EqualTo(4));
155+
156+
if (conn.PostgreSqlVersion.IsGreaterOrEqual(15))
157+
{
158+
cmd = new NpgsqlCommand($"MERGE INTO {table} S USING (SELECT 2 as int) T ON T.int = S.int WHEN MATCHED THEN UPDATE SET int = S.int", conn);
159+
reader = await cmd.ExecuteReaderAsync(Behavior);
160+
reader.Close();
161+
Assert.That(reader.RecordsAffected, Is.EqualTo(1));
162+
}
154163
}
155164

156165
#pragma warning disable CS0618

0 commit comments

Comments
 (0)