Skip to content

Commit ca761ff

Browse files
vbilopavclaude
vbilopav
and
claude
committed
Add @single comment annotation for single record JSON responses
New annotation `single` (aliases: single_record, single_result) returns the first row as a JSON object instead of a JSON array. Works across all endpoint sources. Empty results respect the response_null handling. TsClient generates non-array return types when @single is set. Co-Authored-By: Claude Opus 4.6 (1M context) <noreply@anthropic.com>
1 parent 2e13193 commit ca761ff

12 files changed

Lines changed: 404 additions & 4 deletions

File tree

‎NpgsqlRest/Defaults/CommentParsers/SingleHandler.cs‎

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,25 @@
1+
namespace NpgsqlRest.Defaults;
2+
3+
internal static partial class DefaultCommentParser
4+
{
5+
/// <summary>
6+
/// Annotation: single | single_record | single_result
7+
/// Syntax: single
8+
///
9+
/// Description: Return only the first row as a JSON object instead of a JSON array.
10+
/// If the query returns multiple rows, only the first row is returned.
11+
/// </summary>
12+
private static readonly string[] SingleKey = [
13+
"single",
14+
"single_record",
15+
"single_result",
16+
];
17+
18+
private static void HandleSingle(
19+
RoutineEndpoint endpoint,
20+
string description)
21+
{
22+
CommentLogger?.CommentSetSingleRecord(description);
23+
endpoint.ReturnSingleRecord = true;
24+
}
25+
}

‎NpgsqlRest/Defaults/DefaultCommentParser.cs‎

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -221,6 +221,13 @@ internal static partial class DefaultCommentParser
221221
HandleRaw(routineEndpoint, description);
222222
}
223223

224+
// single
225+
// single_record
226+
else if (haveTag is true && StrEqualsToArray(wordsLower[0], SingleKey))
227+
{
228+
HandleSingle(routineEndpoint, description);
229+
}
230+
224231
// separator [ value ]
225232
// raw_separator [ value ]
226233
else if (haveTag is true && line.StartsWith(string.Concat(SeparatorKey[0], " ")))
@@ -467,6 +474,13 @@ public static void SetCustomParameter(RoutineEndpoint endpoint, string name, str
467474
endpoint.Raw = parsedRaw;
468475
}
469476
}
477+
else if (StrEqualsToArray(name, SingleKey))
478+
{
479+
if (bool.TryParse(value, out var parsedSingle))
480+
{
481+
endpoint.ReturnSingleRecord = parsedSingle;
482+
}
483+
}
470484
else if (StrEqualsToArray(name, SeparatorKey))
471485
{
472486
endpoint.RawValueSeparator = value;

‎NpgsqlRest/Log.cs‎

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -101,6 +101,9 @@ public static partial class Log
101101
[LoggerMessage(Level = LogLevel.Debug, Message = "{description} has set RAW MODE by the comment annotation.")]
102102
public static partial void CommentSetRawMode(this ILogger logger, string description);
103103

104+
[LoggerMessage(Level = LogLevel.Debug, Message = "{description} has set SINGLE RECORD by the comment annotation.")]
105+
public static partial void CommentSetSingleRecord(this ILogger logger, string description);
106+
104107
[LoggerMessage(Level = LogLevel.Debug, Message = "{description} has set SEPARATOR by the comment annotation to {value}.")]
105108
public static partial void CommentSetRawValueSeparator(this ILogger logger, string description, string value);
106109

‎NpgsqlRest/NpgsqlRestEndpoint.cs‎

Lines changed: 21 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -2543,7 +2543,7 @@ Options.AuthenticationOptions.DefaultDataProtector is not null &&
25432543
currentColumnCount = cmdInfo.ColumnCount;
25442544
}
25452545

2546-
if (routine.ReturnsSet && endpoint.Raw is false && binary is false)
2546+
if (routine.ReturnsSet && endpoint.Raw is false && binary is false && endpoint.ReturnSingleRecord is false)
25472547
{
25482548
writer.Advance(Encoding.UTF8.GetBytes(Consts.OpenBracket.ToString().AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(1))));
25492549
if (shouldCache)
@@ -2873,6 +2873,11 @@ descriptor.CompositeFieldNames is not null &&
28732873
cacheBuffer = null; // Release memory
28742874
}
28752875

2876+
if (endpoint.ReturnSingleRecord)
2877+
{
2878+
break;
2879+
}
2880+
28762881
if (bufferRows != 1 && rowCount % bufferRows == 0)
28772882
{
28782883
// Append to cache buffer before clearing row
@@ -2886,7 +2891,20 @@ descriptor.CompositeFieldNames is not null &&
28862891
}
28872892
} // end while
28882893

2889-
if (binary is true)
2894+
if (endpoint.ReturnSingleRecord && rowCount == 0)
2895+
{
2896+
if (endpoint.TextResponseNullHandling == TextResponseNullHandling.NullLiteral)
2897+
{
2898+
writer.Advance(Encoding.UTF8.GetBytes(Consts.Null.AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(Consts.Null.Length))));
2899+
await writer.FlushAsync(cancellationToken);
2900+
}
2901+
else if (endpoint.TextResponseNullHandling == TextResponseNullHandling.NoContent)
2902+
{
2903+
context.Response.StatusCode = (int)HttpStatusCode.NoContent;
2904+
}
2905+
// else EmptyString: empty 200 OK (default)
2906+
}
2907+
else if (binary is true)
28902908
{
28912909
await writer.FlushAsync(cancellationToken);
28922910
}
@@ -2902,7 +2920,7 @@ descriptor.CompositeFieldNames is not null &&
29022920
WriteStringBuilderToWriter(rowBuilder, writer);
29032921
await writer.FlushAsync(cancellationToken);
29042922
}
2905-
if (routine.ReturnsSet && endpoint.Raw is false)
2923+
if (routine.ReturnsSet && endpoint.Raw is false && endpoint.ReturnSingleRecord is false)
29062924
{
29072925
writer.Advance(Encoding.UTF8.GetBytes(Consts.CloseBracket.ToString().AsSpan(), writer.GetSpan(Encoding.UTF8.GetMaxByteCount(1))));
29082926
if (shouldCache)

‎NpgsqlRest/RoutineEndpoint.cs‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -252,6 +252,13 @@ internal void EnsurePathParametersHashSet()
252252
/// </summary>
253253
public Dictionary<string, List<ValidationRule>>? ParameterValidations { get; set; } = null;
254254

255+
/// <summary>
256+
/// When true, only the first row is returned and the result is serialized as a JSON object
257+
/// instead of a JSON array. If the query returns multiple rows, only the first row is used.
258+
/// Configured via the "single" comment annotation.
259+
/// </summary>
260+
public bool ReturnSingleRecord { get; set; } = false;
261+
255262
/// <summary>
256263
/// When true, composite type columns in the response are serialized as nested JSON objects.
257264
/// For example, a column "req" of type "my_request(id int, name text)" becomes {"req": {"id": 1, "name": "test"}}

‎NpgsqlRestTests/CommentTests/CommentSingleTests.cs‎

Lines changed: 170 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,170 @@
1+
namespace NpgsqlRestTests;
2+
3+
public static partial class Database
4+
{
5+
public static void CommentSingleTests()
6+
{
7+
script.Append(@"
8+
-- Single record from a function returning SETOF (multiple rows, but @single returns only first as object)
9+
create function comment_single_setof() returns setof text language sql as
10+
'select ''row1'' union all select ''row2'' union all select ''row3''';
11+
comment on function comment_single_setof() is 'HTTP GET
12+
@single';
13+
14+
-- Single record from a function returning a table (multi-column, returns object not array)
15+
create function comment_single_table()
16+
returns table(id int, name text) language sql as
17+
'select 1, ''alice'' union all select 2, ''bob''';
18+
comment on function comment_single_table() is 'HTTP GET
19+
@single';
20+
21+
-- Single record from a function that naturally returns one row (SETOF with one result)
22+
create function comment_single_one_row() returns setof text language sql as
23+
'select ''only_one''';
24+
comment on function comment_single_one_row() is 'HTTP GET
25+
@single';
26+
27+
-- Without @single for comparison (returns array)
28+
create function comment_no_single_setof() returns setof text language sql as
29+
'select ''row1'' union all select ''row2''';
30+
comment on function comment_no_single_setof() is 'HTTP GET';
31+
32+
-- Single with single_record alias
33+
create function comment_single_record_alias() returns setof text language sql as
34+
'select ''alias_result''';
35+
comment on function comment_single_record_alias() is 'HTTP GET
36+
@single_record';
37+
38+
-- Single record from a function returning table with multiple rows - verifies early exit
39+
create function comment_single_multi_row_table()
40+
returns table(id int, val text) language sql as
41+
'select generate_series(1, 100), ''item_'' || generate_series(1, 100)';
42+
comment on function comment_single_multi_row_table() is 'HTTP GET
43+
single';
44+
45+
-- Single record from a function returning table with one column (named, ReturnsUnnamedSet=false)
46+
-- Should return object not bare value
47+
create function comment_single_named_column()
48+
returns table(val text) language sql as
49+
'select ''hello''';
50+
comment on function comment_single_named_column() is 'HTTP GET
51+
@single';
52+
53+
-- Single with empty result, default null handling (empty string)
54+
create function comment_single_empty_default()
55+
returns table(id int, name text) language sql as
56+
'select id, name from (select 1 as id, ''x'' as name) t where false';
57+
comment on function comment_single_empty_default() is 'HTTP GET
58+
@single';
59+
60+
-- Single with empty result, null_literal handling
61+
create function comment_single_empty_null_literal()
62+
returns table(id int, name text) language sql as
63+
'select id, name from (select 1 as id, ''x'' as name) t where false';
64+
comment on function comment_single_empty_null_literal() is 'HTTP GET
65+
@single
66+
response_null null_literal';
67+
68+
-- Single with empty result, no_content handling (204)
69+
create function comment_single_empty_no_content()
70+
returns table(id int, name text) language sql as
71+
'select id, name from (select 1 as id, ''x'' as name) t where false';
72+
comment on function comment_single_empty_no_content() is 'HTTP GET
73+
@single
74+
response_null no_content';
75+
76+
");
77+
}
78+
}
79+
80+
[Collection("TestFixture")]
81+
public class CommentSingleTests(TestFixture test)
82+
{
83+
[Fact]
84+
public async Task Test_single_setof_returns_scalar_not_array()
85+
{
86+
using var response = await test.Client.GetAsync("/api/comment-single-setof/");
87+
var content = await response.Content.ReadAsStringAsync();
88+
response.StatusCode.Should().Be(HttpStatusCode.OK);
89+
content.Should().Be("\"row1\"");
90+
}
91+
92+
[Fact]
93+
public async Task Test_single_table_returns_object_not_array()
94+
{
95+
using var response = await test.Client.GetAsync("/api/comment-single-table/");
96+
var content = await response.Content.ReadAsStringAsync();
97+
response.StatusCode.Should().Be(HttpStatusCode.OK);
98+
content.Should().Be("{\"id\":1,\"name\":\"alice\"}");
99+
}
100+
101+
[Fact]
102+
public async Task Test_single_one_row_returns_scalar()
103+
{
104+
using var response = await test.Client.GetAsync("/api/comment-single-one-row/");
105+
var content = await response.Content.ReadAsStringAsync();
106+
response.StatusCode.Should().Be(HttpStatusCode.OK);
107+
content.Should().Be("\"only_one\"");
108+
}
109+
110+
[Fact]
111+
public async Task Test_no_single_returns_array()
112+
{
113+
using var response = await test.Client.GetAsync("/api/comment-no-single-setof/");
114+
var content = await response.Content.ReadAsStringAsync();
115+
response.StatusCode.Should().Be(HttpStatusCode.OK);
116+
content.Should().Be("[\"row1\",\"row2\"]");
117+
}
118+
119+
[Fact]
120+
public async Task Test_single_record_alias()
121+
{
122+
using var response = await test.Client.GetAsync("/api/comment-single-record-alias/");
123+
var content = await response.Content.ReadAsStringAsync();
124+
response.StatusCode.Should().Be(HttpStatusCode.OK);
125+
content.Should().Be("\"alias_result\"");
126+
}
127+
128+
[Fact]
129+
public async Task Test_single_multi_row_table_returns_only_first()
130+
{
131+
using var response = await test.Client.GetAsync("/api/comment-single-multi-row-table/");
132+
var content = await response.Content.ReadAsStringAsync();
133+
response.StatusCode.Should().Be(HttpStatusCode.OK);
134+
content.Should().Be("{\"id\":1,\"val\":\"item_1\"}");
135+
}
136+
137+
[Fact]
138+
public async Task Test_single_named_column_returns_object_not_value()
139+
{
140+
using var response = await test.Client.GetAsync("/api/comment-single-named-column/");
141+
var content = await response.Content.ReadAsStringAsync();
142+
response.StatusCode.Should().Be(HttpStatusCode.OK);
143+
content.Should().Be("{\"val\":\"hello\"}");
144+
}
145+
146+
[Fact]
147+
public async Task Test_single_empty_default_returns_empty_string()
148+
{
149+
using var response = await test.Client.GetAsync("/api/comment-single-empty-default/");
150+
var content = await response.Content.ReadAsStringAsync();
151+
response.StatusCode.Should().Be(HttpStatusCode.OK);
152+
content.Should().Be("");
153+
}
154+
155+
[Fact]
156+
public async Task Test_single_empty_null_literal_returns_null()
157+
{
158+
using var response = await test.Client.GetAsync("/api/comment-single-empty-null-literal/");
159+
var content = await response.Content.ReadAsStringAsync();
160+
response.StatusCode.Should().Be(HttpStatusCode.OK);
161+
content.Should().Be("null");
162+
}
163+
164+
[Fact]
165+
public async Task Test_single_empty_no_content_returns_204()
166+
{
167+
using var response = await test.Client.GetAsync("/api/comment-single-empty-no-content/");
168+
response.StatusCode.Should().Be(HttpStatusCode.NoContent);
169+
}
170+
}

‎NpgsqlRestTests/SqlFileSourceTests/AdvancedFeatureTests/SqlFileAdvancedFixture.cs‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -222,6 +222,7 @@ private static void WriteSqlFiles(string dir)
222222
-- Content-Type: text/csv
223223
select 123 as n, true as b, 'hello' as t;
224224
""");
225+
225226
}
226227

227228
#pragma warning disable CA1816

0 commit comments

Comments
 (0)