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
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
|
----------------------------------------------------------------------
-- Create local table
CREATE TABLE bytea_local (
fid serial primary key,
geom bytea,
name varchar,
age bigint,
size integer,
value float8,
num numeric(6,2),
dt date,
tm time,
dttm timestamp,
varch char(8),
yn char
);
----------------------------------------------------------------------
-- Populate local table
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn) VALUES
('Jim', '14232'::bytea, 23, 1, 4.3, 5.5, '2010-10-10'::date, '13:23:21'::time, '2010-10-10 13:23:21'::timestamp, 'this', 'y' ),
('Marvin', '55555'::bytea, 34, 2, 5.4, 10.13, '2011-11-11'::date, '15:21:45'::time, '2011-11-11 15:21:45'::timestamp, 'that', 'n' ),
(NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
----------------------------------------------------------------------
-- Create remote table
CREATE SERVER pgserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'PG:dbname=contrib_regression host=localhost',
format 'PostgreSQL' );
CREATE FOREIGN TABLE bytea_fdw (
fid bigint,
geom bytea,
name varchar,
age bigint,
size integer,
value float8,
num numeric(6,2),
dt date,
tm time,
dttm timestamp,
varch char(8),
yn char
) SERVER pgserver OPTIONS (layer 'bytea_local');
SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn FROM bytea_fdw;
fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
-----+--------+--------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
1 | Jim | \x3134323332 | 23 | 1 | 4.3 | 5.50 | 10-10-2010 | 13:23:21 | Sun Oct 10 13:23:21 2010 | this | y
2 | Marvin | \x3535353535 | 34 | 2 | 5.4 | 10.13 | 11-11-2011 | 15:21:45 | Fri Nov 11 15:21:45 2011 | that | n
3 | | | | | | | | | | |
(3 rows)
SELECT a.name, b.name
FROM bytea_local a
JOIN bytea_fdw b
USING (fid);
name | name
--------+--------
Jim | Jim
Marvin | Marvin
|
(3 rows)
EXPLAIN VERBOSE
SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
FROM bytea_fdw;
QUERY PLAN
-----------------------------------------------------------------------------
Foreign Scan on public.bytea_fdw (cost=25.00..1025.00 rows=1000 width=170)
Output: fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
(2 rows)
----------------------------------------------------------------------
-- Remote Query and OGR SQL pushdown
SET client_min_messages = DEBUG1;
SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
FROM bytea_fdw
WHERE fid = 4;
DEBUG: OGR SQL: (fid = 4)
fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
-----+------+------+-----+------+-------+-----+----+----+------+-------+----
(0 rows)
SELECT fid, name, dt
FROM bytea_fdw
WHERE name IS NULL;
DEBUG: OGR SQL: (name IS NULL)
fid | name | dt
-----+------+----
3 | |
(1 row)
SELECT fid, name
FROM bytea_fdw
WHERE name = 'Jim' AND age <= 30;
DEBUG: OGR SQL: (age <= 30) AND (name = 'Jim')
fid | name
-----+------
1 | Jim
(1 row)
SELECT fid, name, dt
FROM bytea_fdw
WHERE name = 'Jim' AND age <= 30 AND dt > '2010-10-1'::date;
DEBUG: OGR SQL: (age <= 30) AND (dt > '10-01-2010') AND (name = 'Jim')
fid | name | dt
-----+------+------------
1 | Jim | 10-10-2010
(1 row)
SELECT fid, name
FROM bytea_fdw
WHERE name = 'Jim' OR name IS NULL;
DEBUG: OGR SQL: ((name = 'Jim') OR (name IS NULL))
fid | name
-----+------
1 | Jim
3 |
(2 rows)
----------------------------------------------------------------------
-- Cached query case, exercised by statement handles or
-- functions.
CREATE OR REPLACE FUNCTION get_names()
RETURNS varchar AS
$$
BEGIN
RETURN (SELECT string_agg(name,',')
FROM bytea_fdw
WHERE name = 'Jim' OR name IS NULL);
END;
$$
LANGUAGE 'plpgsql';
SELECT get_names();
DEBUG: OGR SQL: ((name = 'Jim') OR (name IS NULL))
get_names
-----------
Jim
(1 row)
DROP FUNCTION get_names();
----------------------------------------------------------------------
-- Remote Update
INSERT INTO bytea_fdw (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
VALUES ('Margaret', '2222'::bytea, 12, 5, 1.4, 19.13, '2001-11-23'::date, '9:12:34'::time, '2001-02-11 09:23:11'::timestamp, 'them', 'y' )
RETURNING fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn;
fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
-----+----------+------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
4 | Margaret | \x32323232 | 12 | 5 | 1.4 | 19.13 | 11-23-2001 | 09:12:34 | Sun Feb 11 09:23:11 2001 | them | y
(1 row)
UPDATE bytea_fdw
SET name = 'Maggie', num = 45.34, yn = 'n'
WHERE age = 12;
DEBUG: OGR SQL: (age = 12)
SELECT fid, name, num, yn
FROM bytea_fdw
WHERE fid = 4;
DEBUG: OGR SQL: (fid = 4)
fid | name | num | yn
-----+--------+-------+----
4 | Maggie | 45.34 | n
(1 row)
UPDATE bytea_fdw
SET dt = '2089-12-13', tm = '01:23:45'
WHERE num = 45.34;
DEBUG: OGR SQL: (num = 45.34)
SELECT fid, dt, tm
FROM bytea_fdw
WHERE fid = 4;
DEBUG: OGR SQL: (fid = 4)
fid | dt | tm
-----+------------+----------
4 | 12-13-2089 | 01:23:45
(1 row)
DELETE FROM bytea_fdw
WHERE fid = 4;
DEBUG: OGR SQL: (fid = 4)
SELECT a.fid, a.name, b.name
FROM bytea_local a
JOIN bytea_fdw b
USING (fid);
fid | name | name
-----+--------+--------
1 | Jim | Jim
2 | Marvin | Marvin
3 | |
(3 rows)
----------------------------------------------------------------------
-- Populate local array table
SET client_min_messages = NOTICE;
CREATE TABLE array_local (
fid integer primary key,
geom bytea,
txt text[],
int int2[],
flt float4[],
b boolean[]
);
INSERT INTO array_local (fid,txt, int, flt, b) VALUES
(1, ARRAY['Jim'], ARRAY[1,2,3], ARRAY[3.4,5.6,7.8], ARRAY[true,false]),
(2, ARRAY['Jim',NULL,'Joe'], ARRAY[1,3,NULL,4], ARRAY[4.5,NULL,3.4], ARRAY[false,NULL]),
(3, NULL, NULL, NULL, NULL);
----------------------------------------------------------------------
-- Create remote array table
CREATE FOREIGN TABLE array_fdw (
fid bigint,
geom bytea,
txt text[],
int int4[],
flt float8[],
b boolean[]
) SERVER pgserver OPTIONS (layer 'array_local');
SELECT fid, txt, int, flt, b FROM array_fdw;
fid | txt | int | flt | b
-----+--------------+-----------+---------------+-------
1 | {Jim} | {1,2,3} | {3.4,5.6,7.8} | {t,f}
2 | {Jim,"",Joe} | {1,3,0,4} | {4.5,0,3.4} | {f,f}
3 | | | |
(3 rows)
----------------------------------------------------------------------
-- Update remote array table
UPDATE array_fdw SET
txt = ARRAY['newJim', 'newJoe'],
int = ARRAY[-2, -1, 0, 1, 2],
flt = ARRAY[-0.1, 0.0, 0.1]
WHERE fid = 3;
SELECT txt, int, flt FROM array_fdw WHERE fid = 3;
txt | int | flt
-----------------+---------------+--------------
{newJim,newJoe} | {-2,-1,0,1,2} | {-0.1,0,0.1}
(1 row)
|
