CREATE SERVER oai_server_dnb FOREIGN DATA WRAPPER oai_fdw
OPTIONS (url 'https://services.dnb.de/oai/repository',
         request_redirect 'true',
         request_max_redirect '1');
CREATE FOREIGN TABLE IF NOT EXISTS dnb_oai_dc (
  id text                OPTIONS (oai_node 'identifier'), 
  xmldoc xml             OPTIONS (oai_node 'content'), 
  sets text[]            OPTIONS (oai_node 'setspec'), 
  updatedate timestamp   OPTIONS (oai_node 'datestamp'),
  format text            OPTIONS (oai_node 'metadataprefix')
 ) SERVER oai_server_dnb OPTIONS (metadataprefix 'oai_dc');
/* Target table without schema */
CALL OAI_HarvestTable('dnb_oai_dc','clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
NOTICE:  target table "public.clone_dnb_oai_dc" created
INFO:  page stored into "public.clone_dnb_oai_dc": 213 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "public.clone_dnb_oai_dc": 896 records inserted and 0 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "public.clone_dnb_oai_dc"): 1109 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM clone_dnb_oai_dc;
 count 
-------
  1109
(1 row)

-- Existing table will be upserted 
CALL OAI_HarvestTable('dnb_oai_dc','clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
INFO:  page stored into "public.clone_dnb_oai_dc": 0 records inserted and 213 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "public.clone_dnb_oai_dc": 0 records inserted and 896 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "public.clone_dnb_oai_dc"): 0 records inserted and 1109 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM clone_dnb_oai_dc;
 count 
-------
  1109
(1 row)

CREATE SCHEMA oai_schema;
/* Target table with specific schema */
CALL OAI_HarvestTable('dnb_oai_dc','oai_schema.clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
NOTICE:  target table "oai_schema.clone_dnb_oai_dc" created
INFO:  page stored into "oai_schema.clone_dnb_oai_dc": 213 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "oai_schema.clone_dnb_oai_dc": 896 records inserted and 0 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "oai_schema.clone_dnb_oai_dc"): 1109 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM oai_schema.clone_dnb_oai_dc;
 count 
-------
  1109
(1 row)

/* Target table with specific schema (update records) */
CALL OAI_HarvestTable('dnb_oai_dc','oai_schema.clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
INFO:  page stored into "oai_schema.clone_dnb_oai_dc": 0 records inserted and 213 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "oai_schema.clone_dnb_oai_dc": 0 records inserted and 896 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "oai_schema.clone_dnb_oai_dc"): 0 records inserted and 1109 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM oai_schema.clone_dnb_oai_dc;
 count 
-------
  1109
(1 row)

-- Existing table will be upserted 
CALL OAI_HarvestTable('dnb_oai_dc','oai_schema.clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
INFO:  page stored into "oai_schema.clone_dnb_oai_dc": 0 records inserted and 213 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "oai_schema.clone_dnb_oai_dc": 0 records inserted and 896 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "oai_schema.clone_dnb_oai_dc"): 0 records inserted and 1109 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM oai_schema.clone_dnb_oai_dc;
 count 
-------
  1109
(1 row)

/* Target table containing special characters */
CALL OAI_HarvestTable('dnb_oai_dc','oai_schema."clone-dnb:oai/dc"', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
NOTICE:  target table "oai_schema."clone-dnb:oai/dc"" created
INFO:  page stored into "oai_schema."clone-dnb:oai/dc"": 213 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "oai_schema."clone-dnb:oai/dc"": 896 records inserted and 0 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "oai_schema."clone-dnb:oai/dc""): 1109 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM oai_schema."clone-dnb:oai/dc";
 count 
-------
  1109
(1 row)

/* Target table containing special characters without schema */
CALL OAI_HarvestTable('dnb_oai_dc','"clone-dnb:oai/dc"', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
NOTICE:  target table "public."clone-dnb:oai/dc"" created
INFO:  page stored into "public."clone-dnb:oai/dc"": 213 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "public."clone-dnb:oai/dc"": 896 records inserted and 0 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.dnb_oai_dc" -> "public."clone-dnb:oai/dc""): 1109 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
SELECT count(*) FROM "clone-dnb:oai/dc";
 count 
-------
  1109
(1 row)

/* WARNING: Foreign table without identifier */
CREATE FOREIGN TABLE IF NOT EXISTS table_without_identifier (
  xmldoc xml             OPTIONS (oai_node 'content'), 
  sets text[]            OPTIONS (oai_node 'setspec'), 
  updatedate timestamp   OPTIONS (oai_node 'datestamp'),
  format text            OPTIONS (oai_node 'metadataprefix')
 ) SERVER oai_server_dnb OPTIONS (metadataprefix 'oai_dc');
 
 CALL OAI_HarvestTable('table_without_identifier','clone_table_without_identifier', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true); 
WARNING:  foreign table "public.table_without_identifier" has no identifier column. It is strongly recommended to map the OAI identifier to a column, as it can ensure that records are not duplicated
NOTICE:  target table "public.clone_table_without_identifier" created
INFO:  page stored into "public.clone_table_without_identifier": 213 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-02 00:00:00]
INFO:  page stored into "public.clone_table_without_identifier": 896 records inserted and 0 updated [2020-01-02 00:00:00 - 2020-01-03 00:00:00]
NOTICE:  OAI harvester complete ("public.table_without_identifier" -> "public.clone_table_without_identifier"): 1109 records inserted and 0 updated [2020-01-01 00:00:00 - 2020-01-03 00:00:00]
 /* EXCEPTION: oai_table does not exist */
CALL OAI_HarvestTable('foo','clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true);
ERROR:  foreign table "public.foo" does not exist
CONTEXT:  PL/pgSQL function oai_harvesttable(text,text,interval,timestamp without time zone,timestamp without time zone,boolean,boolean) line 68 at RAISE
/* EXCEPTION: end date smaller than start date */
CALL OAI_HarvestTable('dnb_oai_dc','clone_dnb_oai_dc', interval '1 day', '2020-01-01 00:00:00', '2019-12-31 00:00:00',true,true);
ERROR:  invalid time window. The end date [Wed Jan 01 00:00:00 2020] lies before the start date [Tue Dec 31 00:00:00 2019]
CONTEXT:  PL/pgSQL function oai_harvesttable(text,text,interval,timestamp without time zone,timestamp without time zone,boolean,boolean) line 37 at RAISE
/* EXCEPTION: Foreign table without datestamp */
CREATE FOREIGN TABLE IF NOT EXISTS table_without_datestamp (
  id text                OPTIONS (oai_node 'identifier'), 
  xmldoc xml             OPTIONS (oai_node 'content'), 
  sets text[]            OPTIONS (oai_node 'setspec'), 
  format text            OPTIONS (oai_node 'metadataprefix')
 ) SERVER oai_server_dnb OPTIONS (metadataprefix 'oai_dc');
CALL OAI_HarvestTable('table_without_datestamp', 'clone_table_without_datestamp', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true); 
ERROR:  foreign table "public.table_without_datestamp" has no datestamp column
CONTEXT:  PL/pgSQL function oai_harvesttable(text,text,interval,timestamp without time zone,timestamp without time zone,boolean,boolean) line 76 at RAISE
/* EXCEPTION: Foreign table without datestamp and identifier*/
CREATE FOREIGN TABLE IF NOT EXISTS table_without_datestamp_identifier (
  xmldoc xml             OPTIONS (oai_node 'content'), 
  sets text[]            OPTIONS (oai_node 'setspec'), 
  format text            OPTIONS (oai_node 'metadataprefix')
 ) SERVER oai_server_dnb OPTIONS (metadataprefix 'oai_dc');
CALL OAI_HarvestTable('table_without_datestamp_identifier', 'clone_table_without_datestamp_identifier', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true); 
ERROR:  foreign table "public.table_without_datestamp_identifier" has no datestamp column
CONTEXT:  PL/pgSQL function oai_harvesttable(text,text,interval,timestamp without time zone,timestamp without time zone,boolean,boolean) line 76 at RAISE
/* EXCEPTION: Foreign table without any oai_node */
CREATE FOREIGN TABLE IF NOT EXISTS table_without_oai_node (
  xmldoc xml, 
  sets text[], 
  format text
 ) SERVER oai_server_dnb OPTIONS (metadataprefix 'oai_dc');
CALL OAI_HarvestTable('table_without_oai_node', 'clone_table_without_oai_node', interval '1 day', '2020-01-01 00:00:00', '2020-01-03 00:00:00',true,true); 
ERROR:  foreign table "public.table_without_oai_node" has no datestamp column
CONTEXT:  PL/pgSQL function oai_harvesttable(text,text,interval,timestamp without time zone,timestamp without time zone,boolean,boolean) line 76 at RAISE
DROP SERVER IF EXISTS oai_server_dnb CASCADE;
NOTICE:  drop cascades to 5 other objects
DETAIL:  drop cascades to foreign table dnb_oai_dc
drop cascades to foreign table table_without_identifier
drop cascades to foreign table table_without_datestamp
drop cascades to foreign table table_without_datestamp_identifier
drop cascades to foreign table table_without_oai_node