CREATE SERVER pgserver
  FOREIGN DATA WRAPPER ogr_fdw
  OPTIONS (
    datasource 'PG:dbname=contrib_regression host=localhost',
    format 'PostgreSQL' );
	
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
);

----------------------------------------------------------------------

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' );
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
  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' );
INSERT INTO bytea_local (name, geom, age, size, value, num, dt, tm, dttm, varch, yn)
  VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL);

----------------------------------------------------------------------

CREATE FOREIGN TABLE bytea_fdw (
  fid integer,
  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;

SELECT a.name, b.name 
  FROM bytea_local a 
  JOIN bytea_fdw b 
  USING (fid);

EXPLAIN VERBOSE 
  SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
  FROM bytea_fdw;

----------------------------------------------------------------------

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;

SELECT fid, name, geom, age, size, value, num, dt, tm, dttm, varch, yn
  FROM bytea_fdw
  WHERE fid = 4;

UPDATE bytea_fdw 
  SET name = 'Maggie', num = 45.34, yn = 'n'
  WHERE age = 12;

SELECT fid, name, num, yn
  FROM bytea_fdw
  WHERE fid = 4;

UPDATE bytea_fdw 
  SET dt = '2089-12-13', tm = '01:23:45'
  WHERE num = 45.34;

SELECT fid, dt, tm
  FROM bytea_fdw
  WHERE fid = 4;

DELETE FROM bytea_fdw 
  WHERE fid = 4;
  
SELECT a.fid, a.name, b.name 
  FROM bytea_local a 
  JOIN bytea_fdw b 
  USING (fid);