SET client_min_messages TO DEBUG1;

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 dnb_zdb_oai_dc (
  id text OPTIONS (oai_node 'identifier'),
  content text OPTIONS (oai_node 'content'),
  setspec text[] OPTIONS (oai_node 'setspec'),
  datestamp timestamp OPTIONS (oai_node 'datestamp'),
  meta text OPTIONS (oai_node 'metadataprefix')
 )
SERVER oai_server_dnb OPTIONS (setspec 'zdb', 
                               metadataPrefix 'oai_dc');

-- records between '2021-01-03' and '2021-01-04'
SELECT ROW_NUMBER() OVER (), id, content::xml IS DOCUMENT AS content, setspec, datestamp, meta
FROM dnb_zdb_oai_dc
WHERE
  datestamp BETWEEN '2021-01-03' AND '2021-01-04';

-- Records between '2022-02-01' and '2022-02-02'.
-- Override 'setspec' option -> dnb:reiheC.
-- Override 'metadataPrefix' -> MARC21/XML.
SELECT ROW_NUMBER() OVER (), id, content::xml IS DOCUMENT AS content, setspec, datestamp, meta
FROM dnb_zdb_oai_dc
WHERE
  datestamp BETWEEN '2022-02-01' AND '2022-02-02' AND
  meta = 'MARC21-xml' AND
  setspec <@ ARRAY['dnb:reiheC'];

-- Counting (computed locally!) between '2021-01-01' and '2021-01-31.
-- Override 'setspec' option -> dnb:reiheC.
SELECT count(*) FILTER (WHERE content::xml IS DOCUMENT)
FROM dnb_zdb_oai_dc
WHERE
  datestamp BETWEEN '2021-01-01' AND '2021-01-05' AND
  setspec <@ ARRAY['dnb:reiheC'];
  
-- GetRecord request
SELECT * FROM dnb_zdb_oai_dc
WHERE id = 'oai:dnb.de/zdb/1250800153';

-- ListIdentifiers request.
-- An OAI foreign table without a content column will be queried
-- with ListIndentifier requests.
CREATE FOREIGN TABLE dnb_zdb_oai_dc_nocontent (
  id text OPTIONS (oai_node 'identifier'),
  setspec text[] OPTIONS (oai_node 'setspec'),
  datestamp timestamp OPTIONS (oai_node 'datestamp'),
  meta text OPTIONS (oai_node 'metadataprefix')
 )                                             
SERVER oai_server_dnb OPTIONS (setspec 'zdb', 
                               metadataPrefix 'oai_dc');

-- Identifiers between '2021-01-01' and '2021-01-03'.
SELECT * 
FROM dnb_zdb_oai_dc_nocontent
WHERE datestamp BETWEEN '2021-01-03' AND '2021-01-04';

SELECT o.*
FROM (VALUES ('zdb'), ('oai_dc')) AS v(fmt)
JOIN dnb_zdb_oai_dc_nocontent o ON o.setSpec <@ ARRAY[v.fmt]
WHERE datestamp BETWEEN '2021-01-03' AND '2021-01-04';

SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_material = off;

SELECT v.s,
       (SELECT count(*)
        FROM dnb_zdb_oai_dc_nocontent o
        WHERE o.datestamp BETWEEN '2021-01-03' AND '2021-01-04'
          AND o.setspec <@ ARRAY[v.s])
FROM (VALUES ('zdb'), ('zdb'), ('zdb')) AS v(s);

RESET enable_hashjoin;
RESET enable_mergejoin;
RESET enable_material;

-- Values only known at execution time (parameters, now(), ...) are
-- pushed down as well.
SELECT (SELECT count(*)
        FROM dnb_zdb_oai_dc_nocontent o
        WHERE o.datestamp BETWEEN v.f AND v.u)
FROM (VALUES ('2021-01-03'::timestamp, '2021-01-04'::timestamp)) AS v(f, u);

DROP SERVER oai_server_dnb CASCADE;