Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit59efda3

Browse files
committed
Implement IMPORT FOREIGN SCHEMA.
This command provides an automated way to create foreign table definitionsthat match remote tables, thereby reducing tedium and chances for error.In this patch, we provide the necessary core-server infrastructure andimplement the feature fully in the postgres_fdw foreign-data wrapper.Other wrappers will throw a "feature not supported" error until/unlessthey are updated.Ronan Dunklau and Michael Paquier, additional work by me
1 parent6a605cd commit59efda3

29 files changed

+1239
-15
lines changed

‎contrib/postgres_fdw/deparse.c

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,6 @@ static void deparseReturningList(StringInfo buf, PlannerInfo *root,
116116
staticvoiddeparseColumnRef(StringInfobuf,intvarno,intvarattno,
117117
PlannerInfo*root);
118118
staticvoiddeparseRelation(StringInfobuf,Relationrel);
119-
staticvoiddeparseStringLiteral(StringInfobuf,constchar*val);
120119
staticvoiddeparseExpr(Expr*expr,deparse_expr_cxt*context);
121120
staticvoiddeparseVar(Var*node,deparse_expr_cxt*context);
122121
staticvoiddeparseConst(Const*node,deparse_expr_cxt*context);
@@ -1160,7 +1159,7 @@ deparseRelation(StringInfo buf, Relation rel)
11601159
/*
11611160
* Append a SQL string literal representing "val" to buf.
11621161
*/
1163-
staticvoid
1162+
void
11641163
deparseStringLiteral(StringInfobuf,constchar*val)
11651164
{
11661165
constchar*valptr;

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 230 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2834,3 +2834,233 @@ NOTICE: NEW: (13,"test triggered !")
28342834
(0,27)
28352835
(1 row)
28362836

2837+
-- ===================================================================
2838+
-- test IMPORT FOREIGN SCHEMA
2839+
-- ===================================================================
2840+
CREATE SCHEMA import_source;
2841+
CREATE TABLE import_source.t1 (c1 int, c2 varchar NOT NULL);
2842+
CREATE TABLE import_source.t2 (c1 int default 42, c2 varchar NULL, c3 text collate "POSIX");
2843+
CREATE TYPE typ1 AS (m1 int, m2 varchar);
2844+
CREATE TABLE import_source.t3 (c1 timestamptz default now(), c2 typ1);
2845+
CREATE TABLE import_source."x 4" (c1 float8, "C 2" text, c3 varchar(42));
2846+
CREATE TABLE import_source."x 5" (c1 float8);
2847+
ALTER TABLE import_source."x 5" DROP COLUMN c1;
2848+
CREATE SCHEMA import_dest1;
2849+
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest1;
2850+
\det+ import_dest1
2851+
List of foreign tables
2852+
Schema | Table | Server | FDW Options | Description
2853+
--------------+-------+----------+-------------------------------------------------+-------------
2854+
import_dest1 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
2855+
import_dest1 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
2856+
import_dest1 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
2857+
import_dest1 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
2858+
import_dest1 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
2859+
(5 rows)
2860+
2861+
\d import_dest1.*
2862+
Foreign table "import_dest1.t1"
2863+
Column | Type | Modifiers | FDW Options
2864+
--------+-------------------+-----------+--------------------
2865+
c1 | integer | | (column_name 'c1')
2866+
c2 | character varying | not null | (column_name 'c2')
2867+
Server: loopback
2868+
FDW Options: (schema_name 'import_source', table_name 't1')
2869+
2870+
Foreign table "import_dest1.t2"
2871+
Column | Type | Modifiers | FDW Options
2872+
--------+-------------------+---------------+--------------------
2873+
c1 | integer | | (column_name 'c1')
2874+
c2 | character varying | | (column_name 'c2')
2875+
c3 | text | collate POSIX | (column_name 'c3')
2876+
Server: loopback
2877+
FDW Options: (schema_name 'import_source', table_name 't2')
2878+
2879+
Foreign table "import_dest1.t3"
2880+
Column | Type | Modifiers | FDW Options
2881+
--------+--------------------------+-----------+--------------------
2882+
c1 | timestamp with time zone | | (column_name 'c1')
2883+
c2 | typ1 | | (column_name 'c2')
2884+
Server: loopback
2885+
FDW Options: (schema_name 'import_source', table_name 't3')
2886+
2887+
Foreign table "import_dest1.x 4"
2888+
Column | Type | Modifiers | FDW Options
2889+
--------+-----------------------+-----------+---------------------
2890+
c1 | double precision | | (column_name 'c1')
2891+
C 2 | text | | (column_name 'C 2')
2892+
c3 | character varying(42) | | (column_name 'c3')
2893+
Server: loopback
2894+
FDW Options: (schema_name 'import_source', table_name 'x 4')
2895+
2896+
Foreign table "import_dest1.x 5"
2897+
Column | Type | Modifiers | FDW Options
2898+
--------+------+-----------+-------------
2899+
Server: loopback
2900+
FDW Options: (schema_name 'import_source', table_name 'x 5')
2901+
2902+
-- Options
2903+
CREATE SCHEMA import_dest2;
2904+
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest2
2905+
OPTIONS (import_default 'true');
2906+
\det+ import_dest2
2907+
List of foreign tables
2908+
Schema | Table | Server | FDW Options | Description
2909+
--------------+-------+----------+-------------------------------------------------+-------------
2910+
import_dest2 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
2911+
import_dest2 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
2912+
import_dest2 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
2913+
import_dest2 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
2914+
import_dest2 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
2915+
(5 rows)
2916+
2917+
\d import_dest2.*
2918+
Foreign table "import_dest2.t1"
2919+
Column | Type | Modifiers | FDW Options
2920+
--------+-------------------+-----------+--------------------
2921+
c1 | integer | | (column_name 'c1')
2922+
c2 | character varying | not null | (column_name 'c2')
2923+
Server: loopback
2924+
FDW Options: (schema_name 'import_source', table_name 't1')
2925+
2926+
Foreign table "import_dest2.t2"
2927+
Column | Type | Modifiers | FDW Options
2928+
--------+-------------------+---------------+--------------------
2929+
c1 | integer | default 42 | (column_name 'c1')
2930+
c2 | character varying | | (column_name 'c2')
2931+
c3 | text | collate POSIX | (column_name 'c3')
2932+
Server: loopback
2933+
FDW Options: (schema_name 'import_source', table_name 't2')
2934+
2935+
Foreign table "import_dest2.t3"
2936+
Column | Type | Modifiers | FDW Options
2937+
--------+--------------------------+---------------+--------------------
2938+
c1 | timestamp with time zone | default now() | (column_name 'c1')
2939+
c2 | typ1 | | (column_name 'c2')
2940+
Server: loopback
2941+
FDW Options: (schema_name 'import_source', table_name 't3')
2942+
2943+
Foreign table "import_dest2.x 4"
2944+
Column | Type | Modifiers | FDW Options
2945+
--------+-----------------------+-----------+---------------------
2946+
c1 | double precision | | (column_name 'c1')
2947+
C 2 | text | | (column_name 'C 2')
2948+
c3 | character varying(42) | | (column_name 'c3')
2949+
Server: loopback
2950+
FDW Options: (schema_name 'import_source', table_name 'x 4')
2951+
2952+
Foreign table "import_dest2.x 5"
2953+
Column | Type | Modifiers | FDW Options
2954+
--------+------+-----------+-------------
2955+
Server: loopback
2956+
FDW Options: (schema_name 'import_source', table_name 'x 5')
2957+
2958+
CREATE SCHEMA import_dest3;
2959+
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest3
2960+
OPTIONS (import_collate 'false', import_not_null 'false');
2961+
\det+ import_dest3
2962+
List of foreign tables
2963+
Schema | Table | Server | FDW Options | Description
2964+
--------------+-------+----------+-------------------------------------------------+-------------
2965+
import_dest3 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
2966+
import_dest3 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
2967+
import_dest3 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
2968+
import_dest3 | x 4 | loopback | (schema_name 'import_source', table_name 'x 4') |
2969+
import_dest3 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
2970+
(5 rows)
2971+
2972+
\d import_dest3.*
2973+
Foreign table "import_dest3.t1"
2974+
Column | Type | Modifiers | FDW Options
2975+
--------+-------------------+-----------+--------------------
2976+
c1 | integer | | (column_name 'c1')
2977+
c2 | character varying | | (column_name 'c2')
2978+
Server: loopback
2979+
FDW Options: (schema_name 'import_source', table_name 't1')
2980+
2981+
Foreign table "import_dest3.t2"
2982+
Column | Type | Modifiers | FDW Options
2983+
--------+-------------------+-----------+--------------------
2984+
c1 | integer | | (column_name 'c1')
2985+
c2 | character varying | | (column_name 'c2')
2986+
c3 | text | | (column_name 'c3')
2987+
Server: loopback
2988+
FDW Options: (schema_name 'import_source', table_name 't2')
2989+
2990+
Foreign table "import_dest3.t3"
2991+
Column | Type | Modifiers | FDW Options
2992+
--------+--------------------------+-----------+--------------------
2993+
c1 | timestamp with time zone | | (column_name 'c1')
2994+
c2 | typ1 | | (column_name 'c2')
2995+
Server: loopback
2996+
FDW Options: (schema_name 'import_source', table_name 't3')
2997+
2998+
Foreign table "import_dest3.x 4"
2999+
Column | Type | Modifiers | FDW Options
3000+
--------+-----------------------+-----------+---------------------
3001+
c1 | double precision | | (column_name 'c1')
3002+
C 2 | text | | (column_name 'C 2')
3003+
c3 | character varying(42) | | (column_name 'c3')
3004+
Server: loopback
3005+
FDW Options: (schema_name 'import_source', table_name 'x 4')
3006+
3007+
Foreign table "import_dest3.x 5"
3008+
Column | Type | Modifiers | FDW Options
3009+
--------+------+-----------+-------------
3010+
Server: loopback
3011+
FDW Options: (schema_name 'import_source', table_name 'x 5')
3012+
3013+
-- Check LIMIT TO and EXCEPT
3014+
CREATE SCHEMA import_dest4;
3015+
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t1, nonesuch)
3016+
FROM SERVER loopback INTO import_dest4;
3017+
\det+ import_dest4
3018+
List of foreign tables
3019+
Schema | Table | Server | FDW Options | Description
3020+
--------------+-------+----------+------------------------------------------------+-------------
3021+
import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
3022+
(1 row)
3023+
3024+
IMPORT FOREIGN SCHEMA import_source EXCEPT (t1, "x 4", nonesuch)
3025+
FROM SERVER loopback INTO import_dest4;
3026+
\det+ import_dest4
3027+
List of foreign tables
3028+
Schema | Table | Server | FDW Options | Description
3029+
--------------+-------+----------+-------------------------------------------------+-------------
3030+
import_dest4 | t1 | loopback | (schema_name 'import_source', table_name 't1') |
3031+
import_dest4 | t2 | loopback | (schema_name 'import_source', table_name 't2') |
3032+
import_dest4 | t3 | loopback | (schema_name 'import_source', table_name 't3') |
3033+
import_dest4 | x 5 | loopback | (schema_name 'import_source', table_name 'x 5') |
3034+
(4 rows)
3035+
3036+
-- Assorted error cases
3037+
IMPORT FOREIGN SCHEMA import_source FROM SERVER loopback INTO import_dest4;
3038+
ERROR: relation "t1" already exists
3039+
CONTEXT: importing foreign table "t1"
3040+
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO import_dest4;
3041+
ERROR: schema "nonesuch" is not present on foreign server "loopback"
3042+
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER loopback INTO notthere;
3043+
ERROR: schema "notthere" does not exist
3044+
IMPORT FOREIGN SCHEMA nonesuch FROM SERVER nowhere INTO notthere;
3045+
ERROR: server "nowhere" does not exist
3046+
-- Check case of a type present only on the remote server.
3047+
-- We can fake this by dropping the type locally in our transaction.
3048+
CREATE TYPE "Colors" AS ENUM ('red', 'green', 'blue');
3049+
CREATE TABLE import_source.t5 (c1 int, c2 text collate "C", "Col" "Colors");
3050+
CREATE SCHEMA import_dest5;
3051+
BEGIN;
3052+
DROP TYPE "Colors" CASCADE;
3053+
NOTICE: drop cascades to table import_source.t5 column Col
3054+
IMPORT FOREIGN SCHEMA import_source LIMIT TO (t5)
3055+
FROM SERVER loopback INTO import_dest5; -- ERROR
3056+
ERROR: type "public.Colors" does not exist
3057+
LINE 4: "Col" public."Colors" OPTIONS (column_name 'Col')
3058+
^
3059+
QUERY: CREATE FOREIGN TABLE t5 (
3060+
c1 integer OPTIONS (column_name 'c1'),
3061+
c2 text OPTIONS (column_name 'c2') COLLATE pg_catalog."C",
3062+
"Col" public."Colors" OPTIONS (column_name 'Col')
3063+
) SERVER loopback
3064+
OPTIONS (schema_name 'import_source', table_name 't5');
3065+
CONTEXT: importing foreign table "t5"
3066+
ROLLBACK;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp