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'; row_number | id | content | setspec | datestamp | meta ------------+---------------------------+---------+---------+--------------------------+-------- 1 | oai:dnb.de/zdb/01950022X | t | {zdb} | Sun Jan 03 16:38:59 2021 | oai_dc 2 | oai:dnb.de/zdb/1224394720 | t | {zdb} | Sun Jan 03 16:34:54 2021 | oai_dc 3 | oai:dnb.de/zdb/1224398580 | t | {zdb} | Sun Jan 03 17:42:56 2021 | oai_dc (3 rows) -- 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']; row_number | id | content | setspec | datestamp | meta ------------+----------------------------------+---------+--------------+--------------------------+------------ 1 | oai:dnb.de/dnb:reiheC/116804152X | t | {dnb:reiheC} | Tue Feb 01 14:11:46 2022 | MARC21-xml 2 | oai:dnb.de/dnb:reiheC/969901429 | t | {dnb:reiheC} | Tue Feb 01 14:53:41 2022 | MARC21-xml 3 | oai:dnb.de/dnb:reiheC/99107503X | t | {dnb:reiheC} | Tue Feb 01 09:16:00 2022 | MARC21-xml 4 | oai:dnb.de/dnb:reiheC/965379930 | t | {dnb:reiheC} | Tue Feb 01 14:54:44 2022 | MARC21-xml 5 | oai:dnb.de/dnb:reiheC/1078457522 | t | {dnb:reiheC} | Tue Feb 01 12:56:14 2022 | MARC21-xml 6 | oai:dnb.de/dnb:reiheC/1078264724 | t | {dnb:reiheC} | Tue Feb 01 14:46:08 2022 | MARC21-xml 7 | oai:dnb.de/dnb:reiheC/1053818726 | t | {dnb:reiheC} | Tue Feb 01 14:47:25 2022 | MARC21-xml 8 | oai:dnb.de/dnb:reiheC/1222519003 | t | {dnb:reiheC} | Tue Feb 01 21:05:47 2022 | MARC21-xml 9 | oai:dnb.de/dnb:reiheC/122251897X | t | {dnb:reiheC} | Tue Feb 01 20:56:15 2022 | MARC21-xml 10 | oai:dnb.de/dnb:reiheC/1222518953 | t | {dnb:reiheC} | Tue Feb 01 20:54:12 2022 | MARC21-xml 11 | oai:dnb.de/dnb:reiheC/1222518910 | t | {dnb:reiheC} | Tue Feb 01 20:48:33 2022 | MARC21-xml 12 | oai:dnb.de/dnb:reiheC/1222518813 | t | {dnb:reiheC} | Tue Feb 01 09:00:00 2022 | MARC21-xml 13 | oai:dnb.de/dnb:reiheC/1222272652 | t | {dnb:reiheC} | Tue Feb 01 09:04:14 2022 | MARC21-xml 14 | oai:dnb.de/dnb:reiheC/1222272210 | t | {dnb:reiheC} | Tue Feb 01 09:13:12 2022 | MARC21-xml 15 | oai:dnb.de/dnb:reiheC/1221197002 | t | {dnb:reiheC} | Tue Feb 01 08:56:46 2022 | MARC21-xml 16 | oai:dnb.de/dnb:reiheC/1221196928 | t | {dnb:reiheC} | Tue Feb 01 08:52:24 2022 | MARC21-xml 17 | oai:dnb.de/dnb:reiheC/1221196820 | t | {dnb:reiheC} | Tue Feb 01 08:39:54 2022 | MARC21-xml 18 | oai:dnb.de/dnb:reiheC/1222518937 | t | {dnb:reiheC} | Tue Feb 01 20:53:22 2022 | MARC21-xml (18 rows) -- 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']; count ------- 7 (1 row) -- GetRecord request SELECT * FROM dnb_zdb_oai_dc WHERE id = 'oai:dnb.de/zdb/1250800153'; id | content | setspec | datestamp | meta ---------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------+--------------------------+-------- oai:dnb.de/zdb/1250800153 | +| {zdb} | Tue Feb 01 15:39:05 2022 | oai_dc | Jahrbuch Deutsch als Fremdsprache +| | | | München : Iudicium Verlag +| | | | 2022 +| | | | ger +| | | | 1250800153 +| | | | 3108310-9 +| | | | Online-Ressource +| | | | http://d-nb.info/011071060 +| | | | | | | (1 row) -- 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'; id | setspec | datestamp | meta ---------------------------+---------+--------------------------+-------- oai:dnb.de/zdb/01950022X | {zdb} | Sun Jan 03 16:38:59 2021 | oai_dc oai:dnb.de/zdb/1224394720 | {zdb} | Sun Jan 03 16:34:54 2021 | oai_dc oai:dnb.de/zdb/1224398580 | {zdb} | Sun Jan 03 17:42:56 2021 | oai_dc (3 rows) CREATE FOREIGN TABLE davidrumsey_oai_dc ( id text OPTIONS (oai_node 'identifier'), content xml 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_davidrumsey OPTIONS (metadataprefix 'oai_dc'); -- SELECT using timestamps hh24:mm:ss hh24:mi:ss. SELECT ROW_NUMBER() OVER (), id, content IS DOCUMENT AS content, setspec, datestamp, meta FROM davidrumsey_oai_dc WHERE datestamp BETWEEN '2022-07-04 03:23:24' AND '2022-07-04 03:23:25'; row_number | id | content | setspec | datestamp | meta ------------+------------------------------------+---------+--------------+--------------------------+-------- 1 | oai:N/A:RUMSEY~8~1~343557~90111455 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 2 | oai:N/A:RUMSEY~8~1~343558~90111458 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 3 | oai:N/A:RUMSEY~8~1~343559~90111459 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 4 | oai:N/A:RUMSEY~8~1~343560~90111464 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 5 | oai:N/A:RUMSEY~8~1~343561~90111465 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 6 | oai:N/A:RUMSEY~8~1~343562~90111462 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 7 | oai:N/A:RUMSEY~8~1~343563~90111463 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 8 | oai:N/A:RUMSEY~8~1~343564~90111466 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 9 | oai:N/A:RUMSEY~8~1~343565~90111467 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 10 | oai:N/A:RUMSEY~8~1~343566~90111468 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 11 | oai:N/A:RUMSEY~8~1~343567~90111469 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc 12 | oai:N/A:RUMSEY~8~1~343568~90111470 | t | {RUMSEY~8~1} | Mon Jul 04 03:23:24 2022 | oai_dc (12 rows)