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 * 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;
------------------------------------------------
-- 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)

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