diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out index 31b5de91ad..cfcb300337 100644 --- a/contrib/postgres_fdw/expected/postgres_fdw.out +++ b/contrib/postgres_fdw/expected/postgres_fdw.out @@ -8544,6 +8544,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); CREATE TABLE import_source."x 5" (c1 float8); ALTER TABLE import_source."x 5" DROP COLUMN c1; +CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored); CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 FOR VALUES FROM (1) TO (100); @@ -8561,7 +8562,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1; import_dest1 | t4 | loopback | (schema_name 'import_source', table_name 't4') | import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') | import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | -(6 rows) + import_dest1 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | +(7 rows) \d import_dest1.* Foreign table "import_dest1.t1" @@ -8611,6 +8613,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4') Server: loopback FDW options: (schema_name 'import_source', table_name 'x 5') + Foreign table "import_dest1.x 6" + Column | Type | Collation | Nullable | Default | FDW options +--------+---------+-----------+----------+---------+-------------------- + c1 | integer | | | | (column_name 'c1') + c2 | integer | | | | (column_name 'c2') +Server: loopback +FDW options: (schema_name 'import_source', table_name 'x 6') + -- Options CREATE SCHEMA import_dest2; IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 @@ -8625,7 +8635,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2 import_dest2 | t4 | loopback | (schema_name 'import_source', table_name 't4') | import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') | import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | -(6 rows) + import_dest2 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | +(7 rows) \d import_dest2.* Foreign table "import_dest2.t1" @@ -8675,6 +8686,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4') Server: loopback FDW options: (schema_name 'import_source', table_name 'x 5') + Foreign table "import_dest2.x 6" + Column | Type | Collation | Nullable | Default | FDW options +--------+---------+-----------+----------+---------+-------------------- + c1 | integer | | | | (column_name 'c1') + c2 | integer | | | | (column_name 'c2') +Server: loopback +FDW options: (schema_name 'import_source', table_name 'x 6') + CREATE SCHEMA import_dest3; IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 OPTIONS (import_collate 'false', import_not_null 'false'); @@ -8688,7 +8707,8 @@ IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3 import_dest3 | t4 | loopback | (schema_name 'import_source', table_name 't4') | import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') | import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | -(6 rows) + import_dest3 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | +(7 rows) \d import_dest3.* Foreign table "import_dest3.t1" @@ -8738,6 +8758,14 @@ FDW options: (schema_name 'import_source', table_name 'x 4') Server: loopback FDW options: (schema_name 'import_source', table_name 'x 5') + Foreign table "import_dest3.x 6" + Column | Type | Collation | Nullable | Default | FDW options +--------+---------+-----------+----------+---------+-------------------- + c1 | integer | | | | (column_name 'c1') + c2 | integer | | | | (column_name 'c2') +Server: loopback +FDW options: (schema_name 'import_source', table_name 'x 6') + -- Check LIMIT TO and EXCEPT CREATE SCHEMA import_dest4; IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch, t4_part) @@ -8762,7 +8790,8 @@ IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch, t4_part) import_dest4 | t4 | loopback | (schema_name 'import_source', table_name 't4') | import_dest4 | t4_part | loopback | (schema_name 'import_source', table_name 't4_part') | import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') | -(6 rows) + import_dest4 | x 6 | loopback | (schema_name 'import_source', table_name 'x 6') | +(7 rows) -- Assorted error cases IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4; diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c index fafbab6b02..35d738e9b6 100644 --- a/contrib/postgres_fdw/postgres_fdw.c +++ b/contrib/postgres_fdw/postgres_fdw.c @@ -5275,13 +5275,24 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) * include a schema name for types/functions in other schemas, which * is what we want. */ + appendStringInfoString(&buf, + "SELECT relname, " + " attname, " + " format_type(atttypid, atttypmod), " + " attnotnull, "); + + /* Generated columns are supported since Postgres 12 */ + if (PQserverVersion(conn) >= 120000) + appendStringInfoString(&buf, + " attgenerated, " + " pg_get_expr(adbin, adrelid), "); + else + appendStringInfoString(&buf, + " NULL, " + " pg_get_expr(adbin, adrelid), "); + if (import_collate) appendStringInfoString(&buf, - "SELECT relname, " - " attname, " - " format_type(atttypid, atttypmod), " - " attnotnull, " - " pg_get_expr(adbin, adrelid), " " collname, " " collnsp.nspname " "FROM pg_class c " @@ -5298,11 +5309,6 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) " collnsp.oid = collnamespace "); else appendStringInfoString(&buf, - "SELECT relname, " - " attname, " - " format_type(atttypid, atttypmod), " - " attnotnull, " - " pg_get_expr(adbin, adrelid), " " NULL, NULL " "FROM pg_class c " " JOIN pg_namespace n ON " @@ -5379,6 +5385,7 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) char *attname; char *typename; char *attnotnull; + char *attgenerated; char *attdefault; char *collname; char *collnamespace; @@ -5390,12 +5397,14 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) attname = PQgetvalue(res, i, 1); typename = PQgetvalue(res, i, 2); attnotnull = PQgetvalue(res, i, 3); - attdefault = PQgetisnull(res, i, 4) ? (char *) NULL : + attgenerated = PQgetisnull(res, i, 4) ? (char *) NULL : PQgetvalue(res, i, 4); - collname = PQgetisnull(res, i, 5) ? (char *) NULL : + attdefault = PQgetisnull(res, i, 5) ? (char *) NULL : PQgetvalue(res, i, 5); - collnamespace = PQgetisnull(res, i, 6) ? (char *) NULL : + collname = PQgetisnull(res, i, 6) ? (char *) NULL : PQgetvalue(res, i, 6); + collnamespace = PQgetisnull(res, i, 7) ? (char *) NULL : + PQgetvalue(res, i, 7); if (first_item) first_item = false; @@ -5423,7 +5432,8 @@ postgresImportForeignSchema(ImportForeignSchemaStmt *stmt, Oid serverOid) quote_identifier(collname)); /* Add DEFAULT if needed */ - if (import_default && attdefault != NULL) + if (import_default && attdefault != NULL && + (!attgenerated || !attgenerated[0])) appendStringInfo(&buf, " DEFAULT %s", attdefault); /* Add NOT NULL if needed */ diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql index 286dd99573..ca0d703191 100644 --- a/contrib/postgres_fdw/sql/postgres_fdw.sql +++ b/contrib/postgres_fdw/sql/postgres_fdw.sql @@ -2499,6 +2499,7 @@ CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1); CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42)); CREATE TABLE import_source."x 5" (c1 float8); ALTER TABLE import_source."x 5" DROP COLUMN c1; +CREATE TABLE import_source."x 6" (c1 int, c2 int generated always as (c1 * 2) stored); CREATE TABLE import_source.t4 (c1 int) PARTITION BY RANGE (c1); CREATE TABLE import_source.t4_part PARTITION OF import_source.t4 FOR VALUES FROM (1) TO (100);