CREATE SERVER dbpedia FOREIGN DATA WRAPPER rdf_fdw OPTIONS (endpoint 'https://dbpedia.org/sparql', fetch_size '5'); CREATE FOREIGN TABLE public.dbpedia_cities ( uri text OPTIONS (variable '?city', nodetype 'iri'), city_name text OPTIONS (variable '?name', nodetype 'literal', literaltype 'xsd:string'), elevation numeric OPTIONS (variable '?elevation', nodetype 'literal', literaltype 'xsd:integer') ) SERVER dbpedia OPTIONS ( sparql ' PREFIX dbo: PREFIX foaf: PREFIX dbr: SELECT * { ?city a dbo:City ; foaf:name ?name ; dbo:federalState dbr:North_Rhine-Westphalia ; dbo:elevation ?elevation } ORDER BY ?name OFFSET 7300 LIMIT 4200 '); /* * 't1' only partially matches with 'dbpedia_cities', with columns * 'city_name' and 'uri'. * SERVER option 'fetch_size' will be used, as both FOREIGN TABLE and * function call do not set 'fetch_size'. * 'commit_page' is set to 'false', so all retrieved and inserted records * are committed only when the transaction finishes. */ CREATE TABLE public.t1(id serial, city_name text, c1_null text, uri text, c2_null text); CALL rdf_fdw_clone_table( foreign_table => 'public.dbpedia_cities', target_table => 'public.t1', verbose => true, commit_page => false ); INFO: == Parameters == foreign_table: 'dbpedia_cities' target_table: 'public.t1' create_table: 'false' fetch_size: 5 begin_offset: 0 max_records: 0 ordering_column: 'NOT SET' ordering sparql variable: '?city' sort_order: 'ASC' INFO: [0 - 5]: 5 records inserted INFO: [5 - 10]: 5 records inserted INFO: [10 - 15]: 3 records inserted SELECT * FROM public.t1; id | city_name | c1_null | uri | c2_null ----+---------------------+---------+-----------------------------------------------------------+--------- 1 | Aachen | | http://dbpedia.org/resource/Aachen | 2 | Bielefeld | | http://dbpedia.org/resource/Bielefeld | 3 | Dortmund | | http://dbpedia.org/resource/Dortmund | 4 | Düsseldorf | | http://dbpedia.org/resource/Düsseldorf | 5 | Gelsenkirchen | | http://dbpedia.org/resource/Gelsenkirchen | 6 | Hagen | | http://dbpedia.org/resource/Hagen | 7 | Hamm | | http://dbpedia.org/resource/Hamm | 8 | Herne | | http://dbpedia.org/resource/Herne,_North_Rhine-Westphalia | 9 | Krefeld | | http://dbpedia.org/resource/Krefeld | 10 | Mönchengladbach | | http://dbpedia.org/resource/Mönchengladbach | 11 | Mülheim an der Ruhr | | http://dbpedia.org/resource/Mülheim | 12 | Münster | | http://dbpedia.org/resource/Münster | 13 | Remscheid | | http://dbpedia.org/resource/Remscheid | (13 rows) /* * only a single column of 't2' matches the foreign table 'dbpedia_cities'. * reducing the 'fetch_size' to 2 and setting maximum limit of 9 records. * the SPARQL query will be ordered by 'city_name' */ CREATE TABLE public.t2(id serial, foo int, bar date, city_name text); CALL rdf_fdw_clone_table( foreign_table => 'public.dbpedia_cities', target_table => 'public.t2', fetch_size => 2, max_records => 9, orderby_column => 'city_name', verbose => true, commit_page => true ); INFO: == Parameters == foreign_table: 'dbpedia_cities' target_table: 'public.t2' create_table: 'false' fetch_size: 2 begin_offset: 0 max_records: 9 ordering_column: 'city_name' ordering sparql variable: '?name' sort_order: 'ASC' INFO: [0 - 2]: 2 records inserted INFO: [2 - 4]: 2 records inserted INFO: [4 - 6]: 2 records inserted INFO: [6 - 8]: 2 records inserted INFO: [8 - 10]: 1 records inserted SELECT * FROM public.t2; id | foo | bar | city_name ----+-----+-----+--------------- 1 | | | Aachen 2 | | | Bielefeld 3 | | | Dortmund 4 | | | Düsseldorf 5 | | | Gelsenkirchen 6 | | | Hagen 7 | | | Hamm 8 | | | Herne 9 | | | Krefeld (9 rows) /* * 't3' does not exist. it will be created by the function due to * 'create_table => true' as a copy of 'dbedia_cities' */ CALL rdf_fdw_clone_table( foreign_table => 'public.dbpedia_cities', target_table => 'public.t3', create_table => true, orderby_column => 'elevation', sort_order => 'DESC', verbose => true ); INFO: Target TABLE "public.t3" created based on FOREIGN TABLE "public.dbpedia_cities": CREATE TABLE public.t3 AS SELECT * FROM public.dbpedia_cities WITH NO DATA; INFO: == Parameters == foreign_table: 'dbpedia_cities' target_table: 'public.t3' create_table: 'true' fetch_size: 5 begin_offset: 0 max_records: 0 ordering_column: 'elevation' ordering sparql variable: '?elevation' sort_order: 'DESC' INFO: [0 - 5]: 5 records inserted INFO: [5 - 10]: 5 records inserted INFO: [10 - 15]: 3 records inserted SELECT * FROM public.t3; uri | city_name | elevation -----------------------------------------------------------+---------------------+----------- http://dbpedia.org/resource/Remscheid | Remscheid | 365.0 http://dbpedia.org/resource/Aachen | Aachen | 173.0 http://dbpedia.org/resource/Bielefeld | Bielefeld | 118.0 http://dbpedia.org/resource/Hagen | Hagen | 106.0 http://dbpedia.org/resource/Dortmund | Dortmund | 86.0 http://dbpedia.org/resource/Mönchengladbach | Mönchengladbach | 70.0 http://dbpedia.org/resource/Herne,_North_Rhine-Westphalia | Herne | 65.0 http://dbpedia.org/resource/Gelsenkirchen | Gelsenkirchen | 60.0 http://dbpedia.org/resource/Münster | Münster | 60.0 http://dbpedia.org/resource/Krefeld | Krefeld | 39.0 http://dbpedia.org/resource/Düsseldorf | Düsseldorf | 38.0 http://dbpedia.org/resource/Hamm | Hamm | 37.7 http://dbpedia.org/resource/Mülheim | Mülheim an der Ruhr | 26.0 (13 rows) /*----------------------------------------------------------------------------------------------------------*/ CREATE FOREIGN TABLE public.film ( film_id text OPTIONS (variable '?film'), name text OPTIONS (variable '?name', language 'en'), released date OPTIONS (variable '?released', literaltype 'xsd:date'), runtime int OPTIONS (variable '?runtime'), abstract text OPTIONS (variable '?abstract') ) SERVER dbpedia OPTIONS ( log_sparql 'false', sparql ' PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?film ?name ?released ?runtime ?abstract WHERE { ?film a dbo:Film ; rdfs:comment ?abstract ; dbp:name ?name ; dbp:released ?released ; dbp:runtime ?runtime . FILTER (LANG ( ?abstract ) = "en") FILTER (datatype(?released) = xsd:date) FILTER (datatype(?runtime) = xsd:integer) } OFFSET 7300 LIMIT 4200 '); /* * 'public.heap1' only partially matches the columns of 'public.film'. * the non-matching columns will be set to NULL. */ CREATE TABLE public.heap1 (id bigserial, foo text, runtime int, bar text, name varchar, released date); CALL rdf_fdw_clone_table( foreign_table => 'public.film', target_table => 'public.heap1', orderby_column => 'released', fetch_size => 4, max_records => 15 ); SELECT * FROM public.heap1; id | foo | runtime | bar | name | released ----+-----+---------+-----+-------------------------------+------------ 1 | | 2 | | Nervy Nat Kisses the Bride | 09-30-1904 2 | | 1 | | When Knights Were Bold | 05-20-1908 3 | | 1 | | Pippa Passes | 10-04-1909 4 | | 1 | | Briton and Boer | 10-25-1909 5 | | 12 | | Twelfth Night | 02-05-1910 6 | | 1 | | In the Season of Buds | 06-02-1910 7 | | 1 | | An Engineer's Sweetheart | 06-14-1910 8 | | 1 | | The Fire Chief's Daughter | 06-30-1910 9 | | 1 | | The Merry Wives of Windsor | 11-24-1910 10 | | 1 | | A Tin-Type Romance | 12-06-1910 11 | | 1 | | Baseball and Bloomers | 01-06-1911 12 | | 1 | | Jean Rescues | 01-31-1911 13 | | 1 | | Fisher Folks | 02-16-1911 14 | | 1 | | Artful Kate | 02-23-1911 15 | | 2500 | | A Tale of the Australian Bush | 03-15-1911 (15 rows) /* * 'public.heap2' does not exist. * it will be created, since 'create_table' is set to true. */ CALL rdf_fdw_clone_table( foreign_table => 'public.film', target_table => 'public.heap2', orderby_column => 'released', create_table => true, fetch_size => 4, max_records => 15 ); SELECT runtime,name,released FROM public.heap2; runtime | name | released ---------+-------------------------------+------------ 2 | Nervy Nat Kisses the Bride | 09-30-1904 1 | When Knights Were Bold | 05-20-1908 1 | Pippa Passes | 10-04-1909 1 | Briton and Boer | 10-25-1909 12 | Twelfth Night | 02-05-1910 1 | In the Season of Buds | 06-02-1910 1 | An Engineer's Sweetheart | 06-14-1910 1 | The Fire Chief's Daughter | 06-30-1910 1 | The Merry Wives of Windsor | 11-24-1910 1 | A Tin-Type Romance | 12-06-1910 1 | Baseball and Bloomers | 01-06-1911 1 | Jean Rescues | 01-31-1911 1 | Fisher Folks | 02-16-1911 1 | Artful Kate | 02-23-1911 2500 | A Tale of the Australian Bush | 03-15-1911 (15 rows) /* * the matching columns of 'public.heap1' and 'public.heap2' * must be identical */ SELECT runtime,name,released FROM public.heap1 EXCEPT SELECT runtime,name,released FROM public.heap2; runtime | name | released ---------+------+---------- (0 rows) /* * setting 'begin_offset' to 10 */ CALL rdf_fdw_clone_table( foreign_table => 'public.film', target_table => 'public.heap3', orderby_column => 'released', create_table => true, begin_offset => 10, fetch_size => 2, max_records => 7, verbose => true ); INFO: Target TABLE "public.heap3" created based on FOREIGN TABLE "public.film": CREATE TABLE public.heap3 AS SELECT * FROM public.film WITH NO DATA; INFO: == Parameters == foreign_table: 'film' target_table: 'public.heap3' create_table: 'true' fetch_size: 2 begin_offset: 10 max_records: 7 ordering_column: 'released' ordering sparql variable: '?released' sort_order: 'ASC' INFO: [10 - 12]: 2 records inserted INFO: [12 - 14]: 2 records inserted INFO: [14 - 16]: 2 records inserted INFO: [16 - 18]: 1 records inserted SELECT runtime,name,released FROM public.heap3; runtime | name | released ---------+-------------------------------+------------ 1 | Baseball and Bloomers | 01-06-1911 1 | Jean Rescues | 01-31-1911 1 | Fisher Folks | 02-16-1911 1 | Artful Kate | 02-23-1911 2500 | A Tale of the Australian Bush | 03-15-1911 1 | Monsieur | 04-04-1911 1 | How Spriggins Took Lodgers | 04-19-1911 (7 rows) /* * clean up the mess */ DROP TABLE IF EXISTS public.t1, public.t2, public.t3, public.heap1, public.heap2, public.heap3; DROP FOREIGN TABLE public.film, dbpedia_cities; DROP SERVER dbpedia; /* == Exceptions == */ CREATE TABLE public.t1_local(id serial, c1_null text, c2_null text); CREATE TABLE public.t2_local(name text, foo text); /* ordinary table instead of foreign table in 'foreign_table' */ CALL rdf_fdw_clone_table( foreign_table => 't1_local', target_table => 't2_local' ); ERROR: invalid relation: 't1_local' is not a foreign table /* foreign table instead of an ordinary table in 'target_table' */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1' ); ERROR: relation "t1" does not exist LINE 1: ...'f' THEN oid ELSE 0 END FROM pg_class WHERE oid = 't1'::regc... ^ QUERY: SELECT CASE relkind WHEN 'f' THEN oid ELSE 0 END FROM pg_class WHERE oid = 't1'::regclass::oid; /* empty target_table */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => '' ); ERROR: no 'target_table' provided /* empty foreign_table */ CALL rdf_fdw_clone_table( foreign_table => '', target_table => 't1_local' ); ERROR: no 'foreign_table' provided /* negative fetch_size */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', fetch_size => -1 ); ERROR: invalid 'fetch_size': -1 HINT: the page size corresponds to the number of records that are retrieved after each iteration and therefore must be a positive number /* negative begin_offset */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => -1 ); ERROR: invalid 'begin_offset': -1 /* invalid ordering_column */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't2_local', orderby_column => 'foo' ); ERROR: relation "t1" does not exist LINE 1: ...'f' THEN oid ELSE 0 END FROM pg_class WHERE oid = 't1'::regc... ^ QUERY: SELECT CASE relkind WHEN 'f' THEN oid ELSE 0 END FROM pg_class WHERE oid = 't1'::regclass::oid; /* target table does not match any column of t1 */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local' ); ERROR: relation "t1" does not exist LINE 1: ...'f' THEN oid ELSE 0 END FROM pg_class WHERE oid = 't1'::regc... ^ QUERY: SELECT CASE relkind WHEN 'f' THEN oid ELSE 0 END FROM pg_class WHERE oid = 't1'::regclass::oid; /* invalid sort_order */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', sort_order => 'foo' ); ERROR: invalid 'sort_order': foo HINT: the 'sort_order' must be either 'ASC' (ascending) or 'DESC' (descending) /* NULL foreign_table */ CALL rdf_fdw_clone_table( foreign_table => NULL, target_table => 't1_local'); ERROR: 'foreign_table' cannot be NULL /* NULL target_table */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => NULL); ERROR: 'target_table' cannot be NULL /* NULL begin_offset */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => NULL); ERROR: 'begin_offset' cannot be NULL HINT: either set it to 0 or ignore the paramter to start the pagination from the beginning /* NULL fetch_size */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => 42, fetch_size => NULL); ERROR: 'fetch_size' cannot be NULL /* NULL max_records */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => 42, fetch_size => 8, max_records => NULL); ERROR: 'max_records' cannot be NULL /* NULL sort_order */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => 42, fetch_size => 8, max_records => 103, orderby_column => 'foo', sort_order => NULL); ERROR: 'sort_order' cannot be NULL /* NULL create_table */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => 42, fetch_size => 8, max_records => 103, orderby_column => 'foo', sort_order => 'DESC', create_table => NULL); ERROR: 'create_table' cannot be NULL /* NULL verbose */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => 42, fetch_size => 8, max_records => 103, orderby_column => 'foo', sort_order => 'DESC', create_table => true, verbose => NULL); ERROR: 'verbose' cannot be NULL /* NULL commit_page */ CALL rdf_fdw_clone_table( foreign_table => 't1', target_table => 't1_local', begin_offset => 42, fetch_size => 8, max_records => 103, orderby_column => 'foo', sort_order => 'DESC', create_table => true, verbose => false, commit_page => NULL); ERROR: 'commit_page' cannot be NULL DROP TABLE IF EXISTS t1_local, t2_local;