Skip to content

Commit a020c78

Browse files
rojiroji
authored
Handle UDTs in non-public schemas in GetSchema (#4394)
Fixes #4392
1 parent 47de341 commit a020c78

2 files changed

Lines changed: 75 additions & 15 deletions

File tree

‎src/Npgsql/NpgsqlSchema.cs‎

Lines changed: 25 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -232,24 +232,34 @@ static async Task<DataTable> GetColumns(NpgsqlConnection conn, string?[]? restri
232232
{
233233
var columns = new DataTable("Columns") { Locale = CultureInfo.InvariantCulture };
234234

235-
columns.Columns.AddRange(new[] {
236-
new DataColumn("table_catalog"), new DataColumn("table_schema"), new DataColumn("table_name"),
237-
new DataColumn("column_name"), new DataColumn("ordinal_position", typeof(int)), new DataColumn("column_default"),
238-
new DataColumn("is_nullable"), new DataColumn("data_type"),
239-
new DataColumn("character_maximum_length", typeof(int)), new DataColumn("character_octet_length", typeof(int)),
240-
new DataColumn("numeric_precision", typeof(int)), new DataColumn("numeric_precision_radix", typeof(int)),
241-
new DataColumn("numeric_scale", typeof(int)), new DataColumn("datetime_precision", typeof(int)),
242-
new DataColumn("character_set_catalog"), new DataColumn("character_set_schema"),
243-
new DataColumn("character_set_name"), new DataColumn("collation_catalog")
235+
columns.Columns.AddRange(new DataColumn[] {
236+
new("table_catalog"), new("table_schema"), new("table_name"), new("column_name"),
237+
new("ordinal_position", typeof(int)),
238+
new("column_default"),
239+
new("is_nullable"),
240+
new("data_type"),
241+
new("character_maximum_length", typeof(int)), new("character_octet_length", typeof(int)),
242+
new("numeric_precision", typeof(int)), new("numeric_precision_radix", typeof(int)), new("numeric_scale", typeof(int)),
243+
new("datetime_precision", typeof(int)),
244+
new("character_set_catalog"), new("character_set_schema"), new("character_set_name"),
245+
new("collation_catalog")
244246
});
245247

246248
var getColumns = new StringBuilder(@"
247249
SELECT
248-
table_catalog, table_schema, table_name, column_name, ordinal_position, column_default, is_nullable,
249-
udt_name::regtype::text AS data_type, character_maximum_length, character_octet_length, numeric_precision,
250-
numeric_precision_radix, numeric_scale, datetime_precision, character_set_catalog, character_set_schema,
251-
character_set_name, collation_catalog
252-
FROM information_schema.columns");
250+
table_catalog, table_schema, table_name, column_name,
251+
ordinal_position,
252+
column_default,
253+
is_nullable,
254+
CASE WHEN udt_schema is NULL THEN udt_name ELSE format_type(typ.oid, NULL) END AS data_type,
255+
character_maximum_length, character_octet_length,
256+
numeric_precision, numeric_precision_radix, numeric_scale,
257+
datetime_precision,
258+
character_set_catalog, character_set_schema, character_set_name,
259+
collation_catalog
260+
FROM information_schema.columns
261+
JOIN pg_namespace AS ns ON ns.nspname = udt_schema
262+
JOIN pg_type AS typ ON typnamespace = ns.oid AND typname = udt_name");
253263

254264
using var command = BuildCommand(conn, getColumns, restrictions, "table_catalog", "table_schema", "table_name", "column_name");
255265
using var adapter = new NpgsqlDataAdapter(command);
@@ -858,4 +868,4 @@ static DataTable GetReservedWords()
858868
};
859869

860870
#endregion Reserved Keywords
861-
}
871+
}

‎test/Npgsql.Tests/SchemaTests.cs‎

Lines changed: 50 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -541,6 +541,56 @@ CONSTRAINT types_table_pkey PRIMARY KEY(p0)
541541
}
542542
}
543543

544+
[Test, IssueLink("https://github.com/npgsql/npgsql/issues/4392")]
545+
public async Task Enum_in_public_schema()
546+
{
547+
using var conn = OpenConnection();
548+
try
549+
{
550+
conn.ExecuteNonQuery(@"
551+
CREATE TYPE colors AS ENUM ('red', 'yellow', 'blue');
552+
553+
DROP TABLE IF EXISTS data;
554+
CREATE TABLE data (color colors);");
555+
556+
var dataTable = await GetSchema(conn, "Columns", new[] { null, null, "data" });
557+
var row = dataTable.Rows.Cast<DataRow>().Single();
558+
Assert.That(row["data_type"], Is.EqualTo("colors"));
559+
}
560+
finally
561+
{
562+
conn.ExecuteNonQuery(@"
563+
DROP TABLE IF EXISTS data;
564+
DROP TYPE IF EXISTS colors;");
565+
}
566+
}
567+
568+
[Test, IssueLink("https://github.com/npgsql/npgsql/issues/4392")]
569+
public async Task Enum_in_non_public_schema()
570+
{
571+
using var conn = OpenConnection();
572+
try
573+
{
574+
conn.ExecuteNonQuery(@"
575+
DROP SCHEMA IF EXISTS enum_schema CASCADE;
576+
CREATE SCHEMA enum_schema;
577+
CREATE TYPE enum_schema.colors AS ENUM ('red', 'yellow', 'blue');
578+
579+
DROP TABLE IF EXISTS data;
580+
CREATE TABLE data (color enum_schema.colors);");
581+
582+
var dataTable = await GetSchema(conn, "Columns", new[] { null, null, "data" });
583+
var row = dataTable.Rows.Cast<DataRow>().Single();
584+
Assert.That(row["data_type"], Is.EqualTo("enum_schema.colors"));
585+
}
586+
finally
587+
{
588+
conn.ExecuteNonQuery(@"
589+
DROP TABLE IF EXISTS data;
590+
DROP SCHEMA IF EXISTS enum_schema CASCADE;");
591+
}
592+
}
593+
544594
public SchemaTests(SyncOrAsync syncOrAsync) : base(syncOrAsync) { }
545595

546596
async Task<DataTable> GetSchema(NpgsqlConnection conn)

0 commit comments

Comments
 (0)