Skip to content

Commit fea1676

Browse files
vbilopavvbilopav
committed
finalize custom types support
1 parent f1240a1 commit fea1676

13 files changed

Lines changed: 617 additions & 49 deletions

‎NpgsqlRest/Extensions.cs‎

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,7 +6,16 @@ public static class Ext
66
{
77
public static T Get<T>(this NpgsqlDataReader reader, int ordinal)
88
{
9-
var value = reader[ordinal];
9+
object? value;
10+
if (typeof(T) == typeof(short?[]))
11+
{
12+
value = reader.GetFieldValue<short?[]>(ordinal);
13+
}
14+
else
15+
{
16+
value = reader[ordinal];
17+
}
18+
1019
if (value == DBNull.Value)
1120
{
1221
return default!;

‎NpgsqlRest/NpgsqlRest.csproj‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -40,7 +40,7 @@
4040

4141
<ItemGroup>
4242
<PackageReference Include="Microsoft.SourceLink.GitHub" Version="8.0.0" PrivateAssets="All" />
43-
<PackageReference Include="Npgsql" Version="8.0.3" />
43+
<PackageReference Include="Npgsql" Version="8.0.4" />
4444
<PackageReferenceFiles Include="bin\$(Configuration)\$(AssemblyName).xml" />
4545
</ItemGroup>
4646

‎NpgsqlRest/NpgsqlRestOptions.cs‎

Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -49,7 +49,8 @@ public class NpgsqlRestOptions(
4949
bool returnNpgsqlExceptionMessage = true,
5050
Dictionary<string, int>? postgreSqlErrorCodeToHttpStatusCodeMapping = null,
5151
Action<NpgsqlConnection, Routine, RoutineEndpoint, HttpContext>? beforeConnectionOpen = null,
52-
Dictionary<string, StringValues>? customRequestHeaders = null)
52+
Dictionary<string, StringValues>? customRequestHeaders = null,
53+
string? customTypeParameterSeparator = ".")
5354
{
5455

5556
/// <summary>
@@ -290,5 +291,11 @@ public NpgsqlRestOptions() : this(null)
290291
/// </summary>
291292
public Dictionary<string, StringValues> CustomRequestHeaders { get; set; } = customRequestHeaders ?? [];
292293

294+
/// <summary>
295+
/// Custom type parameter separator. Default is `.`.
296+
/// </summary>
297+
public string? CustomTypeParameterSeparator { get; set; } = customTypeParameterSeparator;
298+
299+
293300
internal List<IRoutineSource> RoutineSources { get; set; } = [new RoutineSource()];
294301
}

‎NpgsqlRest/RoutineSource.cs‎

Lines changed: 42 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -2,7 +2,6 @@
22
using Npgsql;
33
using NpgsqlTypes;
44
using NpgsqlRest.Extensions;
5-
using System.Text.RegularExpressions;
65

76
namespace NpgsqlRest;
87

@@ -18,7 +17,6 @@ public class RoutineSource(
1817
string? query = null,
1918
CommentsMode? commentsMode = null) : IRoutineSource
2019
{
21-
private readonly IRoutineSourceParameterFormatter _formatter = new RoutineSourceParameterFormatter();
2220
public string? SchemaSimilarTo { get; set; } = schemaSimilarTo;
2321
public string? SchemaNotSimilarTo { get; set; } = schemaNotSimilarTo;
2422
public string[]? IncludeSchemas { get; set; } = includeSchemas;
@@ -166,20 +164,49 @@ public class RoutineSource(
166164
schema, ".",
167165
name, "(",
168166
paramCount == 0 ? ")" : ""));
169-
167+
168+
169+
var customTypeNames = reader.Get<string?[]>(18);
170+
var customTypeTypes = reader.Get<string?[]>(19);
171+
var customTypePositions = reader.Get<short?[]>(20);
172+
170173
TypeDescriptor[] descriptors;
174+
bool hasCustomType = false;
171175
if (paramCount > 0)
172176
{
173177
descriptors = new TypeDescriptor[paramCount];
174178
for (var i = 0; i < paramCount; i++)
175179
{
176180
var paramName = paramNames[i];
181+
var originalParameterName = paramName;
182+
var customTypeName = customTypeNames[i];
183+
string? customType;
184+
if (customTypeName != null)
185+
{
186+
customType = paramTypes[i];
187+
paramTypes[i] = customTypeTypes[i] ?? customType;
188+
paramName = string.Concat(paramName, options.CustomTypeParameterSeparator, customTypeName);
189+
paramNames[i] = paramName;
190+
if (hasCustomType is false)
191+
{
192+
hasCustomType = true;
193+
}
194+
}
195+
else
196+
{
197+
customType = null;
198+
}
177199
var defaultValue = paramDefaults[i];
178200
var paramType = paramTypes[i];
179201
var fullParamType = defaultValue == null ? paramType : $"{paramType} DEFAULT {defaultValue}";
180202
simpleDefinition
181203
.AppendLine(string.Concat(" ", paramName, " ", fullParamType, i == paramCount - 1 ? "" : ","));
182-
descriptors[i] = new TypeDescriptor(paramType, hasDefault: hasParamDefaults[i]);
204+
descriptors[i] = new TypeDescriptor(
205+
paramType,
206+
hasDefault: hasParamDefaults[i],
207+
customType: customType,
208+
customTypePosition: customTypePositions[i],
209+
originalParameterName: originalParameterName);
183210
}
184211
simpleDefinition.AppendLine(")");
185212
}
@@ -213,6 +240,16 @@ public class RoutineSource(
213240
}
214241
}
215242

243+
IRoutineSourceParameterFormatter formatter;
244+
if (hasCustomType is false)
245+
{
246+
formatter = new RoutineSourceParameterFormatter();
247+
}
248+
else
249+
{
250+
formatter = new RoutineSourceCustomTypesParameterFormatter();
251+
}
252+
216253
yield return (
217254
new Routine(
218255
type: routineType,
@@ -245,7 +282,7 @@ public class RoutineSource(
245282
'i' => "immutable",
246283
_ => "other"
247284
}]),
248-
_formatter);
285+
formatter);
249286
}
250287

251288
yield break;

‎NpgsqlRest/RoutineSourceParameterFormatter.cs‎

Lines changed: 45 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,7 @@
1-
namespace NpgsqlRest;
1+
using System.Text;
2+
using Microsoft.Extensions.Primitives;
3+
4+
namespace NpgsqlRest;
25

36
public class RoutineSourceParameterFormatter : IRoutineSourceParameterFormatter
47
{
@@ -32,3 +35,44 @@ public string AppendCommandParameter(ref NpgsqlRestParameter parameter, ref int
3235

3336
public string? AppendEmpty() => ")";
3437
}
38+
39+
public class RoutineSourceCustomTypesParameterFormatter : IRoutineSourceParameterFormatter
40+
{
41+
public bool IsFormattable { get; } = true;
42+
43+
public string FormatCommand(ref Routine routine, ref List<NpgsqlRestParameter> parameters)
44+
{
45+
var sb = new StringBuilder(routine.Expression);
46+
var count = parameters.Count;
47+
for (var i = 0; i < count; i++)
48+
{
49+
var parameter = parameters[i];
50+
var suffix = parameter.TypeDescriptor.IsCastToText() ? $"::{parameter.TypeDescriptor.OriginalType}" : "";
51+
if (i > 0)
52+
{
53+
sb.Append(',');
54+
}
55+
if (parameter.TypeDescriptor.CustomType is null)
56+
{
57+
sb.Append(parameter.ActualName is null ?
58+
string.Concat("$", (i + 1).ToString(), suffix) :
59+
string.Concat(parameter.ActualName, "=>$", (i+1).ToString()));
60+
}
61+
else
62+
{
63+
if (parameter.TypeDescriptor.CustomTypePosition == 1)
64+
{
65+
sb.Append(parameter.TypeDescriptor.OriginalParameterName);
66+
sb.Append("=>row(");
67+
}
68+
sb.Append(string.Concat("$", (i + 1).ToString(), suffix));
69+
if (i == count - 1 || parameter.TypeDescriptor.CustomTypePosition != parameters[i + 1].TypeDescriptor.CustomTypePosition - 1)
70+
{
71+
sb.Append(string.Concat(")::", parameters[i].TypeDescriptor.CustomType));
72+
}
73+
}
74+
}
75+
sb.Append(')');
76+
return sb.ToString();
77+
}
78+
}

‎NpgsqlRest/RoutineSourceQuery.cs‎

Lines changed: 59 additions & 25 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,36 @@
33
internal class RoutineSourceQuery
44
{
55
public const string Query = """
6-
with r as (
6+
with s as (
7+
8+
select
9+
array_agg(nspname) as s
10+
from
11+
pg_catalog.pg_namespace
12+
where
13+
nspname not like 'pg_%'
14+
and nspname <> 'information_schema'
15+
and ($1 is null or nspname similar to $1)
16+
and ($2 is null or nspname not similar to $2)
17+
and ($3 is null or nspname = any($3))
18+
and ($4 is null or not nspname = any($4))
19+
), t as (
20+
21+
select
22+
n.nspname::text as schema,
23+
t.typname::text as name,
24+
a.attnum as att_pos,
25+
quote_ident(a.attname) as att_name,
26+
pg_catalog.format_type(a.atttypid, a.atttypmod) as att_type
27+
from
28+
pg_catalog.pg_type t
29+
join pg_catalog.pg_namespace n on n.oid = t.typnamespace
30+
join s on n.nspname = any(s.s)
31+
join pg_catalog.pg_class c on t.typrelid = c.oid and c.relkind = 'c'
32+
join pg_catalog.pg_attribute a on t.typrelid = a.attrelid and a.attisdropped is false
33+
34+
), r as (
35+
736
select
837
r.routine_type,
938
r.specific_schema,
@@ -18,32 +47,29 @@ with r as (
1847
quote_ident(p.parameter_name::text) as param_name,
1948
p.parameter_mode = 'IN' or p.parameter_mode = 'INOUT' as is_in_param,
2049
p.parameter_mode = 'INOUT' or p.parameter_mode = 'OUT' as is_out_param,
21-
p.ordinal_position as param_position,
22-
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end as param_type
50+
51+
row_number() over (partition by r.specific_schema, r.specific_name order by p.ordinal_position, t.att_pos) as param_position,
52+
case when p.data_type = 'bit' then 'varbit' else (p.udt_schema || '.' || p.udt_name)::regtype::text end as param_type,
53+
54+
t.att_name as custom_type_name,
55+
t.att_type as custom_type_type,
56+
t.att_pos as custom_type_pos
2357
2458
from information_schema.routines r
59+
join s on r.specific_schema = any(s.s)
2560
left join information_schema.parameters p on r.specific_name = p.specific_name and r.specific_schema = p.specific_schema
61+
left join t on r.specific_schema = t.schema and p.data_type = 'USER-DEFINED' and (p.udt_schema || '.' || p.udt_name)::regtype::text = t.name
62+
2663
where
27-
r.specific_schema = any(
28-
select
29-
nspname
30-
from
31-
pg_catalog.pg_namespace
32-
where
33-
nspname not like 'pg_%'
34-
and nspname <> 'information_schema'
35-
and ($1 is null or nspname similar to $1)
36-
and ($2 is null or nspname not similar to $2)
37-
and ($3 is null or nspname = any($3))
38-
and ($4 is null or not nspname = any($4))
39-
)
40-
and ($5 is null or r.routine_name similar to $5)
41-
and ($6 is null or r.routine_name not similar to $6)
42-
and ($7 is null or r.routine_name = any($7))
43-
and ($8 is null or not r.routine_name = any($8))
44-
and not lower(r.external_language) = any(array['c', 'internal'])
45-
and coalesce(r.type_udt_name, '') <> 'trigger'
46-
and r.routine_type in ('FUNCTION', 'PROCEDURE')
64+
not lower(r.external_language) = any(array['c', 'internal'])
65+
and coalesce(r.type_udt_name, '') <> 'trigger'
66+
and r.routine_type in ('FUNCTION', 'PROCEDURE')
67+
and ($5 is null or r.routine_name similar to $5)
68+
and ($6 is null or r.routine_name not similar to $6)
69+
and ($7 is null or r.routine_name = any($7))
70+
and ($8 is null or not r.routine_name = any($8))
71+
order by
72+
r.specific_schema, r.specific_name, p.ordinal_position, t.att_pos
4773
4874
), cte1 as (
4975
@@ -78,7 +104,11 @@ when r.is_user_defined then null
78104
79105
r.type_udt_schema,
80106
r.type_udt_name,
81-
r.data_type
107+
r.data_type,
108+
109+
coalesce(array_agg(r.custom_type_name order by r.param_position), '{}'::text[]) as custom_type_names,
110+
coalesce(array_agg(r.custom_type_type order by r.param_position), '{}'::text[]) as custom_type_types,
111+
coalesce(array_agg(r.custom_type_pos order by r.param_position), '{}'::smallint[]) as custom_type_positions
82112
from
83113
r
84114
join pg_catalog.pg_proc proc on r.specific_name = proc.proname || '_' || proc.oid
@@ -152,8 +182,12 @@ when array_length(coalesce(cte1.out_param_types, cte2.out_param_types), 1) is nu
152182
in_param_types as param_types,
153183
arguments_def,
154184
has_variadic,
155-
pg_get_functiondef(oid) as definition
185+
pg_get_functiondef(oid) as definition,
186+
custom_type_names,
187+
custom_type_types,
188+
custom_type_positions
156189
from cte1
157190
left join cte2 on cte1.schema = cte2.schema and cte1.specific_name = cte2.specific_name
191+
158192
""";
159193
}

‎NpgsqlRest/TypeDescriptor.cs‎

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -20,8 +20,18 @@ public readonly struct TypeDescriptor
2020
public bool NeedsEscape { get; }
2121
public bool IsPk { get; }
2222
public bool IsIdentity { get; }
23+
public string? CustomType { get; }
24+
public short? CustomTypePosition { get; }
25+
public string? OriginalParameterName { get; }
2326

24-
public TypeDescriptor(string type, bool hasDefault = false, bool isPk = false, bool isIdentity = false)
27+
public TypeDescriptor(
28+
string type,
29+
bool hasDefault = false,
30+
bool isPk = false,
31+
bool isIdentity = false,
32+
string? customType = null,
33+
short? customTypePosition = null,
34+
string? originalParameterName = null)
2535
{
2636
OriginalType = type;
2737
HasDefault = hasDefault;
@@ -89,6 +99,10 @@ public TypeDescriptor(string type, bool hasDefault = false, bool isPk = false, b
8999
BaseDbType == NpgsqlDbType.LTxtQuery ||
90100
BaseDbType == NpgsqlDbType.TsVector ||
91101
BaseDbType == NpgsqlDbType.Hstore;
102+
103+
CustomType = customType;
104+
CustomTypePosition = customTypePosition;
105+
OriginalParameterName = originalParameterName;
92106
}
93107

94108
public bool IsCastToText() => CastToText(BaseDbType);

‎NpgsqlRestClient/NpgsqlRestClient.csproj‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
<InvariantGlobalization>true</InvariantGlobalization>
88
<NoDefaultLaunchSettingsFile>true</NoDefaultLaunchSettingsFile>
99
<PublishAot>true</PublishAot>
10-
<Version>2.1.0</Version>
10+
<Version>2.2.0</Version>
1111
</PropertyGroup>
1212

1313
<ItemGroup>

0 commit comments

Comments
 (0)