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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
CREATE EXTENSION ogr_fdw;
CREATE SERVER myserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '@abs_srcdir@/data',
format 'ESRI Shapefile' );
------------------------------------------------
CREATE FOREIGN TABLE pt_1 (
fid integer,
geom bytea,
name varchar,
age integer,
height double precision,
birthdate date )
SERVER myserver
OPTIONS ( layer 'pt_two' );
SELECT * FROM pt_1 WHERE fid = 1;
fid | geom | name | age | height | birthdate
-----+----------------------------------------------+------+-----+--------+------------
1 | \x010100000054e943acd697e2bfc0895ee54a46cf3f | Paul | 33 | 5.84 | 03-25-1971
(1 row)
------------------------------------------------
CREATE FOREIGN TABLE pt_2 (
fid integer,
name varchar )
SERVER myserver
OPTIONS ( layer 'pt_two' );
SELECT * FROM pt_2 ORDER BY name;
fid | name
-----+-------
1 | Paul
0 | Peter
(2 rows)
------------------------------------------------
CREATE FOREIGN TABLE pt_3 (
geom bytea,
name varchar )
SERVER myserver
OPTIONS ( layer 'pt_two' );
SELECT * FROM pt_3 ORDER BY name;
geom | name
----------------------------------------------+-------
\x010100000054e943acd697e2bfc0895ee54a46cf3f | Paul
\x0101000000c00497d1162cb93f8cbaef08a080e63f | Peter
(2 rows)
------------------------------------------------
CREATE FOREIGN TABLE poly_1 (
fid bigint,
geom bytea,
id double precision,
name varchar(5)
) SERVER myserver
OPTIONS (layer 'poly');
SELECT length(geom) FROM poly_1 WHERE name = 'Three';
length
--------
307
(1 row)
------------------------------------------------
-- Laundering and explicit column naming test
CREATE FOREIGN TABLE column_name_test (
fid integer,
name varchar OPTIONS (column_name '2ame'),
theage integer OPTIONS (column_name 'age'),
height real OPTIONS (column_name 'Height'),
birthdate date OPTIONS (column_name 'b-rthdate')
) SERVER myserver
OPTIONS (layer '2launder');
SELECT * FROM column_name_test ORDER BY fid;
fid | name | theage | height | birthdate
-----+-------+--------+--------+------------
0 | Peter | 45 | 5.6 | 04-12-1965
1 | Paul | 33 | 5.84 | 03-25-1971
(2 rows)
-- Check that columns are reverse-laundered when generating
-- OGR SQL filters
SET client_min_messages = debug1;
SELECT name FROM column_name_test WHERE name = 'Paul';
DEBUG: OGR SQL: ("2ame" = 'Paul')
name
------
Paul
(1 row)
SET client_min_messages = notice;
------------------------------------------------
-- GDAL options passing tests
CREATE SERVER myserver_latin1
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '@abs_srcdir@/data',
format 'ESRI Shapefile',
config_options 'SHAPE_ENCODING=LATIN1' );
CREATE FOREIGN TABLE e_1 (
fid integer,
name varchar )
SERVER myserver_latin1
OPTIONS ( layer 'enc' );
SET client_min_messages = debug1;
SELECT fid, name FROM e_1 WHERE fid = 1;
DEBUG: GDAL config option 'SHAPE_ENCODING' set to 'LATIN1'
DEBUG: OGR SQL: (fid = 1)
DEBUG: GDAL config option 'SHAPE_ENCODING' set to 'LATIN1'
fid | name
-----+------
1 | Pàul
(1 row)
SET client_min_messages = notice;
------------------------------------------------
-- Using encoding option directly
CREATE SERVER myserver_latin1_direct
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '@abs_srcdir@/data',
format 'ESRI Shapefile',
character_encoding 'LATIN1'
);
CREATE FOREIGN TABLE e_2 (
fid integer,
name varchar )
SERVER myserver_latin1_direct
OPTIONS ( layer 'enc' );
SELECT fid, name FROM e_2 WHERE fid = 1;
fid | name
-----+------
1 | Pàul
(1 row)
------------------------------------------------
-- Geometryless test
CREATE SERVER csvserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '@abs_srcdir@/data/no_geom.csv',
format 'CSV' );
CREATE FOREIGN TABLE no_geom (
fid bigint,
name varchar,
age varchar,
value varchar
) SERVER csvserver
OPTIONS (layer 'no_geom');
SELECT c.*
FROM generate_series(1,4) g
JOIN no_geom c
ON (c.fid = g.g);
fid | name | age | value
-----+---------+-----+-------
1 | Peter | 34 | 10.2
2 | John | 77 | 3.4
3 | Paul | 45 | 19.2
4 | Matthew | 35 | 18.2
(4 rows)
------------------------------------------------
-- FGDB test
CREATE SERVER fgdbserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource '/vsizip/@abs_srcdir@/data/Querying.zip/Querying.gdb',
format 'OpenFileGDB' );
CREATE FOREIGN TABLE cities (
fid bigint,
shape bytea,
city_fips varchar(5),
city_name varchar(40),
state_fips varchar(2),
state_name varchar(25),
state_city varchar(7),
type varchar(25),
capital varchar(1),
elevation integer,
pop1990 integer,
popcat integer
) SERVER "fgdbserver"
OPTIONS (layer 'Cities');
SET client_min_messages = LOG;
SELECT fid, city_name, pop1990 FROM cities WHERE pop1990 = 17710;
fid | city_name | pop1990
-----+--------------+---------
9 | Port Angeles | 17710
(1 row)
SELECT fid, city_name, pop1990 FROM cities WHERE city_name = 'Williston';
fid | city_name | pop1990
-----+-----------+---------
8 | Williston | 13131
(1 row)
|
