1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
SET client_min_messages=NOTICE;
CREATE EXTENSION multicorn;
create or replace function create_foreign_server() returns void as $block$
DECLARE
current_db varchar;
BEGIN
SELECT into current_db current_database();
EXECUTE $$
CREATE server multicorn_srv foreign data wrapper multicorn options (
wrapper 'multicorn.sqlalchemyfdw.SqlAlchemyFdw',
db_url 'postgresql://$$ || current_user || '@localhost/' || current_db || $$'
);
$$;
END;
$block$ language plpgsql;
select create_foreign_server();
create_foreign_server
-----------------------
(1 row)
CREATE SCHEMA local_schema;
CREATE TABLE local_schema.t1 (
c1 int primary key,
c2 text,
c3 timestamp,
c4 numeric
);
CREATE TABLE local_schema.t2 (
c1 int,
c2 text,
c3 timestamp,
c4 numeric
);
CREATE TABLE local_schema.t3 (
c1 int,
c2 text,
c3 timestamp,
c4 numeric
);
CREATE SCHEMA remote_schema;
IMPORT FOREIGN SCHEMA local_schema FROM SERVER multicorn_srv INTO remote_schema ;
\d remote_schema.t1
Foreign table "remote_schema.t1"
Column | Type | Modifiers | FDW Options
--------+-----------------------------+-----------+-------------
c1 | integer | |
c2 | text | |
c3 | timestamp without time zone | |
c4 | numeric | |
Server: multicorn_srv
FDW Options: (primary_key 'c1', schema 'local_schema', tablename 't1')
\d remote_schema.t2
Foreign table "remote_schema.t2"
Column | Type | Modifiers | FDW Options
--------+-----------------------------+-----------+-------------
c1 | integer | |
c2 | text | |
c3 | timestamp without time zone | |
c4 | numeric | |
Server: multicorn_srv
FDW Options: (schema 'local_schema', tablename 't2')
\d remote_schema.t3
Foreign table "remote_schema.t3"
Column | Type | Modifiers | FDW Options
--------+-----------------------------+-----------+-------------
c1 | integer | |
c2 | text | |
c3 | timestamp without time zone | |
c4 | numeric | |
Server: multicorn_srv
FDW Options: (schema 'local_schema', tablename 't3')
SELECT * FROM remote_schema.t1;
c1 | c2 | c3 | c4
----+----+----+----
(0 rows)
INSERT INTO remote_schema.t1 VALUES (1, '2', NULL, NULL);
SELECT * FROM remote_schema.t1;
c1 | c2 | c3 | c4
----+----+----+----
1 | 2 | |
(1 row)
DROP SCHEMA remote_schema CASCADE;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to foreign table remote_schema.t1
drop cascades to foreign table remote_schema.t2
drop cascades to foreign table remote_schema.t3
CREATE SCHEMA remote_schema;
IMPORT FOREIGN SCHEMA local_schema LIMIT TO (t1) FROM SERVER multicorn_srv INTO remote_schema ;
SELECT relname FROM pg_class c INNER JOIN pg_namespace n on c.relnamespace = n.oid WHERE n.nspname = 'remote_schema';
relname
---------
t1
(1 row)
IMPORT FOREIGN SCHEMA local_schema EXCEPT (t1, t3) FROM SERVER multicorn_srv INTO remote_schema ;
SELECT relname FROM pg_class c INNER JOIN pg_namespace n on c.relnamespace = n.oid WHERE n.nspname = 'remote_schema';
relname
---------
t1
t2
(2 rows)
DROP EXTENSION multicorn CASCADE;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to server multicorn_srv
drop cascades to foreign table remote_schema.t1
drop cascades to foreign table remote_schema.t2
DROP SCHEMA local_schema CASCADE;
NOTICE: drop cascades to 3 other objects
DETAIL: drop cascades to table local_schema.t1
drop cascades to table local_schema.t2
drop cascades to table local_schema.t3
DROP SCHEMA remote_schema CASCADE;
|
