forked from npgsql/npgsql
-
Notifications
You must be signed in to change notification settings - Fork 3
Expand file tree
/
Copy pathFunctionTests.cs
More file actions
570 lines (505 loc) · 26.7 KB
/
FunctionTests.cs
File metadata and controls
570 lines (505 loc) · 26.7 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
using System;
using System.Data;
using System.Threading.Tasks;
using Npgsql.PostgresTypes;
using NpgsqlTypes;
using NUnit.Framework;
using static Npgsql.Tests.TestUtil;
namespace Npgsql.Tests;
/// <summary>
/// A fixture for tests which interact with functions.
/// All tests should create functions in the pg_temp schema only to ensure there's no interaction between
/// the tests.
/// </summary>
[NonParallelizable] // Manipulates the EnableStoredProcedureCompatMode global flag
public class FunctionTests : TestBase
{
[Test, Description("Simple function with no parameters, results accessed as a resultset")]
public async Task Resultset()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync($"CREATE FUNCTION {function}() RETURNS integer AS 'SELECT 8' LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure };
Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(8));
}
[Test, Description("Basic function call with an in parameter")]
public async Task Param_Input()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync($"CREATE FUNCTION {function}(IN param text) RETURNS text AS 'SELECT param' LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param", "hello");
Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo("hello"));
}
[Test, Description("Basic function call with an out parameter")]
public async Task Param_Output()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync(@$"
CREATE FUNCTION {function} (IN param_in text, OUT param_out text) AS $$
BEGIN
param_out=param_in;
END
$$ LANGUAGE plpgsql");
await using var cmd = new NpgsqlCommand(function, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@param_in", "hello");
var outParam = new NpgsqlParameter("param_out", DbType.String) { Direction = ParameterDirection.Output };
cmd.Parameters.Add(outParam);
await cmd.ExecuteNonQueryAsync();
Assert.That(outParam.Value, Is.EqualTo("hello"));
}
[Test, Description("Basic function call with an in/out parameter")]
public async Task Param_InputOutput()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync($@"
CREATE FUNCTION {function} (INOUT param integer) AS $$
BEGIN
param=param+1;
END
$$ LANGUAGE plpgsql");
await using var cmd = new NpgsqlCommand(function, conn);
cmd.CommandType = CommandType.StoredProcedure;
var outParam = new NpgsqlParameter("param", DbType.Int32)
{
Direction = ParameterDirection.InputOutput,
Value = 8
};
cmd.Parameters.Add(outParam);
await cmd.ExecuteNonQueryAsync();
Assert.That(outParam.Value, Is.EqualTo(9));
}
[Test]
public async Task Void()
{
await using var conn = await OpenConnectionAsync();
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;
await command.ExecuteNonQueryAsync();
}
[Test]
public async Task Named_parameters()
{
await using var conn = await OpenConnectionAsync();
MinimumPgVersion(conn, "9.4.0", "make_timestamp was introduced in 9.4");
await 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)(await command.ExecuteScalarAsync())!;
Assert.That(dt, Is.EqualTo(new DateTime(2015, 8, 1, 2, 3, 4)));
command.Parameters[0].Value = 2014;
command.Parameters[0].ParameterName = ""; // 2014 will be sent as a positional parameter
dt = (DateTime)(await command.ExecuteScalarAsync())!;
Assert.That(dt, Is.EqualTo(new DateTime(2014, 8, 1, 2, 3, 4)));
}
[Test]
public async Task Too_many_output_params()
{
await using var conn = await OpenConnectionAsync();
var command = new NpgsqlCommand("VALUES (4,5), (6,7)", conn);
command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32)
{
Direction = ParameterDirection.Output,
Value = -1
});
command.Parameters.Add(new NpgsqlParameter("b", DbType.Int32)
{
Direction = ParameterDirection.Output,
Value = -1
});
command.Parameters.Add(new NpgsqlParameter("c", DbType.Int32)
{
Direction = ParameterDirection.Output,
Value = -1
});
await command.ExecuteNonQueryAsync();
Assert.That(command.Parameters["a"].Value, Is.EqualTo(4));
Assert.That(command.Parameters["b"].Value, Is.EqualTo(5));
Assert.That(command.Parameters["c"].Value, Is.EqualTo(-1));
}
[Test, IssueLink("https://github.com/npgsql/npgsql/issues/5793")]
public async Task ReturnValue_parameter_ignored()
{
await using var conn = await OpenConnectionAsync();
var funcName = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync(@$"CREATE FUNCTION {funcName}() RETURNS integer AS 'SELECT 8;' LANGUAGE 'sql'");
await using var cmd = new NpgsqlCommand(funcName, conn) { CommandType = CommandType.StoredProcedure };
var param = new NpgsqlParameter
{
ParameterName = "@ReturnValue",
NpgsqlDbType = NpgsqlDbType.Integer,
Direction = ParameterDirection.ReturnValue,
Value = 0
};
cmd.Parameters.Add(param);
Assert.That(cmd.ExecuteScalar(), Is.EqualTo(8));
Assert.That(param.Value, Is.EqualTo(0));
}
[Test]
public async Task CommandBehavior_SchemaOnly_support_function_call()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync($"CREATE OR REPLACE FUNCTION {function}() RETURNS SETOF integer as 'SELECT 1;' LANGUAGE 'sql';");
var command = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure };
await using var dr = await command.ExecuteReaderAsync(CommandBehavior.SchemaOnly);
var i = 0;
while (dr.Read())
i++;
Assert.That(i, Is.EqualTo(0));
}
[Test, IssueLink("https://github.com/npgsql/npgsql/issues/5820")]
public async Task Output_param_cast_error()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync(@$"
CREATE FUNCTION {function} (INOUT param_in int4, OUT param_out interval) AS $$
BEGIN
param_out = interval '5 years';
END
$$ LANGUAGE plpgsql");
await using var cmd = new NpgsqlCommand(function, conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("param_in", DbType.Int32)
{
Direction = ParameterDirection.InputOutput,
Value = 1
});
cmd.Parameters.Add(new NpgsqlParameter("param_out", NpgsqlDbType.Interval)
{
Direction = ParameterDirection.Output
});
Assert.ThrowsAsync<InvalidCastException>(cmd.ExecuteNonQueryAsync);
Assert.DoesNotThrowAsync(async () => await conn.ExecuteNonQueryAsync("SELECT 1"));
}
#region DeriveParameters
[Test, Description("Tests function parameter derivation with IN, OUT and INOUT parameters")]
public async Task DeriveParameters_function_various()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
// This function returns record because of the two Out (InOut & Out) parameters
await conn.ExecuteNonQueryAsync($@"
CREATE FUNCTION {function}(IN param1 INT, OUT param2 text, INOUT param3 INT) RETURNS record AS $$
BEGIN
param2 = 'sometext';
param3 = param1 + param3;
END;
$$ LANGUAGE plpgsql");
await using var cmd = new NpgsqlCommand(function, 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[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(cmd.Parameters[0].PostgresType, Is.TypeOf<PostgresBaseType>());
Assert.That(cmd.Parameters[0].DataTypeName, Is.EqualTo("integer"));
Assert.That(cmd.Parameters[0].ParameterName, Is.EqualTo("param1"));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Output));
Assert.That(cmd.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
Assert.That(cmd.Parameters[1].PostgresType, Is.TypeOf<PostgresBaseType>());
Assert.That(cmd.Parameters[1].DataTypeName, Is.EqualTo("text"));
Assert.That(cmd.Parameters[1].ParameterName, Is.EqualTo("param2"));
Assert.That(cmd.Parameters[2].Direction, Is.EqualTo(ParameterDirection.InputOutput));
Assert.That(cmd.Parameters[2].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(cmd.Parameters[2].PostgresType, Is.TypeOf<PostgresBaseType>());
Assert.That(cmd.Parameters[2].DataTypeName, Is.EqualTo("integer"));
Assert.That(cmd.Parameters[2].ParameterName, Is.EqualTo("param3"));
cmd.Parameters[0].Value = 5;
cmd.Parameters[2].Value = 4;
await cmd.ExecuteNonQueryAsync();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[1].Value, Is.EqualTo("sometext"));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(9));
}
[Test, Description("Tests function parameter derivation with IN-only parameters")]
public async Task DeriveParameters_function_in_only()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
// This function returns record because of the two Out (InOut & Out) parameters
await conn.ExecuteNonQueryAsync(
$@"CREATE FUNCTION {function}(IN param1 INT, IN param2 INT) RETURNS int AS 'SELECT param1 + param2' LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Has.Count.EqualTo(2));
Assert.That(cmd.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(cmd.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Input));
cmd.Parameters[0].Value = 5;
cmd.Parameters[1].Value = 4;
Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(9));
}
[Test, Description("Tests function parameter derivation with no parameters")]
public async Task DeriveParameters_function_no_params()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync($@"CREATE FUNCTION {function}() RETURNS int AS 'SELECT 4' LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(cmd);
Assert.That(cmd.Parameters, Is.Empty);
}
[Test]
public async Task DeriveParameters_function_with_case_sensitive_name()
{
await using var conn = await OpenConnectionAsync();
await conn.ExecuteNonQueryAsync(
@"CREATE OR REPLACE FUNCTION ""FunctionCaseSensitive""(int4, text) RETURNS int4 AS 'SELECT 0' LANGUAGE sql");
try
{
await using var command = new NpgsqlCommand(@"""FunctionCaseSensitive""", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(command);
Assert.That(command.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(command.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
}
finally
{
await conn.ExecuteNonQueryAsync(@"DROP FUNCTION ""FunctionCaseSensitive""");
}
}
[Test, Description("Tests function parameter derivation for quoted functions with double quotes in the name works")]
public async Task DeriveParameters_quote_characters_in_function_name()
{
await using var conn = await OpenConnectionAsync();
var function = @"""""""FunctionQuote""""CharactersInName""""""";
await conn.ExecuteNonQueryAsync($"CREATE OR REPLACE FUNCTION {function}(int4, text) RETURNS int4 AS 'SELECT 0' LANGUAGE sql");
try
{
await using var command = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(command);
Assert.That(command.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(command.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
}
finally
{
await conn.ExecuteNonQueryAsync("DROP FUNCTION " + function);
}
}
[Test, Description("Tests function parameter derivation for quoted functions with dots in the name works")]
public async Task DeriveParameters_dot_character_in_function_name()
{
await using var conn = await OpenConnectionAsync();
await conn.ExecuteNonQueryAsync(
@"CREATE OR REPLACE FUNCTION ""My.Dotted.Function""(int4, text) RETURNS int4 AS 'SELECT 0' LANGUAGE sql");
try
{
await using var command = new NpgsqlCommand(@"""My.Dotted.Function""", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(command);
Assert.That(command.Parameters[0].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Integer));
Assert.That(command.Parameters[1].NpgsqlDbType, Is.EqualTo(NpgsqlDbType.Text));
}
finally
{
await conn.ExecuteNonQueryAsync(@"DROP FUNCTION ""My.Dotted.Function""");
}
}
[Test]
public async Task DeriveParameters_parameter_name_from_function()
{
await using var conn = await OpenConnectionAsync();
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync(
$"CREATE FUNCTION {function}(x int, y int, out sum int, out product int) AS 'SELECT $1 + $2, $1 * $2' LANGUAGE sql");
await using var command = new NpgsqlCommand(function, conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(command);
Assert.That(command.Parameters[0].ParameterName, Is.EqualTo("x"));
Assert.That(command.Parameters[1].ParameterName, Is.EqualTo("y"));
}
[Test]
public async Task DeriveParameters_non_existing_function()
{
await using var conn = await OpenConnectionAsync();
var invalidCommandName = new NpgsqlCommand("invalidfunctionname", conn) { CommandType = CommandType.StoredProcedure };
Assert.That(() => NpgsqlCommandBuilder.DeriveParameters(invalidCommandName),
Throws.Exception.TypeOf<PostgresException>()
.With.Property(nameof(PostgresException.SqlState)).EqualTo(PostgresErrorCodes.UndefinedFunction));
}
[Test, IssueLink("https://github.com/npgsql/npgsql/issues/1212")]
public async Task DeriveParameters_function_with_table_parameters()
{
await using var conn = await OpenConnectionAsync();
MinimumPgVersion(conn, "9.2.0");
var function = await GetTempFunctionName(conn);
// This function returns record because of the two Out (InOut & Out) parameters
await conn.ExecuteNonQueryAsync(
$"CREATE FUNCTION {function}(IN in1 INT) RETURNS TABLE(t1 INT, t2 INT) AS 'SELECT in1, in1+1' LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, 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;
await cmd.ExecuteNonQueryAsync();
Assert.That(cmd.Parameters[1].Value, Is.EqualTo(5));
Assert.That(cmd.Parameters[2].Value, Is.EqualTo(6));
}
[Test, Description("Tests if the right function according to search_path is used in function parameter derivation")]
public async Task DeriveParameters_function_correct_schema_resolution()
{
await using var conn = await OpenConnectionAsync();
var schema1 = await CreateTempSchema(conn);
var schema2 = await CreateTempSchema(conn);
await conn.ExecuteNonQueryAsync($@"
CREATE FUNCTION {schema1}.redundantfunc() RETURNS int AS 'SELECT 1' LANGUAGE sql;
CREATE FUNCTION {schema2}.redundantfunc(IN param1 INT, IN param2 INT) RETURNS int AS 'SELECT param1 + param2' LANGUAGE sql;
SET search_path TO {schema2};");
await using var command = new NpgsqlCommand("redundantfunc", conn) { CommandType = CommandType.StoredProcedure };
NpgsqlCommandBuilder.DeriveParameters(command);
Assert.That(command.Parameters, Has.Count.EqualTo(2));
Assert.That(command.Parameters[0].Direction, Is.EqualTo(ParameterDirection.Input));
Assert.That(command.Parameters[1].Direction, Is.EqualTo(ParameterDirection.Input));
command.Parameters[0].Value = 5;
command.Parameters[1].Value = 4;
Assert.That(await command.ExecuteScalarAsync(), Is.EqualTo(9));
}
[Test, Description("Tests if function parameter derivation throws an exception if the specified function is not in the search_path")]
public async Task DeriveParameters_throws_for_existing_function_that_is_not_in_search_path()
{
await using var conn = await OpenConnectionAsync();
var schema = await CreateTempSchema(conn);
await conn.ExecuteNonQueryAsync($@"
CREATE FUNCTION {schema}.schema1func() RETURNS int AS 'SELECT 1' LANGUAGE sql;
RESET search_path;");
await using var command = new NpgsqlCommand("schema1func", conn) { CommandType = CommandType.StoredProcedure };
Assert.That(() => NpgsqlCommandBuilder.DeriveParameters(command),
Throws.Exception.TypeOf<PostgresException>()
.With.Property(nameof(PostgresException.SqlState)).EqualTo(PostgresErrorCodes.UndefinedFunction));
}
[Test, Description("Tests if an exception is thrown if multiple functions with the specified name are in the search_path")]
public async Task DeriveParameters_throws_for_multiple_function_name_hits_in_search_path()
{
await using var conn = await OpenConnectionAsync();
var schema1 = await CreateTempSchema(conn);
var schema2 = await CreateTempSchema(conn);
await conn.ExecuteNonQueryAsync(
$@"
CREATE FUNCTION {schema1}.redundantfunc() RETURNS int AS 'SELECT 1' LANGUAGE sql;
CREATE FUNCTION {schema1}.redundantfunc(IN param1 INT, IN param2 INT) RETURNS int AS 'SELECT param1 + param2' LANGUAGE sql;
SET search_path TO {schema1}, {schema2};");
var command = new NpgsqlCommand("redundantfunc", conn) { CommandType = CommandType.StoredProcedure };
Assert.That(() => NpgsqlCommandBuilder.DeriveParameters(command),
Throws.Exception.TypeOf<PostgresException>()
.With.Property(nameof(PostgresException.SqlState)).EqualTo(PostgresErrorCodes.AmbiguousFunction));
}
#region Set returning functions
[Test, Description("Tests parameter derivation for a function that returns SETOF sometype")]
public async Task DeriveParameters_function_returning_setof_type()
{
await using var conn = await OpenConnectionAsync();
MinimumPgVersion(conn, "9.2.0");
var table = await GetTempTableName(conn);
var function = await GetTempFunctionName(conn);
// This function returns record because of the two Out (InOut & Out) parameters
await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (fooid int, foosubid int, fooname text);
INSERT INTO {table} VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');
CREATE FUNCTION {function}(int) RETURNS SETOF {table} AS $$
SELECT * FROM {table} WHERE {table}.fooid = $1 ORDER BY {table}.foosubid;
$$ LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, 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;
await cmd.ExecuteNonQueryAsync();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(1));
}
[Test, Description("Tests parameter derivation for a function that returns TABLE")]
public async Task DeriveParameters_function_returning_table()
{
await using var conn = await OpenConnectionAsync();
MinimumPgVersion(conn, "9.2.0");
var table = await GetTempTableName(conn);
var function = await GetTempFunctionName(conn);
// This function returns record because of the two Out (InOut & Out) parameters
await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (fooid int, foosubid int, fooname text);
INSERT INTO {table} VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');
CREATE FUNCTION {function}(int) RETURNS TABLE(fooid int, foosubid int, fooname text) AS $$
SELECT * FROM {table} WHERE {table}.fooid = $1 ORDER BY {table}.foosubid;
$$ LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, 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;
await cmd.ExecuteNonQueryAsync();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(1));
}
[Test, Description("Tests parameter derivation for a function that returns SETOF record")]
public async Task DeriveParameters_function_returning_setof_record()
{
await using var conn = await OpenConnectionAsync();
MinimumPgVersion(conn, "9.2.0");
var table = await GetTempTableName(conn);
var function = await GetTempFunctionName(conn);
// This function returns record because of the two Out (InOut & Out) parameters
await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (fooid int, foosubid int, fooname text);
INSERT INTO {table} VALUES (1, 1, 'Joe'), (1, 2, 'Ed'), (2, 1, 'Mary');
CREATE FUNCTION {function}(int, OUT fooid int, OUT foosubid int, OUT fooname text) RETURNS SETOF record AS $$
SELECT * FROM {table} WHERE {table}.fooid = $1 ORDER BY {table}.foosubid;
$$ LANGUAGE sql");
await using var cmd = new NpgsqlCommand(function, 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;
await cmd.ExecuteNonQueryAsync();
Assert.That(cmd.Parameters[0].Value, Is.EqualTo(1));
}
[Test, IssueLink("https://github.com/npgsql/npgsql/issues/2022")]
public async Task DeriveParameters_function_returning_setof_type_with_dropped_column()
{
await using var conn = await OpenConnectionAsync();
MinimumPgVersion(conn, "9.2.0");
var table = await GetTempTableName(conn);
var function = await GetTempFunctionName(conn);
await conn.ExecuteNonQueryAsync($@"
CREATE TABLE {table} (id serial PRIMARY KEY, t1 text, t2 text);
CREATE FUNCTION {function}() RETURNS SETOF {table} AS 'SELECT * FROM {table}' LANGUAGE sql;
ALTER TABLE {table} DROP t2;");
await using var cmd = new NpgsqlCommand(function, 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));
}
#endregion
#endregion DeriveParameters
#if DEBUG
[OneTimeSetUp]
public void OneTimeSetup() => NpgsqlCommand.EnableStoredProcedureCompatMode = true;
[OneTimeTearDown]
public void OneTimeTeardown() => NpgsqlCommand.EnableStoredProcedureCompatMode = false;
#else
[OneTimeSetUp]
public void OneTimeSetup()
=> Assert.Ignore("Cannot test function invocation via CommandType.StoredProcedure since that depends on the global EnableStoredProcedureCompatMode compatibility flag");
#endif
}