Skip to content

Commit 4e10b66

Browse files
vbilopavvbilopav
committed
v1.3.0
1 parent 0547d79 commit 4e10b66

6 files changed

Lines changed: 279 additions & 13 deletions

File tree

‎NpgsqlRest/MiddlewareExtension.cs‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -11,6 +11,7 @@
1111
using static NpgsqlRest.Logging;
1212
using static System.Net.Mime.MediaTypeNames;
1313
using System.Diagnostics.CodeAnalysis;
14+
using NpgsqlTypes;
1415

1516
namespace NpgsqlRest;
1617

@@ -669,7 +670,15 @@ await options.ValidateParametersAsync(new ParameterValidationValues(
669670
}
670671
else
671672
{
672-
if (descriptor.NeedsEscape)
673+
if (descriptor.ActualDbType == NpgsqlDbType.Unknown)
674+
{
675+
if (raw.StartsWith('(') && raw.EndsWith(')'))
676+
{
677+
raw = raw[1..^1];
678+
}
679+
await context.Response.WriteAsync(SerializeString(ref raw));
680+
}
681+
else if (descriptor.NeedsEscape)
673682
{
674683
await context.Response.WriteAsync(SerializeString(ref raw));
675684
}

‎NpgsqlRest/RoutineQuery.cs‎

Lines changed: 49 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -29,31 +29,57 @@ internal class RoutineQuery()
2929
else '' end volatility_option,
3030
proc.proretset as returns_set,
3131
(r.type_udt_schema || '.' || r.type_udt_name)::regtype::text as return_type,
32+
3233
coalesce(
3334
array_agg(p.parameter_name::text order by p.ordinal_position) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
3435
'{}'::text[]
3536
) as in_params,
37+
3638
coalesce(
37-
array_agg(p.parameter_name::text order by p.ordinal_position) filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT'),
39+
case when r.data_type = 'USER-DEFINED' then
40+
(
41+
select array_agg(column_name order by col.ordinal_position)
42+
from information_schema.columns col
43+
where table_schema = r.type_udt_schema and table_name = r.type_udt_name
44+
)
45+
else
46+
array_agg(p.parameter_name::text order by p.ordinal_position) filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT')
47+
end,
48+
3849
'{}'::text[]
3950
) as out_params,
51+
4052
coalesce(
4153
array_agg(
4254
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end
4355
order by p.ordinal_position
4456
) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
4557
'{}'::text[]
4658
) as in_param_types,
59+
4760
coalesce(
48-
array_agg(
49-
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end
50-
order by p.ordinal_position) filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT'),
51-
'{}'::text[]
61+
case when r.data_type = 'USER-DEFINED' then
62+
(
63+
select array_agg(
64+
case when col.data_type = 'bit' then 'varbit' else (col.udt_schema || '.' || col.udt_name)::regtype::text end
65+
order by col.ordinal_position
66+
)
67+
from information_schema.columns col
68+
where table_schema = r.type_udt_schema and table_name = r.type_udt_name
69+
)
70+
else
71+
array_agg(
72+
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end
73+
order by p.ordinal_position) filter(where p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT')
74+
end, '{}'::text[]
5275
) as out_param_types,
76+
5377
coalesce(
5478
array_agg(p.parameter_default order by p.ordinal_position) filter(where p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT'),
5579
'{}'::text[]
56-
) as in_param_defaults
80+
) as in_param_defaults,
81+
82+
case when proc.provariadic <> 0 then true else false end as has_variadic
5783
from
5884
information_schema.routines r
5985
join pg_catalog.pg_proc proc on r.specific_name = proc.proname || '_' || proc.oid
@@ -84,8 +110,9 @@ and r.type_udt_name <> 'trigger'
84110
group by
85111
r.routine_type, r.specific_schema, r.routine_name,
86112
proc.oid, r.specific_name, r.external_language, des.description,
87-
r.security_type, r.type_udt_schema, r.type_udt_name,
88-
proc.proisstrict, proc.procost, proc.prorows, proc.proparallel, proc.provolatile, proc.proretset
113+
r.security_type, r.data_type, r.type_udt_schema, r.type_udt_name,
114+
proc.proisstrict, proc.procost, proc.prorows, proc.proparallel, proc.provolatile,
115+
proc.proretset, proc.provariadic
89116
)
90117
select
91118
type,
@@ -106,6 +133,7 @@ group by
106133
when returns_set is true and return_type <> 'record' then 'record'
107134
else return_type
108135
end as return_type,
136+
109137
case
110138
when case when returns_set is true or return_type = 'record' then true else false end then
111139
case when array_length(out_params, 1) is null then 1 else array_length(out_params, 1) end
@@ -121,11 +149,15 @@ when returns_set is true and return_type <> 'record' then 'record'
121149
case when array_length(out_params, 1) is null then array[return_type]::text[] else out_param_types end
122150
else array[]::text[]
123151
end as return_record_types,
152+
124153
returns_set is true and return_type <> 'record' and array_length(out_params, 1) is null as returns_unnamed_set,
154+
returns_set is true and return_type = 'record' and array_length(out_params, 1) is null as is_unnamed_record,
155+
125156
array_length(in_params, 1) as param_count,
126157
in_params as param_names,
127158
in_param_types as param_types,
128159
in_param_defaults as param_defaults,
160+
has_variadic,
129161
pg_get_functiondef(oid) as definition
130162
from cte";
131163

@@ -191,9 +223,13 @@ void AddParameter(object? value, bool isArray = false) => command?
191223
.Select((x, i) => new TypeDescriptor(x, hasDefault: paramDefaults[i] is not null))
192224
.ToArray();
193225

226+
bool hasVariadic = reader.Get<bool>("has_variadic");
227+
bool isUnnamedRecord = reader.Get<bool>("is_unnamed_record");
228+
194229
Dictionary<int, string> expressions = [];
230+
195231
var expression = string.Concat(
196-
(isVoid || !returnsRecord || (returnsRecord && returnsUnnamedSet)) ?
232+
(isVoid || !returnsRecord || (returnsRecord && returnsUnnamedSet) || isUnnamedRecord) ?
197233
"select " :
198234
string.Concat("select ", string.Join(", ", returnRecordNames), " from "),
199235
schema,
@@ -210,11 +246,12 @@ void AddParameter(object? value, bool isArray = false) => command?
210246
.Select(i =>
211247
{
212248
var descriptor = paramTypeDescriptor[i - 1];
249+
string prefix = hasVariadic && i == paramCount ? "variadic " : "";
213250
if (descriptor.IsCastToText())
214251
{
215-
return $"${i}::{descriptor.OriginalType}";
252+
return $"{prefix}${i}::{descriptor.OriginalType}";
216253
}
217-
return $"${i}";
254+
return $"{prefix}${i}";
218255
})),
219256
")");
220257
}
@@ -240,7 +277,7 @@ void AddParameter(object? value, bool isArray = false) => command?
240277
returnRecordCount: reader.Get<int>("return_record_count"),
241278
returnRecordNames: returnRecordNames,
242279
returnRecordTypes: returnRecordTypes,
243-
returnsUnnamedSet: returnsUnnamedSet,
280+
returnsUnnamedSet: returnsUnnamedSet || isUnnamedRecord,
244281
paramCount: paramCount,
245282
paramNames: paramNames,
246283
paramTypes: paramTypes,

‎NpgsqlRestTests/SetofRecordTests.cs‎

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,33 @@
1+
namespace NpgsqlRestTests;
2+
3+
public static partial class Database
4+
{
5+
public static void SetofRecordTests()
6+
{
7+
script.Append(@"
8+
create function get_test_records()
9+
returns setof record
10+
language sql
11+
as
12+
$$
13+
select * from (values (1, 'one'), (2, 'two'), (3, 'three')) v(id, name);
14+
$$;
15+
");
16+
}
17+
}
18+
19+
20+
[Collection("TestFixture")]
21+
public class SetofRecordTests(TestFixture test)
22+
{
23+
[Fact]
24+
public async Task Test_get_test_records()
25+
{
26+
using var response = await test.Client.GetAsync("/api/get-test-records");
27+
var content = await response.Content.ReadAsStringAsync();
28+
29+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
30+
response?.Content?.Headers?.ContentType?.MediaType.Should().Be("application/json");
31+
content.Should().Be("[\"1,one\",\"2,two\",\"3,three\"]");
32+
}
33+
}

‎NpgsqlRestTests/SetofTableTests.cs‎

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
namespace NpgsqlRestTests;
2+
3+
public static partial class Database
4+
{
5+
public static void SetofTableTests()
6+
{
7+
script.Append(@"
8+
create table test_table (
9+
id int,
10+
name text not null
11+
);
12+
13+
insert into test_table values (1, 'one'), (2, 'two'), (3, 'three');
14+
15+
create function get_test_table()
16+
returns setof test_table
17+
language sql
18+
as
19+
$$
20+
select * from test_table;
21+
$$;
22+
");
23+
}
24+
}
25+
26+
[Collection("TestFixture")]
27+
public class SetofTableTests(TestFixture test)
28+
{
29+
[Fact]
30+
public async Task Test_get_test_table()
31+
{
32+
using var response = await test.Client.GetAsync("/api/get-test-table");
33+
var content = await response.Content.ReadAsStringAsync();
34+
35+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
36+
response?.Content?.Headers?.ContentType?.MediaType.Should().Be("application/json");
37+
content.Should().Be("[{\"id\":1,\"name\":\"one\"},{\"id\":2,\"name\":\"two\"},{\"id\":3,\"name\":\"three\"}]");
38+
}
39+
}

‎NpgsqlRestTests/VariadicParamTests.cs‎

Lines changed: 32 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,32 @@
1+
namespace NpgsqlRestTests;
2+
3+
public static partial class Database
4+
{
5+
public static void VariadicParamTests()
6+
{
7+
script.Append(@"
8+
create function variadic_param_plus_one(variadic v int[])
9+
returns int[]
10+
language sql
11+
as
12+
$$
13+
select array_agg(n + 1) from unnest($1) AS n;
14+
$$;
15+
");
16+
}
17+
}
18+
19+
[Collection("TestFixture")]
20+
public class VariadicParamTests(TestFixture test)
21+
{
22+
[Fact]
23+
public async Task Test_variadic_param_plus_one()
24+
{
25+
using var body = new StringContent("{\"v\": [1,2,3,4]}", Encoding.UTF8);
26+
using var response = await test.Client.PostAsync("/api/variadic-param-plus-one", body);
27+
var content = await response.Content.ReadAsStringAsync();
28+
29+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
30+
content.Should().Be("[2,3,4,5]");
31+
}
32+
}

‎changelog.md‎

Lines changed: 116 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,121 @@
11
# Changelog
22

3+
## Version 1.3.0 (2024-23-01)
4+
5+
### 1) Support For Variadic Parameters
6+
7+
```sql
8+
create function variadic_param_plus_one(variadic v int[])
9+
returns int[]
10+
language sql
11+
as
12+
$$
13+
select array_agg(n + 1) from unnest($1) AS n;
14+
$$;
15+
```
16+
```csharp
17+
[Fact]
18+
public async Task Test_variadic_param_plus_one()
19+
{
20+
using var body = new StringContent("{\"v\": [1,2,3,4]}", Encoding.UTF8);
21+
using var response = await test.Client.PostAsync("/api/variadic-param-plus-one", body);
22+
var content = await response.Content.ReadAsStringAsync();
23+
24+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
25+
content.Should().Be("[2,3,4,5]");
26+
}
27+
```
28+
29+
### 2) Support For Table-Valued Functions
30+
31+
It seems that support for functions returning existing tables (set of table, or table-valued functions) was accidentally missing. This is now fixed.
32+
33+
Example:
34+
35+
```sql
36+
create table test_table (
37+
id int,
38+
name text not null
39+
);
40+
41+
insert into test_table values (1, 'one'), (2, 'two'), (3, 'three');
42+
43+
create function get_test_table()
44+
returns setof test_table
45+
language sql
46+
as
47+
$$
48+
select * from test_table;
49+
$$;
50+
```
51+
```csharp
52+
[Fact]
53+
public async Task Test_get_test_table()
54+
{
55+
using var response = await test.Client.GetAsync("/api/get-test-table");
56+
var content = await response.Content.ReadAsStringAsync();
57+
58+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
59+
response?.Content?.Headers?.ContentType?.MediaType.Should().Be("application/json");
60+
content.Should().Be("[{\"id\":1,\"name\":\"one\"},{\"id\":2,\"name\":\"two\"},{\"id\":3,\"name\":\"three\"}]");
61+
}
62+
```
63+
64+
### 2) Support for Untyped Functions
65+
66+
Untyped functions are functions that return a set of records of the unknown type.
67+
68+
For example:
69+
70+
```sql
71+
create function get_test_records()
72+
returns setof record
73+
language sql
74+
as
75+
$$
76+
select * from (values (1, 'one'), (2, 'two'), (3, 'three')) v(id, name);
77+
$$;
78+
```
79+
80+
These cannot be called without the result column definition list:
81+
82+
```sql
83+
select * from get_test_records()
84+
85+
-- throws error:
86+
-- ERROR: a column definition list is required for functions returning "record"
87+
-- LINE 1: select * from get_test_records()
88+
89+
select * from get_test_records() as v(a int, b text)
90+
-- returns result with columns a int, b text
91+
```
92+
93+
There is no way of knowing which results these functions will return (as far as I know) and the only way to return a workable set is to execute it as a scalar function:
94+
95+
```sql
96+
select get_test_records()
97+
98+
-- returns values:
99+
(1,one)
100+
(2,two)
101+
(3,three)
102+
```
103+
104+
These are raw record values as returned from PostgreSQL without any parsing (for example text is not quoted). When NpgsqlRest executes this type of function it will return a JSON array with raw values from the PostgreSQL server without left and right parenthesis:
105+
106+
```csharp
107+
[Fact]
108+
public async Task Test_get_test_records()
109+
{
110+
using var response = await test.Client.GetAsync("/api/get-test-records");
111+
var content = await response.Content.ReadAsStringAsync();
112+
113+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
114+
response?.Content?.Headers?.ContentType?.MediaType.Should().Be("application/json");
115+
content.Should().Be("[\"1,one\",\"2,two\",\"3,three\"]");
116+
}
117+
```
118+
3119
## Version 1.2.0 (2024-22-01)
4120

5121
### 1) Fix AOT Warnings

0 commit comments

Comments
 (0)