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
|
set client_min_messages=NOTICE;
------------------------------------------------
CREATE SCHEMA imp1;
IMPORT FOREIGN SCHEMA ogr_all
LIMIT TO (n2launder)
FROM SERVER myserver
INTO imp1;
NOTICE: Number of tables to be created 1
SELECT c.column_name, c.data_type, attfdwoptions
FROM information_schema._pg_foreign_table_columns AS fc
INNER JOIN information_schema.columns AS c ON
fc.nspname = c.table_schema AND fc.relname = c.table_name
AND fc.attname = c.column_name
WHERE fc.nspname = 'imp1' and fc.relname = 'n2launder'
ORDER BY c.ordinal_position;
column_name | data_type | attfdwoptions
-------------+-------------------+-------------------------
fid | bigint |
geom | bytea |
n2ame | character varying | {column_name=2ame}
age | integer |
height | double precision |
b_rthdate | date | {column_name=b-rthdate}
(6 rows)
SELECT * FROM imp1.n2launder WHERE fid = 0;
fid | geom | n2ame | age | height | b_rthdate
-----+----------------------------------------------+-------+-----+--------+------------
0 | \x0101000000c00497d1162cb93f8cbaef08a080e63f | Peter | 45 | 5.6 | 04-12-1965
(1 row)
------------------------------------------------
CREATE SCHEMA imp2;
IMPORT FOREIGN SCHEMA ogr_all
LIMIT TO ("natural")
FROM SERVER myserver
INTO imp2;
NOTICE: Number of tables to be created 1
SELECT c.column_name, c.data_type, attfdwoptions
FROM information_schema._pg_foreign_table_columns AS fc
INNER JOIN information_schema.columns AS c ON
fc.nspname = c.table_schema AND fc.relname = c.table_name
AND fc.attname = c.column_name
WHERE fc.nspname = 'imp2' and fc.relname = 'natural'
ORDER BY c.ordinal_position;
column_name | data_type | attfdwoptions
-------------+-------------------+---------------
fid | bigint |
id | double precision |
natural | character varying |
(3 rows)
SELECT "natural" FROM imp2."natural";
natural
---------
wood
land
(2 rows)
------------------------------------------------
CREATE SERVER svr_test_apost
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '@abs_srcdir@/data/no_geom_apost.csv',
format 'CSV' );
CREATE SCHEMA imp3;
IMPORT FOREIGN SCHEMA ogr_all
LIMIT TO (no_geom_apost)
FROM SERVER svr_test_apost
INTO imp3;
NOTICE: Number of tables to be created 1
SELECT c.column_name, c.data_type, attfdwoptions
FROM information_schema._pg_foreign_table_columns AS fc
INNER JOIN information_schema.columns AS c ON
fc.nspname = c.table_schema AND fc.relname = c.table_name
AND fc.attname = c.column_name
WHERE fc.nspname = 'imp3' and fc.relname = 'no_geom_apost'
ORDER BY c.ordinal_position;
column_name | data_type | attfdwoptions
----------------+-------------------+--------------------------------
fid | bigint |
name | character varying |
age | character varying |
person_s_value | character varying | {"column_name=person's value"}
(4 rows)
SELECT name,age FROM imp3.no_geom_apost WHERE name = 'Paul';
name | age
------+-----
Paul | 45
(1 row)
SELECT name,age FROM imp3.no_geom_apost WHERE name IS NULL;
name | age
------+-----
(0 rows)
SELECT name,age FROM imp3.no_geom_apost WHERE name = '';
name | age
------+-----
| 44
(1 row)
------------------------------------------------
|
