1----------------------------------------------------------------------
2-- Create local table
3CREATE TABLE bytea_local (
4  fid serial primary key,
5  geom bytea,
6  name varchar,
7  age bigint,
8  size integer,
9  value float8,
10  num numeric(6,2),
11  dt date,
12  tm time,
13  dttm timestamp,
14  varch char(8),
15  yn char
16);
17----------------------------------------------------------------------
18-- Populate local table
19INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
20  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' );
21INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
22  VALUES ('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' );
23INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
24  VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);
25----------------------------------------------------------------------
26-- Create remote table
27CREATE SERVER pgserver
28  FOREIGN DATA WRAPPER ogr_fdw
29  OPTIONS (
30    datasource 'PG:dbname=contrib_regression host=localhost',
31    format 'PostgreSQL' );
32CREATE FOREIGN TABLE bytea_fdw (
33  fid bigint,
34  geom bytea,
35  name varchar,
36  age bigint,
37  size integer,
38  value float8,
39  num numeric(6,2),
40  dt date,
41  tm time,
42  dttm timestamp,
43  varch char(8),
44  yn char
45) SERVER pgserver OPTIONS (layer 'bytea_local');
46SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn FROM bytea_fdw;
47 fid |  name  |     geom     | age | size | value |  num  |     dt     |    tm    |           dttm           |  varch   | yn
48-----+--------+--------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
49   1 | Jim    | \x3134323332 |  23 |    1 |   4.3 |  5.50 | 10-10-2010 | 13:23:21 | Sun Oct 10 13:23:21 2010 | this     | y
50   2 | Marvin | \x3535353535 |  34 |    2 |   5.4 | 10.13 | 11-11-2011 | 15:21:45 | Fri Nov 11 15:21:45 2011 | that     | n
51   3 |        |              |     |      |       |       |            |          |                          |          |
52(3 rows)
53
54SELECT a.name, b.name
55  FROM bytea_local a
56  JOIN bytea_fdw b
57  USING (fid);
58  name  |  name
59--------+--------
60 Jim    | Jim
61 Marvin | Marvin
62        |
63(3 rows)
64
65EXPLAIN VERBOSE
66  SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
67  FROM bytea_fdw;
68                                 QUERY PLAN
69-----------------------------------------------------------------------------
70 Foreign Scan on public.bytea_fdw  (cost=25.00..1025.00 rows=1000 width=170)
71   Output: fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
72(2 rows)
73
74----------------------------------------------------------------------
75-- Remote Query and OGR SQL pushdown
76SET client_min_messages = DEBUG1;
77SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
78  FROM bytea_fdw
79  WHERE fid = 4;
80DEBUG:  OGR SQL: (fid = 4)
81 fid | name | geom | age | size | value | num | dt | tm | dttm | varch | yn
82-----+------+------+-----+------+-------+-----+----+----+------+-------+----
83(0 rows)
84
85SELECT fid, name, dt
86  FROM bytea_fdw
87  WHERE name IS NULL;
88DEBUG:  OGR SQL: (name IS NULL)
89 fid | name | dt
90-----+------+----
91   3 |      |
92(1 row)
93
94SELECT fid, name
95  FROM bytea_fdw
96  WHERE name = 'Jim' AND age <= 30;
97DEBUG:  OGR SQL: (age <= 30) AND (name = 'Jim')
98 fid | name
99-----+------
100   1 | Jim
101(1 row)
102
103SELECT fid, name, dt
104  FROM bytea_fdw
105  WHERE name = 'Jim' AND age <= 30 AND dt > '2010-10-1'::date;
106DEBUG:  OGR SQL: (age <= 30) AND (dt > '10-01-2010') AND (name = 'Jim')
107 fid | name |     dt
108-----+------+------------
109   1 | Jim  | 10-10-2010
110(1 row)
111
112SELECT fid, name
113  FROM bytea_fdw
114  WHERE name = 'Jim' OR name IS NULL;
115DEBUG:  OGR SQL: ((name = 'Jim') OR (name IS NULL))
116 fid | name
117-----+------
118   1 | Jim
119   3 |
120(2 rows)
121
122----------------------------------------------------------------------
123-- Cached query case, exercised by statement handles or
124-- functions.
125CREATE OR REPLACE FUNCTION get_names()
126  RETURNS varchar AS
127  $$
128  BEGIN
129    RETURN (SELECT string_agg(name,',')
130    FROM bytea_fdw
131    WHERE name = 'Jim' OR name IS NULL);
132  END;
133  $$
134  LANGUAGE 'plpgsql';
135SELECT get_names();
136DEBUG:  OGR SQL: ((name = 'Jim') OR (name IS NULL))
137 get_names
138-----------
139 Jim
140(1 row)
141
142DROP FUNCTION get_names();
143----------------------------------------------------------------------
144-- Remote Update
145INSERT INTO bytea_fdw (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
146VALUES ('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' )
147RETURNING fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn;
148 fid |   name   |    geom    | age | size | value |  num  |     dt     |    tm    |           dttm           |  varch   | yn
149-----+----------+------------+-----+------+-------+-------+------------+----------+--------------------------+----------+----
150   4 | Margaret | \x32323232 |  12 |    5 |   1.4 | 19.13 | 11-23-2001 | 09:12:34 | Sun Feb 11 09:23:11 2001 | them     | y
151(1 row)
152
153UPDATE bytea_fdw
154  SET name = 'Maggie', num = 45.34, yn = 'n'
155  WHERE age = 12;
156DEBUG:  OGR SQL: (age = 12)
157SELECT fid, name, num, yn
158  FROM bytea_fdw
159  WHERE fid = 4;
160DEBUG:  OGR SQL: (fid = 4)
161 fid |  name  |  num  | yn
162-----+--------+-------+----
163   4 | Maggie | 45.34 | n
164(1 row)
165
166UPDATE bytea_fdw
167  SET dt = '2089-12-13', tm = '01:23:45'
168  WHERE num = 45.34;
169DEBUG:  OGR SQL: (num = 45.34)
170SELECT fid, dt, tm
171  FROM bytea_fdw
172  WHERE fid = 4;
173DEBUG:  OGR SQL: (fid = 4)
174 fid |     dt     |    tm
175-----+------------+----------
176   4 | 12-13-2089 | 01:23:45
177(1 row)
178
179DELETE FROM bytea_fdw
180  WHERE fid = 4;
181DEBUG:  OGR SQL: (fid = 4)
182
183SELECT a.fid, a.name, b.name
184  FROM bytea_local a
185  JOIN bytea_fdw b
186  USING (fid);
187 fid |  name  |  name
188-----+--------+--------
189   1 | Jim    | Jim
190   2 | Marvin | Marvin
191   3 |        |
192(3 rows)
193
194
195