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