Skip to content

Commit 7cd127a

Browse files
vbilopavvbilopav
committed
return table field support
1 parent 78fffc1 commit 7cd127a

5 files changed

Lines changed: 201 additions & 11 deletions

File tree

‎NpgsqlRest/RoutineSource.cs‎

Lines changed: 44 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -73,7 +73,30 @@ public class RoutineSource(
7373
var schema = reader.Get<string>(1);//"schema");
7474
var returnsSet = reader.Get<bool>(6);//"returns_set");
7575

76+
var returnRecordNames = reader.Get<string[]>(9);//"return_record_names");
7677
var returnRecordTypes = reader.Get<string[]>(10);//"return_record_types");
78+
79+
string[] expNames = new string[returnRecordNames.Length];
80+
var customRecTypeNames = reader.Get<string?[]>(21);
81+
if (customRecTypeNames is not null && customRecTypeNames.Length > 0)
82+
{
83+
var customRecTypeTypes = reader.Get<string?[]>(22);
84+
for(var i = 0; i < returnRecordNames.Length; i++)
85+
{
86+
var customName = customRecTypeNames[i];
87+
if (customName is not null)
88+
{
89+
expNames[i] = string.Concat("(", returnRecordNames[i], "::", returnRecordTypes[i], ").", customName);
90+
returnRecordNames[i] = customName;
91+
returnRecordTypes[i] = customRecTypeTypes[i] ?? returnRecordTypes[i];
92+
}
93+
else
94+
{
95+
expNames[i] = returnRecordNames[i];
96+
}
97+
}
98+
}
99+
77100
TypeDescriptor[] returnTypeDescriptor;
78101
if (isVoid)
79102
{
@@ -83,7 +106,7 @@ public class RoutineSource(
83106
{
84107
returnTypeDescriptor = returnRecordTypes.Select(x => new TypeDescriptor(x)).ToArray();
85108
}
86-
var returnRecordNames = reader.Get<string[]>(9);//"return_record_names");
109+
87110

88111
bool isUnnamedRecord = reader.Get<bool>(11);// "is_unnamed_record");
89112
var routineType = type.GetEnum<RoutineType>();
@@ -146,10 +169,27 @@ public class RoutineSource(
146169

147170
var returnRecordCount = reader.Get<int>(8);// "return_record_count");
148171
var variadic = reader.Get<bool>(16);// "has_variadic");
172+
string from;
173+
if (isVoid || returnRecordCount == 1)
174+
{
175+
from = callIdent;
176+
}
177+
else
178+
{
179+
//from = string.Concat(callIdent, string.Join(",", returnRecordNames), " from ");
180+
StringBuilder sb = new();
181+
for (int i = 0; i < returnRecordCount; i++)
182+
{
183+
sb.Append(expNames[i] ?? returnRecordNames[i]);
184+
if (i < returnRecordCount - 1)
185+
{
186+
sb.Append(',');
187+
}
188+
}
189+
from = string.Concat(callIdent, sb.ToString(), " from ");
190+
}
149191
var expression = string.Concat(
150-
(isVoid || returnRecordCount == 1)
151-
? callIdent
152-
: string.Concat(callIdent, string.Join(",", returnRecordNames), " from "),
192+
from,
153193
schema,
154194
".",
155195
name,

‎NpgsqlRest/RoutineSourceQuery.cs‎

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -108,9 +108,12 @@ when r.is_user_defined then null
108108
r.type_udt_name,
109109
r.data_type,
110110
111-
coalesce(array_agg(r.custom_type_name order by r.param_position), '{}'::text[]) as custom_type_names,
112-
coalesce(array_agg(r.custom_type_type order by r.param_position), '{}'::text[]) as custom_type_types,
113-
coalesce(array_agg(r.custom_type_pos order by r.param_position), '{}'::smallint[]) as custom_type_positions
111+
coalesce(array_agg(r.custom_type_name order by r.param_position) filter(where r.is_in_param), '{}'::text[]) as custom_param_type_names,
112+
coalesce(array_agg(r.custom_type_type order by r.param_position) filter(where r.is_in_param), '{}'::text[]) as custom_param_type_types,
113+
coalesce(array_agg(r.custom_type_pos order by r.param_position) filter(where r.is_in_param), '{}'::smallint[]) as custom_param_type_positions,
114+
115+
coalesce(array_agg(r.custom_type_name order by r.param_position) filter(where r.is_out_param), '{}'::text[]) as custom_rec_type_names,
116+
coalesce(array_agg(r.custom_type_type order by r.param_position) filter(where r.is_out_param), '{}'::text[]) as custom_rec_type_types
114117
from
115118
r
116119
join pg_catalog.pg_proc proc on r.specific_name = proc.proname || '_' || proc.oid
@@ -185,9 +188,12 @@ when array_length(coalesce(cte1.out_param_types, cte2.out_param_types), 1) is nu
185188
arguments_def,
186189
has_variadic,
187190
pg_get_functiondef(oid) as definition,
188-
custom_type_names,
189-
custom_type_types,
190-
custom_type_positions
191+
custom_param_type_names,
192+
custom_param_type_types,
193+
custom_param_type_positions,
194+
195+
custom_rec_type_names,
196+
custom_rec_type_types
191197
from cte1
192198
left join cte2 on cte1.schema = cte2.schema and cte1.specific_name = cte2.specific_name
193199

‎NpgsqlRestTests/ReturnTableTypeTests.cs‎

Lines changed: 104 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,104 @@
1+
namespace NpgsqlRestTests;
2+
3+
public static partial class Database
4+
{
5+
public static void ReturnTableTypeTests()
6+
{
7+
script.Append(@"
8+
9+
create table test_return_table(
10+
id int generated always as identity primary key,
11+
value text,
12+
value2 text
13+
);
14+
15+
insert into test_return_table
16+
(value, value2) values('test1', 'test2'),
17+
('test3', 'test4');
18+
19+
create function test_return_table_record()
20+
returns test_return_table
21+
language sql as 'select * from test_return_table where id = 1';
22+
23+
create function test_return_all_table_records()
24+
returns setof test_return_table
25+
language sql as 'select * from test_return_table';
26+
27+
create or replace function test_return_mixed_table_records()
28+
returns table (
29+
value1 text,
30+
value2 test_return_table
31+
)
32+
language sql as
33+
$$
34+
select
35+
'test1',
36+
row(t.*)::test_return_table
37+
from
38+
test_return_table t;
39+
$$;
40+
41+
create or replace function test_return_mixed_table_records_2()
42+
returns table (
43+
value1 test_return_table,
44+
value2 text
45+
)
46+
language sql as
47+
$$
48+
select
49+
row(t.*)::test_return_table,
50+
'test1'
51+
from
52+
test_return_table t;
53+
$$;
54+
");
55+
}
56+
}
57+
58+
[Collection("TestFixture")]
59+
public class ReturnTableTypeTests(TestFixture test)
60+
{
61+
[Fact]
62+
public async Task Test_test_return_table_record()
63+
{
64+
using var response = await test.Client.PostAsync($"/api/test-return-table-record", null);
65+
var content = await response.Content.ReadAsStringAsync();
66+
67+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
68+
response?.Content.Headers.ContentType?.MediaType.Should().Be("application/json");
69+
content.Should().Be("{\"id\":1,\"value\":\"test1\",\"value2\":\"test2\"}");
70+
}
71+
72+
[Fact]
73+
public async Task Test_test_return_all_table_records()
74+
{
75+
using var response = await test.Client.PostAsync($"/api/test-return-all-table-records", null);
76+
var content = await response.Content.ReadAsStringAsync();
77+
78+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
79+
response?.Content.Headers.ContentType?.MediaType.Should().Be("application/json");
80+
content.Should().Be("[{\"id\":1,\"value\":\"test1\",\"value2\":\"test2\"},{\"id\":2,\"value\":\"test3\",\"value2\":\"test4\"}]");
81+
}
82+
83+
[Fact]
84+
public async Task Test_test_return_mixed_table_records()
85+
{
86+
using var response = await test.Client.PostAsync($"/api/test-return-mixed-table-records", null);
87+
var content = await response.Content.ReadAsStringAsync();
88+
89+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
90+
response?.Content.Headers.ContentType?.MediaType.Should().Be("application/json");
91+
content.Should().Be("[{\"value1\":\"test1\",\"id\":1,\"value\":\"test1\",\"value2\":\"test2\"},{\"value1\":\"test1\",\"id\":2,\"value\":\"test3\",\"value2\":\"test4\"}]");
92+
}
93+
94+
[Fact]
95+
public async Task Test_test_return_mixed_table_records_2()
96+
{
97+
using var response = await test.Client.PostAsync($"/api/test-return-mixed-table-records-2", null);
98+
var content = await response.Content.ReadAsStringAsync();
99+
100+
response?.StatusCode.Should().Be(HttpStatusCode.OK);
101+
response?.Content.Headers.ContentType?.MediaType.Should().Be("application/json");
102+
content.Should().Be("[{\"id\":1,\"value\":\"test1\",\"value2\":\"test2\",\"value2\":\"test1\"},{\"id\":2,\"value\":\"test3\",\"value2\":\"test4\",\"value2\":\"test1\"}]");
103+
}
104+
}

‎NpgsqlRestTests/ReturnTypeTests.cs‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,40 @@
1+
/*
2+
namespace NpgsqlRestTests;
3+
4+
public static partial class Database
5+
{
6+
public static void ReturnSetTypeTests()
7+
{
8+
script.Append(@"
9+
create type test_return_type as (
10+
id int,
11+
value text,
12+
value2 text
13+
);
14+
15+
create function test_return_type()
16+
returns test_return_type
17+
language sql as 'select row(1, ''test1'', ''test2'')::test_return_type';
18+
19+
create function test_return_setof_type()
20+
returns setof test_return_type
21+
language sql as 'select row(1, ''test1'', ''test2'')::test_return_type union all select row(2, ''test3'', ''test4'')::test_return_type';
22+
");
23+
}
24+
}
25+
26+
[Collection("TestFixture")]
27+
public class ReturnSetTypeTests(TestFixture test)
28+
{
29+
[Fact]
30+
public async Task Test_test_return_type()
31+
{
32+
using var response = await test.Client.PostAsync($"/api/test-return-type", null);
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,\"value\":\"test1\",\"value2\":\"test2\"}");
38+
}
39+
}
40+
*/

‎NpgsqlRestTests/Setup/Program.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -80,7 +80,7 @@ public static void Main()
8080

8181
app.UseNpgsqlRest(new(connectionString)
8282
{
83-
//NameSimilarTo = "get_custom_param_query_1p",
83+
//NameSimilarTo = "get_test_table",
8484
//SchemaSimilarTo = "custom_param_schema",
8585
CommentsMode = CommentsMode.ParseAll,
8686
ValidateParametersAsync = ValidateAsync,

0 commit comments

Comments
 (0)