CREATE EXTENSION IF NOT EXISTS rdf_fdw; CREATE SERVER dbpedia FOREIGN DATA WRAPPER rdf_fdw OPTIONS ( endpoint 'https://dbpedia.org/sparql', format 'application/sparql-results+xml', enable_pushdown 'true' ); /* ################### DBpedia Films ################### */ CREATE FOREIGN TABLE 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 'tRuE', 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) } '); -- GROUP BY columns by their aliases and index in the -- SELECT clause SELECT film_id AS id, name, runtime, released AS rel FROM film ORDER BY rel DESC, 1 ASC LIMIT 5; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?film ?name ?released ?runtime { ?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) } ORDER BY DESC (?released) ASC (?film) LIMIT 5 id | name | runtime | rel ----------------------------------------------------------------+-----------------------+---------+------------ http://dbpedia.org/resource/Blade_of_the_47_Ronin | Blade of the 47 Ronin | 106 | 10-25-2022 http://dbpedia.org/resource/The_Flying_Sailor_(film) | The Flying Sailor | 8 | 06-13-2022 http://dbpedia.org/resource/Whispering_Corridors_(film_series) | Whispering Corridors | 492 | 06-17-2021 http://dbpedia.org/resource/Darling_(2021_film) | Darling | 156 | 11-10-2020 http://dbpedia.org/resource/Scare_Package | Scare Package | 107 | 06-18-2020 (5 rows) -- FETCH FIRST x ROWS ONLY is not pushed down, as the -- query contains aggregates SELECT count(runtime),avg(runtime) FROM film ORDER BY count(runtime),avg(runtime) FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?runtime { ?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) } count | avg -------+---------------------- 101 | 337.4752475247524752 (1 row) -- OFFSET x ROWS + FETCH FIRST x ROWS ONLY are pushed down. -- ORDER BY is pushed down. SELECT name, released FROM film ORDER BY released DESC, name ASC OFFSET 5 ROWS FETCH FIRST 10 ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released { ?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) } ORDER BY DESC (?released) ASC (?name) LIMIT 15 name | released -----------------------------+------------ Sagardwipey Jawker Dhan | 12-06-2019 Old-Timers | 10-17-2019 Mitin Mashi | 09-18-2019 Bu | 04-11-2019 Bu, Kasih Suci | 04-11-2019 Hotel Milan | 08-28-2018 Happy Days | 01-26-2018 Middle Class Abbayi | 12-11-2017 Chocolate City: Vegas Strip | 08-12-2017 Breach of Trust | 06-09-2017 (10 rows) -- OFFSET x AND LIMIT x are pushed down -- ORDER BY is pushed down. SELECT name, released FROM film ORDER BY released DESC, name ASC OFFSET 5 LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released { ?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) } ORDER BY DESC (?released) ASC (?name) LIMIT 15 name | released -----------------------------+------------ Sagardwipey Jawker Dhan | 12-06-2019 Old-Timers | 10-17-2019 Mitin Mashi | 09-18-2019 Bu | 04-11-2019 Bu, Kasih Suci | 04-11-2019 Hotel Milan | 08-28-2018 Happy Days | 01-26-2018 Middle Class Abbayi | 12-11-2017 Chocolate City: Vegas Strip | 08-12-2017 Breach of Trust | 06-09-2017 (10 rows) -- LIMIT + OFFSET won't be pushed down, as this is not -- safe to do so with DISTINCT. SELECT DISTINCT name, released FROM film ORDER BY released DESC, name ASC OFFSET 5 LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released { ?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) } ORDER BY DESC (?released) ASC (?name) name | released -----------------------------+------------ Sagardwipey Jawker Dhan | 12-06-2019 Old-Timers | 10-17-2019 Mitin Mashi | 09-18-2019 Bu | 04-11-2019 Bu, Kasih Suci | 04-11-2019 Hotel Milan | 08-28-2018 Happy Days | 01-26-2018 Middle Class Abbayi | 12-11-2017 Chocolate City: Vegas Strip | 08-12-2017 Breach of Trust | 06-09-2017 (10 rows) -- LIMIT + OFFSET won't be pushed down, as this is not -- safe to do so with DISTINCT ON expressions. SELECT DISTINCT ON (released) name, released FROM film ORDER BY released DESC, name ASC OFFSET 5 LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released { ?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) } ORDER BY DESC (?released) ASC (?name) name | released ------------------------------+------------ Sagardwipey Jawker Dhan | 12-06-2019 Old-Timers | 10-17-2019 Mitin Mashi | 09-18-2019 Bu | 04-11-2019 Hotel Milan | 08-28-2018 Happy Days | 01-26-2018 Middle Class Abbayi | 12-11-2017 Chocolate City: Vegas Strip | 08-12-2017 Breach of Trust | 06-09-2017 The Three Stooges Collection | 10-18-2016 (10 rows) -- All three conditions in the WHERE clause are pushed down -- as SPARQL FILTER clauses. The ORDER BY name isn't pushed -- down because of the 'name = 'condition. The FETCH FIRST 3 -- ROWS ONLY is pushed down. SELECT name, released, runtime FROM film WHERE name = 'The Life of Adam Lindsay Gordon' AND runtime < 10 AND released < '1930-03-25' ORDER BY name FETCH FIRST 3 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released ?runtime { ?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) FILTER(?runtime < 10) FILTER(?released < "1930-03-25"^^xsd:date) FILTER(?name = "The Life of Adam Lindsay Gordon"@en) } LIMIT 3 name | released | runtime ---------------------------------+------------+--------- The Life of Adam Lindsay Gordon | 08-17-1916 | 5 The Life of Adam Lindsay Gordon | 09-04-1916 | 5 (2 rows) /* All three conditions in the WHERE clause are pushed down. The lower() function call will be translated to LCASE in a SPARQL FILTER expression. */ SELECT name, released, runtime FROM film WHERE lower(name) = 'the life of adam lindsay gordon' AND runtime < 10 AND released < '1930-03-25' ORDER BY released FETCH FIRST 3 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released ?runtime { ?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) FILTER(?runtime < 10) FILTER(?released < "1930-03-25"^^xsd:date) FILTER(LCASE(STR(?name)) = "the life of adam lindsay gordon") } ORDER BY ASC (?released) LIMIT 3 name | released | runtime ---------------------------------+------------+--------- The Life of Adam Lindsay Gordon | 08-17-1916 | 5 The Life of Adam Lindsay Gordon | 09-04-1916 | 5 (2 rows) /* All three conditions in the WHERE clause are pushed down. The upper() function call will be translated to UCASE in a SPARQL FILTER expression. */ SELECT name, released, runtime FROM film WHERE upper(name) = 'THE LIFE OF ADAM LINDSAY GORDON' AND runtime < 10 AND released < '1930-03-25' ORDER BY released FETCH FIRST 3 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released ?runtime { ?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) FILTER(?runtime < 10) FILTER(?released < "1930-03-25"^^xsd:date) FILTER(UCASE(STR(?name)) = "THE LIFE OF ADAM LINDSAY GORDON") } ORDER BY ASC (?released) LIMIT 3 name | released | runtime ---------------------------------+------------+--------- The Life of Adam Lindsay Gordon | 08-17-1916 | 5 The Life of Adam Lindsay Gordon | 09-04-1916 | 5 (2 rows) /* IS NOT NULL isn't supported in SPARQL. OR conditions won't be pushed down. */ SELECT name, released, runtime FROM film WHERE name IS NOT NULL AND (runtime < 10 OR released < '1930-03-25') ORDER BY released ASC, name DESC FETCH FIRST 3 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released ?runtime { ?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) } ORDER BY ASC (?released) DESC (?name) name | released | runtime ----------------------------+------------+--------- Nervy Nat Kisses the Bride | 09-30-1904 | 2 When Knights Were Bold | 05-20-1908 | 1 Pippa Passes | 10-04-1909 | 1 (3 rows) /* Operator <> will be translated to != */ SELECT name, released, runtime FROM film WHERE name = 'The Life of Adam Lindsay Gordon' AND released <> '1930-03-25'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released ?runtime { ?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) FILTER(?released != "1930-03-25"^^xsd:date) FILTER(?name = "The Life of Adam Lindsay Gordon"@en) } name | released | runtime ---------------------------------+------------+--------- The Life of Adam Lindsay Gordon | 08-17-1916 | 5 The Life of Adam Lindsay Gordon | 09-04-1916 | 5 (2 rows) /* ################### DBpedia Politicians ################### */ CREATE FOREIGN TABLE politicians ( uri text OPTIONS (variable '?person', nodetype 'iri'), name text OPTIONS (variable '?personname', nodetype 'literal', literaltype 'xsd:string'), name_upper text OPTIONS (variable '?name_ucase', nodetype 'literal', expression 'UCASE(?personname)'), name_len int OPTIONS (variable '?name_len', nodetype 'literal', expression 'STRLEN(?personname)'), birthdate date OPTIONS (variable '?birthdate', nodetype 'literal', literaltype 'xsd:date'), party text OPTIONS (variable '?partyname', nodetype 'literal', literaltype 'xsd:string'), wikiid int OPTIONS (variable '?pageid', nodetype 'literal', literaltype 'xsd:nonNegativeInteger'), ts timestamp with time zone OPTIONS (variable '?ts', expression '"2002-03-08T14:33:42"^^xsd:dateTime', literaltype 'xsd:dateTime'), country text OPTIONS (variable '?country', nodetype 'literal', language 'en') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") } '); /* * DISTINCT and WHERE clause will be pushed down. * LIMIT won't be pushed down, as the SQL contains ORDER BY */ SELECT DISTINCT name, birthdate, party, country FROM politicians WHERE country = 'Germany' ORDER BY birthdate DESC, party ASC LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?country = "Germany"@en) } ORDER BY DESC (?birthdate) ASC (?partyname) ASC (?personname) name | birthdate | party | country -------------------+------------+-----------------------------------------+--------- Klara Schedlich | 01-04-2000 | Bündnis 90/Die Grünen | Germany Niklas Wagener | 04-16-1998 | Bündnis 90/Die Grünen | Germany Jakob Blankenburg | 08-05-1997 | Sozialdemokratische Partei Deutschlands | Germany Fabian Funke | 07-25-1997 | Sozialdemokratische Partei Deutschlands | Germany Max Lucks | 04-19-1997 | Bündnis 90/Die Grünen | Germany June Tomiak | 02-08-1997 | Bündnis 90/Die Grünen | Germany Merle Spellerberg | 11-13-1996 | Bündnis 90/Die Grünen | Germany Karoline Otte | 09-11-1996 | Bündnis 90/Die Grünen | Germany Bruno Hönel | 04-01-1996 | Bündnis 90/Die Grünen | Germany Lukas Benner | 02-20-1996 | Bündnis 90/Die Grünen | Germany (10 rows) /* * DISTINCT ON won't be pushed - SPARQL does not support it. * WHERE clause will be pushed down. * LIMIT won't be pushed down, as the SQL contains ORDER BY */ SELECT DISTINCT ON (birthdate) name, birthdate FROM politicians WHERE country = 'Germany' ORDER BY birthdate LIMIT 3; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?country = "Germany"@en) } ORDER BY ASC (?birthdate) name | birthdate ----------------------+------------ Lewin Louis Aronsohn | 10-18-1850 August Schröder | 11-21-1855 Carl Petersen | 01-01-1868 (3 rows) /* * SELECT does not contain the column 'country' but it is * used in the WHERE clause. We automatically add it to the * SPARQL SELECT clause, so that it can be also filtered locally */ SELECT name, birthdate, party FROM politicians WHERE country = 'Germany' AND birthdate > '1995-12-31' ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?birthdate > "1995-12-31"^^xsd:date) FILTER(?country = "Germany"@en) } ORDER BY DESC (?birthdate) ASC (?partyname) LIMIT 5 name | birthdate | party -------------------+------------+----------------------------------------- Klara Schedlich | 01-04-2000 | Bündnis 90/Die Grünen Niklas Wagener | 04-16-1998 | Bündnis 90/Die Grünen Jakob Blankenburg | 08-05-1997 | Sozialdemokratische Partei Deutschlands Fabian Funke | 07-25-1997 | Sozialdemokratische Partei Deutschlands Max Lucks | 04-19-1997 | Bündnis 90/Die Grünen (5 rows) /* * SELECT does not contain all columns used in the * WHERE clause (column 'name') and this column is * used in a function call "WHERE lower(country)". * All available columns / variables will be used in * the SPARQL SELECT. */ SELECT name, birthdate, party FROM politicians WHERE lower(country) = 'germany' AND birthdate > '1995-12-31' ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?birthdate > "1995-12-31"^^xsd:date) FILTER(LCASE(STR(?country)) = "germany") } ORDER BY DESC (?birthdate) ASC (?partyname) LIMIT 5 name | birthdate | party -------------------+------------+----------------------------------------- Klara Schedlich | 01-04-2000 | Bündnis 90/Die Grünen Niklas Wagener | 04-16-1998 | Bündnis 90/Die Grünen Jakob Blankenburg | 08-05-1997 | Sozialdemokratische Partei Deutschlands Fabian Funke | 07-25-1997 | Sozialdemokratische Partei Deutschlands Max Lucks | 04-19-1997 | Bündnis 90/Die Grünen (5 rows) /* * "WHERE country IN " is going to be pushed down in a * FILTER expression. */ SELECT name, birthdate, party FROM politicians WHERE country IN ('Germany','France','Portugal') ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?country IN ("Germany"@en, "France"@en, "Portugal"@en)) } ORDER BY DESC (?birthdate) ASC (?partyname) LIMIT 5 name | birthdate | party --------------------+------------+------------------------ Louis Boyard | 08-26-2000 | La France insoumise Klara Schedlich | 01-04-2000 | Bündnis 90/Die Grünen Pierrick Berteloot | 01-11-1999 | Rassemblement National Rita Matias | 10-17-1998 | Chega Niklas Wagener | 04-16-1998 | Bündnis 90/Die Grünen (5 rows) /* * "WHERE country NOT IN " is going to be pushed down in a * FILTER expression. */ SELECT name, birthdate, party, country FROM politicians WHERE country NOT IN ('Germany','France','Portugal') ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?country NOT IN ("Germany"@en, "France"@en, "Portugal"@en)) } ORDER BY DESC (?birthdate) ASC (?partyname) LIMIT 5 name | birthdate | party | country -----------------+------------+------------------------------+--------------- Bob Evnen | 11-29-2022 | National Republican Party | United States Bob Evnen | 11-29-2022 | Republikanische Partei | United States Russell Findlay | 01-07-2021 | Scottish Conservative Party | Scotland Rachael Thomas | 11-15-2020 | Konservative Partei Kanadas | Canada Ausma Malik | 12-17-2018 | Ontario New Democratic Party | Canada (5 rows) /* * "= ANY(ARRAY[])" is going to be pushed down in a * FILTER expression. */ SELECT name, birthdate, party FROM politicians WHERE country = ANY(ARRAY['Germany','France','Portugal']) ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(?country IN ("Germany"@en, "France"@en, "Portugal"@en)) } ORDER BY DESC (?birthdate) ASC (?partyname) LIMIT 5 name | birthdate | party --------------------+------------+------------------------ Louis Boyard | 08-26-2000 | La France insoumise Klara Schedlich | 01-04-2000 | Bündnis 90/Die Grünen Pierrick Berteloot | 01-11-1999 | Rassemblement National Rita Matias | 10-17-1998 | Chega Niklas Wagener | 04-16-1998 | Bündnis 90/Die Grünen (5 rows) /* * "<> ANY(ARRAY[])" is not going to be pushed down! */ SELECT name, birthdate, party, country FROM politicians WHERE country <> ANY(ARRAY['Germany','France','Portugal']) ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") } ORDER BY DESC (?birthdate) ASC (?partyname) name | birthdate | party | country -----------------+------------+------------------------------+--------------- Bob Evnen | 11-29-2022 | National Republican Party | United States Bob Evnen | 11-29-2022 | Republikanische Partei | United States Russell Findlay | 01-07-2021 | Scottish Conservative Party | Scotland Rachael Thomas | 11-15-2020 | Konservative Partei Kanadas | Canada Ausma Malik | 12-17-2018 | Ontario New Democratic Party | Canada (5 rows) /* * "~~* ANY(ARRAY[])" is not going to be pushed down! */ SELECT name, birthdate, party, country FROM politicians WHERE country ~~* ANY(ARRAY['%UsTr%','%TugA%']) ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") } ORDER BY DESC (?birthdate) ASC (?partyname) name | birthdate | party | country --------------------+------------+-----------------------------+----------- Stephen Mullighan | 04-14-2012 | Australian Labor Party | Australia Rita Matias | 10-17-1998 | Chega | Portugal Jordon Steele-John | 10-14-1994 | Australian Greens | Australia Kira Grünberg | 08-13-1993 | Österreichische Volkspartei | Austria Joana Sá Pereira | 07-04-1993 | Partido Socialista | Portugal (5 rows) /* * "~~ ANY(ARRAY[])" is not going to be pushed down! */ SELECT name, birthdate, party, country FROM politicians WHERE country ~~ ANY(ARRAY['__land%','%GERMAN%']) ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") } ORDER BY DESC (?birthdate) ASC (?partyname) name | birthdate | party | country --------------------------+------------+------------------------+--------- Łukasz Rzepecki | 09-05-1992 | Kukiz’15 | Poland Kinga Magdalena Gajewska | 07-22-1990 | Platforma Obywatelska | Poland Adam Andruszkiewicz | 06-30-1990 | Prawo i Sprawiedliwość | Poland Adam Andruszkiewicz | 06-30-1990 | Ruch Narodowy | Poland Adam Andruszkiewicz | 06-30-1990 | Wolni i Solidarni | Poland (5 rows) /* * "NOT country ~~* ANY(ARRAY[])" is not going to be pushed down! */ SELECT name, birthdate, party, country FROM politicians WHERE NOT country ~~* ANY(ARRAY['%UnItEd%','%land%']) ORDER BY birthdate DESC, party ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") } ORDER BY DESC (?birthdate) ASC (?partyname) name | birthdate | party | country -------------------+------------+------------------------------+-------------- Rachael Thomas | 11-15-2020 | Konservative Partei Kanadas | Canada Ausma Malik | 12-17-2018 | Ontario New Democratic Party | Canada Kenny Motsamai | 02-18-2016 | Economic Freedom Fighters | South Africa Kenny Motsamai | 02-18-2016 | Pan Africanist Congress | South Africa Margarita Bolaños | 03-15-2014 | Partido Acción Ciudadana | Costa Rica (5 rows) /* * Pushdown test for UPPER and LOWER */ SELECT uri, name, name_upper FROM politicians WHERE upper(name) = 'WILL' || ' BOND' AND 'will bond' = lower(name) AND lower(name_upper) = lower(name) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (UCASE(?personname) AS ?name_ucase) { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(UCASE(STR(?personname)) = "WILL BOND") FILTER(LCASE(STR(?personname)) = "will bond") FILTER(LCASE(STR(UCASE(?personname))) = "will bond") } LIMIT 1 uri | name | name_upper ---------------------------------------+-----------+------------ http://dbpedia.org/resource/Will_Bond | Will Bond | WILL BOND (1 row) /* * Pushdown test for LENGTH */ SELECT uri, name, name_upper FROM politicians WHERE 47035308 = wikiid AND length(name) = 9 AND length(name) < (9+1) AND length(name_upper) < (40+2) AND 13 <= LENGTH(country) AND 5 < LENGTH(name_upper) AND LENGTH(name) <= LENGTH(country) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (UCASE(?personname) AS ?name_ucase) ?pageid ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(STRLEN(STR(?personname)) < 10) FILTER(STRLEN(STR(UCASE(?personname))) < 42) FILTER(13 <= STRLEN(STR(?country))) FILTER(5 < STRLEN(STR(UCASE(?personname)))) FILTER(STRLEN(STR(?personname)) <= STRLEN(STR(?country))) FILTER(47035308 = ?pageid) FILTER(STRLEN(STR(?personname)) = 9) } LIMIT 1 uri | name | name_upper ---------------------------------------+-----------+------------ http://dbpedia.org/resource/Will_Bond | Will Bond | WILL BOND (1 row) /* * Pushdown test for ABS */ SELECT uri, name, name_len FROM politicians WHERE 47035308 = wikiid AND abs(wikiid) <> 42.73 AND abs(wikiid) <> abs(-300) AND wikiid = abs(-47035308) AND wikiid > abs(name_len) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (STRLEN(?personname) AS ?name_len) ?pageid { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(ABS(?pageid) != 42.73) FILTER(ABS(?pageid) != 300) FILTER(?pageid > ABS(STRLEN(?personname))) FILTER(?pageid = 47035308) } LIMIT 1 uri | name | name_len ---------------------------------------+-----------+---------- http://dbpedia.org/resource/Will_Bond | Will Bond | 9 (1 row) /* * Pushdown test for CEIL */ SELECT uri, name, name_len FROM politicians WHERE 47035308 = wikiid AND CEIL(wikiid) = ceil(47035307.1) AND ceil(name_len) <> ceil(wikiid) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (STRLEN(?personname) AS ?name_len) ?pageid { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(CEIL(STRLEN(?personname)) != CEIL(?pageid)) FILTER(47035308 = ?pageid) FILTER(CEIL(?pageid) = 47035308) } LIMIT 1 uri | name | name_len ---------------------------------------+-----------+---------- http://dbpedia.org/resource/Will_Bond | Will Bond | 9 (1 row) /* * Pushdown test for ROUND */ SELECT uri, name, name_len FROM politicians WHERE 47035308 = wikiid AND round(wikiid) = ROUND(47035308.4) AND round(name_len) <> round(wikiid) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (STRLEN(?personname) AS ?name_len) ?pageid { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(ROUND(STRLEN(?personname)) != ROUND(?pageid)) FILTER(47035308 = ?pageid) FILTER(ROUND(?pageid) = 47035308) } LIMIT 1 uri | name | name_len ---------------------------------------+-----------+---------- http://dbpedia.org/resource/Will_Bond | Will Bond | 9 (1 row) /* * Pushdown test for FLOOR */ SELECT uri, name, name_len FROM politicians WHERE 47035308 = wikiid AND Floor(wikiid) < 47035308.99999 AND floor(name_len) <> floor(wikiid) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (STRLEN(?personname) AS ?name_len) ?pageid { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(FLOOR(?pageid) < 47035308.99999) FILTER(FLOOR(STRLEN(?personname)) != FLOOR(?pageid)) FILTER(47035308 = ?pageid) } LIMIT 1 uri | name | name_len ---------------------------------------+-----------+---------- http://dbpedia.org/resource/Will_Bond | Will Bond | 9 (1 row) /* * Pushdown test for SUBSTRING */ SELECT uri, name, name_upper FROM politicians WHERE 47035308 = wikiid AND SUBstring(name,1,4) = 'Will' AND 'Bond' = subSTRING(name, 6,4) AND lower(SUBstring(name,1,4)) = 'will' AND SUBSTRING(lower(name_upper),1,4) = 'will' FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname (UCASE(?personname) AS ?name_ucase) ?pageid { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(47035308 = ?pageid) FILTER(SUBSTR(STR(?personname), 1, 4) = "Will") FILTER("Bond" = SUBSTR(STR(?personname), 6, 4)) FILTER(LCASE(SUBSTR(STR(?personname), 1, 4)) = "will") FILTER(SUBSTR(LCASE(STR(UCASE(?personname))), 1, 4) = "will") } LIMIT 1 uri | name | name_upper ---------------------------------------+-----------+------------ http://dbpedia.org/resource/Will_Bond | Will Bond | WILL BOND (1 row) /* * Pushdown test for EXTRACT * Fields in singular form */ SELECT uri, name, birthdate, ts FROM politicians WHERE 47035308 = wikiid AND extract(YEAR FROM birthdate) = 1970 AND EXTRACT(month FROM birthdate) = 4 AND Extract(Day FROM birthdate) = 8 AND EXTRACT(hour FROM ts) = 14 AND ExtracT(minute FROM ts) = 33 AND EXTRACT(second FROM ts) = 42 FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname ?birthdate ?pageid ("2002-03-08T14:33:42"^^xsd:dateTime AS ?ts) { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(47035308 = ?pageid) FILTER(YEAR(?birthdate) = 1970) FILTER(MONTH(?birthdate) = 4) FILTER(DAY(?birthdate) = 8) FILTER(HOURS("2002-03-08T14:33:42"^^xsd:dateTime) = 14) FILTER(MINUTES("2002-03-08T14:33:42"^^xsd:dateTime) = 33) FILTER(SECONDS("2002-03-08T14:33:42"^^xsd:dateTime) = 42) } LIMIT 1 uri | name | birthdate | ts ---------------------------------------+-----------+------------+------------------------------ http://dbpedia.org/resource/Will_Bond | Will Bond | 04-08-1970 | Fri Mar 08 14:33:42 2002 PST (1 row) /* * Pushdown test for EXTRACT * Fields in plural form */ SELECT uri, name, birthdate, ts FROM politicians WHERE 47035308 = wikiid AND EXTRACT(YEARs FROM birthdate) = 1970 AND EXTRACT(months FROM birthdate) = 4 AND EXTRACT(Days FROM birthdate) = 8 AND EXTRACT(hours FROM ts) = 14 AND EXTRACT(minutes FROM ts) = 33 AND EXTRACT(seconds FROM ts) = 42 FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname ?birthdate ?pageid ("2002-03-08T14:33:42"^^xsd:dateTime AS ?ts) { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(47035308 = ?pageid) FILTER(YEAR(?birthdate) = 1970) FILTER(MONTH(?birthdate) = 4) FILTER(DAY(?birthdate) = 8) FILTER(HOURS("2002-03-08T14:33:42"^^xsd:dateTime) = 14) FILTER(MINUTES("2002-03-08T14:33:42"^^xsd:dateTime) = 33) FILTER(SECONDS("2002-03-08T14:33:42"^^xsd:dateTime) = 42) } LIMIT 1 uri | name | birthdate | ts ---------------------------------------+-----------+------------+------------------------------ http://dbpedia.org/resource/Will_Bond | Will Bond | 04-08-1970 | Fri Mar 08 14:33:42 2002 PST (1 row) /* * Pushdown test for DATE_PART * Fields in plural form */ SELECT uri, name, birthdate, ts FROM politicians WHERE 47035308 = wikiid AND 1970 = date_part(lower('years'), birthdate) AND date_part('months', birthdate) = 4 AND 8 = date_part('days', birthdate) AND date_part('hours', ts) = 14 AND 33 = date_part('minutes', ts) AND date_part('seconds', ts) = 42 FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname ?birthdate ?pageid ("2002-03-08T14:33:42"^^xsd:dateTime AS ?ts) { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(47035308 = ?pageid) FILTER(1970 = YEAR(?birthdate)) FILTER(MONTH(?birthdate) = 4) FILTER(8 = DAY(?birthdate)) FILTER(HOURS("2002-03-08T14:33:42"^^xsd:dateTime) = 14) FILTER(33 = MINUTES("2002-03-08T14:33:42"^^xsd:dateTime)) FILTER(SECONDS("2002-03-08T14:33:42"^^xsd:dateTime) = 42) } LIMIT 1 uri | name | birthdate | ts ---------------------------------------+-----------+------------+------------------------------ http://dbpedia.org/resource/Will_Bond | Will Bond | 04-08-1970 | Fri Mar 08 14:33:42 2002 PST (1 row) /* * Pushdown test for DATE_PART * Fields in singular form */ SELECT uri, name, birthdate, ts FROM politicians WHERE 47035308 = wikiid AND 1970 = date_part(lower('year'), birthdate) AND date_part('month', birthdate) = 4 AND 8 = date_part('day', birthdate) AND date_part('hour', ts) = 14 AND 33 = date_part('minute', ts) AND date_part('second', ts) = 42 FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname ?birthdate ?pageid ("2002-03-08T14:33:42"^^xsd:dateTime AS ?ts) { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(47035308 = ?pageid) FILTER(1970 = YEAR(?birthdate)) FILTER(MONTH(?birthdate) = 4) FILTER(8 = DAY(?birthdate)) FILTER(HOURS("2002-03-08T14:33:42"^^xsd:dateTime) = 14) FILTER(33 = MINUTES("2002-03-08T14:33:42"^^xsd:dateTime)) FILTER(SECONDS("2002-03-08T14:33:42"^^xsd:dateTime) = 42) } LIMIT 1 uri | name | birthdate | ts ---------------------------------------+-----------+------------+------------------------------ http://dbpedia.org/resource/Will_Bond | Will Bond | 04-08-1970 | Fri Mar 08 14:33:42 2002 PST (1 row) /* * Pushdown test for MD5 */ SELECT uri, name, birthdate, ts FROM politicians WHERE 47035308 = wikiid AND md5(name) = 'dd16aacc7f77cec7ed83139f81704577' AND 'foo' <> md5(name) FETCH FIRST ROW ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?personname ?birthdate ?pageid ("2002-03-08T14:33:42"^^xsd:dateTime AS ?ts) { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party ; dbo:wikiPageID ?pageid . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER("foo" != MD5(STR(?personname))) FILTER(47035308 = ?pageid) FILTER(MD5(STR(?personname)) = "dd16aacc7f77cec7ed83139f81704577") } LIMIT 1 uri | name | birthdate | ts ---------------------------------------+-----------+------------+------------------------------ http://dbpedia.org/resource/Will_Bond | Will Bond | 04-08-1970 | Fri Mar 08 14:33:42 2002 PST (1 row) /* ################### SPARQL Aggregators ################### */ CREATE FOREIGN TABLE party_members ( country text OPTIONS (variable '?country'), party text OPTIONS (variable '?partyname'), nmembers int OPTIONS (variable '?qt') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbp: PREFIX dbo: SELECT ?country ?partyname COUNT(?person) AS ?qt WHERE { ?person a dbo:Politician ; dbo:party ?party . ?party dbp:country ?country ; dbp:name ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} } GROUP BY ?country ?partyname ORDER BY DESC (?qt) '); /* * All filters (WHERE, FETCH and ILIKE) will be applied locally, * as the raw SPARQL cannot be parsed - it contains aggregators. */ SELECT party, nmembers FROM party_members WHERE country ~~* '%isle of man%' ORDER BY nmembers ASC FETCH FIRST 5 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?country ?partyname COUNT(?person) AS ?qt WHERE { ?person a dbo:Politician ; dbo:party ?party . ?party dbp:country ?country ; dbp:name ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} } GROUP BY ?country ?partyname ORDER BY DESC (?qt) party | nmembers ----------------------+---------- Mec Vannin | 1 Manx Labour Party | 1 Liberal Vannin Party | 3 (3 rows) /* ################### SPARQL UNION ################### */ CREATE FOREIGN TABLE chanceler_candidates ( name text OPTIONS (variable '?name'), party text OPTIONS (variable '?partyname'), birthdate date OPTIONS (variable '?birthdate', literaltype 'xsd:date') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbp: PREFIX dbo: SELECT ?name ?partyname ?birthdate WHERE { ?person rdfs:label ?name { ?person rdfs:label "Friedrich Merz"@de } UNION { ?person rdfs:label "Markus Söder"@de } ?person dbo:birthDate ?birthdate . ?person dbo:party ?party . ?party dbp:name ?partyname FILTER(LANG(?name) = "de") } '); /* ################### Expression Check ################### */ /* * All filters (WHERE and ORDER BY) will be applied locally, * as the raw SPARQL cannot be parsed - it contains UNION. */ SELECT name, party, birthdate FROM chanceler_candidates WHERE party <> '' ORDER BY birthdate DESC; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?name ?partyname ?birthdate WHERE { ?person rdfs:label ?name { ?person rdfs:label "Friedrich Merz"@de } UNION { ?person rdfs:label "Markus Söder"@de } ?person dbo:birthDate ?birthdate . ?person dbo:party ?party . ?party dbp:name ?partyname FILTER(LANG(?name) = "de") } name | party | birthdate ----------------+---------------------------------------+------------ Markus Söder | Christian Social Union in Bavaria | 01-05-1967 Friedrich Merz | Christian Democratic Union of Germany | 11-11-1955 (2 rows) CREATE FOREIGN TABLE german_public_universities ( id text OPTIONS (variable '?uri'), name text OPTIONS (variable '?name'), lon numeric OPTIONS (variable '?lon'), lat numeric OPTIONS (variable '?lat'), wkt text OPTIONS (variable '?wkt', expression 'CONCAT("POINT(",?lon," ",?lat,")")') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX geo: PREFIX dbp: PREFIX dbo: PREFIX dbr: SELECT ?uri ?name ?lon ?lat WHERE { ?uri dbo:type dbr:Public_university ; dbp:name ?name; geo:lat ?lat; geo:long ?lon; dbp:country dbr:Germany }' ); /* * This will return a WKT representation of geo coordinates, although not * previously defined in the SPARQL query. The variables '?lon' and 'uri' * are removed from the SPARQL SELECT clause, as they were not used in the * SQL query. */ SELECT name, wkt FROM german_public_universities ORDER BY lat DESC LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX geo: PREFIX dbp: PREFIX dbo: PREFIX dbr: SELECT ?name ?lat (CONCAT("POINT(",?lon," ",?lat,")") AS ?wkt) { ?uri dbo:type dbr:Public_university ; dbp:name ?name; geo:lat ?lat; geo:long ?lon; dbp:country dbr:Germany } ORDER BY DESC (?lat) LIMIT 10 name | wkt ------------------------------------+-------------------------------------------- Europa Universität Flensburg | POINT(9.451999664306641 54.7760009765625) University of Greifswald | POINT(13.37462615966797 54.09463882446289) University of Lübeck | POINT(10.70583343505859 53.83694458007812) Hamburg University of Technology | POINT(9.969444274902344 53.46083450317383) University of Bremen | POINT(8.853610992431641 53.10861206054688) University of the Arts Bremen | POINT(8.767499923706055 53.09805679321289) Humboldt University of Berlin | POINT(13.39333343505859 52.5180549621582) Berlin University of the Arts | POINT(13.32694435119629 52.5091667175293) Berlin School of Economics and Law | POINT(13.33777809143066 52.48527908325195) Free University of Berlin | POINT(13.29055595397949 52.45305633544922) (10 rows) /* * WHERE clause containing a column with 'expression' OPTION. */ SELECT name, wkt FROM german_public_universities WHERE id <> '' AND lat > 52 AND lon < 9 AND wkt = 'POINT(8.49305534362793 52.03777694702148)'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX geo: PREFIX dbp: PREFIX dbo: PREFIX dbr: SELECT ?uri ?name ?lon ?lat (CONCAT("POINT(",?lon," ",?lat,")") AS ?wkt) { ?uri dbo:type dbr:Public_university ; dbp:name ?name; geo:lat ?lat; geo:long ?lon; dbp:country dbr:Germany FILTER(?uri != "") FILTER(?lat > 52) FILTER(?lon < 9) FILTER(CONCAT("POINT(",?lon," ",?lat,")") = "POINT(8.49305534362793 52.03777694702148)") } name | wkt ----------------------+------------------------------------------- Bielefeld University | POINT(8.49305534362793 52.03777694702148) (1 row) /* * WHERE conditions with expressions */ SELECT name, lon < 9, lat > 52 FROM german_public_universities WHERE id <> '' AND lat - 1 > 52 AND -- conditions with expressions in the left side won't be pushed down. lon < 8+1; -- the expression in the right side will be computed before pushdown. INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX geo: PREFIX dbp: PREFIX dbo: PREFIX dbr: SELECT ?uri ?name ?lon ?lat { ?uri dbo:type dbr:Public_university ; dbp:name ?name; geo:lat ?lat; geo:long ?lon; dbp:country dbr:Germany FILTER(?uri != "") FILTER(?lon < 9) } name | ?column? | ?column? -------------------------------+----------+---------- University of Bremen | t | t University of the Arts Bremen | t | t (2 rows) /* * SPARQL contains a LIMIT. Nothing will be pushed down. */ CREATE FOREIGN TABLE person1 ( person text OPTIONS (variable '?person'), birthdate text OPTIONS (variable '?birthdate') ) SERVER dbpedia OPTIONS (sparql ' PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate } LIMIT 1 ', log_sparql 'true'); SELECT birthdate FROM person1 WHERE person = 'foo'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate } LIMIT 1 birthdate ----------- (0 rows) /* * SPARQL contains ORDER BY. Nothing will be pushed down. */ CREATE FOREIGN TABLE person2 ( person text OPTIONS (variable '?person'), birthdate text OPTIONS (variable '?birthdate') ) SERVER dbpedia OPTIONS (sparql ' PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate } ORDER BY DESC(?birthdate) ', log_sparql 'true'); SELECT birthdate FROM person2 WHERE person = 'foo'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate } ORDER BY DESC(?birthdate) birthdate ----------- (0 rows) /* * SPARQL contains no explicit WHERE clause. */ CREATE FOREIGN TABLE person3 ( person text OPTIONS (variable '?person'), birthdate text OPTIONS (variable '?birthdate') ) SERVER dbpedia OPTIONS (sparql ' PREFIX dbp: PREFIX dbo: SELECT * { ?person a dbo:Politician; dbo:birthDate ?birthdate } ', log_sparql 'true'); SELECT birthdate FROM person3 WHERE person <> '' LIMIT 5; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT ?person ?birthdate { ?person a dbo:Politician; dbo:birthDate ?birthdate FILTER(?person != "") } LIMIT 5 birthdate ------------ 1969-06-19 1788-07-14 1962-12-17 1976-03-30 1987-06-30 (5 rows) /* ===================== Pushdown Check ===================== * Tests the result set for filter applied localy and remotely. * The result sets must be identical. */ CREATE FOREIGN TABLE politicians_germany ( uri text OPTIONS (variable '?person'), name text OPTIONS (variable '?personname'), birthdate date OPTIONS (variable '?birthdate', literaltype 'xsd:date'), party text OPTIONS (variable '?partyname'), country text OPTIONS (variable '?country') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(STR(?country) = "Germany") } '); CREATE TABLE t_film_remotefilters AS SELECT name, released, runtime FROM film WHERE name = 'The Life of Adam Lindsay Gordon' AND runtime < 10 AND released < '1930-03-25' ORDER BY name FETCH FIRST 3 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name ?released ?runtime { ?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) FILTER(?runtime < 10) FILTER(?released < "1930-03-25"^^xsd:date) FILTER(?name = "The Life of Adam Lindsay Gordon"@en) } LIMIT 3 CREATE TABLE t_politicians_remotefilters AS SELECT DISTINCT name, birthdate, party, country FROM politicians_germany WHERE birthdate > '1990-12-01' ORDER BY birthdate DESC, party ASC LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?personname ?birthdate ?partyname ?country { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(STR(?country) = "Germany") FILTER(?birthdate > "1990-12-01"^^xsd:date) } ORDER BY DESC (?birthdate) ASC (?partyname) ASC (?personname) ASC (?country) /* Disabling enable_pushdown OPTION */ ALTER FOREIGN TABLE film OPTIONS (ADD enable_pushdown 'false'); ALTER FOREIGN TABLE politicians_germany OPTIONS (ADD enable_pushdown 'false'); CREATE TABLE t_film_localfilters AS SELECT name, released, runtime FROM film WHERE name = 'The Life of Adam Lindsay Gordon' AND runtime < 10 AND released < '1930-03-25' ORDER BY name FETCH FIRST 3 ROWS ONLY; INFO: SPARQL query sent to 'https://dbpedia.org/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) } CREATE TABLE t_politicians_localfilters AS SELECT DISTINCT name, birthdate, party, country FROM politicians_germany WHERE birthdate > '1990-12-01' ORDER BY birthdate DESC, party ASC LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT * WHERE { ?person a dbo:Politician; dbo:birthDate ?birthdate; dbp:name ?personname; dbo:party ?party . ?party dbp:country ?country; rdfs:label ?partyname . FILTER NOT EXISTS {?person dbo:deathDate ?died} FILTER(LANG(?partyname) = "de") FILTER(STR(?country) = "Germany") } SELECT * FROM t_film_remotefilters EXCEPT SELECT * FROM t_film_localfilters; name | released | runtime ------+----------+--------- (0 rows) SELECT * FROM t_politicians_remotefilters EXCEPT SELECT * FROM t_politicians_localfilters; name | birthdate | party | country ------+-----------+-------+--------- (0 rows) /* * Test SPARQL containing LIMIT keyword in a literal */ CREATE FOREIGN TABLE dbpedia_limit ( name text OPTIONS (variable '?name'), description text OPTIONS (variable '?abstract') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT * { dbr:Cacilhas_Lighthouse dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " limit ")) } '); SELECT name FROM dbpedia_limit LIMIT 2; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT ?name { dbr:Cacilhas_Lighthouse dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " limit ")) } LIMIT 2 name --------------------- ''Farol de Cacilhas Cacilhas Lighthouse (2 rows) /* * Test SPARQL containing ORDER BY keyword in a literal */ CREATE FOREIGN TABLE dbpedia_orderby ( name text OPTIONS (variable '?name'), description text OPTIONS (variable '?abstract') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT * { dbr:List_of_flag_names dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " order by ")) } '); SELECT name FROM dbpedia_orderby ORDER BY name DESC LIMIT 2; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT ?name { dbr:List_of_flag_names dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " order by ")) } ORDER BY DESC (?name) LIMIT 2 name --------------------- Jefferson Seal Flag Five Coloured Flag (2 rows) /* * Test SPARQL containing DISTINCT keyword in a literal */ CREATE FOREIGN TABLE dbpedia_distinct ( name text OPTIONS (variable '?name'), description text OPTIONS (variable '?abstract') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT * { dbr:Cadillac_Eldorado dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " distinct ")) } '); SELECT DISTINCT name FROM dbpedia_distinct LIMIT 1; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT DISTINCT ?name { dbr:Cadillac_Eldorado dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " distinct ")) } ORDER BY ASC (?name) name ------ 1957 (1 row) /* * Test SPARQL containing GROUP BY keyword in a literal */ CREATE FOREIGN TABLE dbpedia_groupby ( name text OPTIONS (variable '?name'), description text OPTIONS (variable '?abstract') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT * { dbr:Only_for_Love dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " group by ")) } '); SELECT name FROM dbpedia_groupby LIMIT 1; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX dbp: PREFIX dbo: SELECT ?name { dbr:Only_for_Love dbo:abstract ?abstract ; dbp:name ?name FILTER(REGEX(STR(?abstract), " group by ")) } LIMIT 1 name --------------- Only for Love (1 row) /* * Test SPARQL containing a REDUCED modifier */ CREATE FOREIGN TABLE musical_artists ( uri text OPTIONS (variable '?uri'), name text OPTIONS (variable '?name') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbp: PREFIX dbo: SELECT REDUCED ?uri ?name { ?uri a dbo:MusicalArtist; dbp:name ?name } '); SELECT name FROM musical_artists LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbp: PREFIX dbo: SELECT REDUCED ?name { ?uri a dbo:MusicalArtist; dbp:name ?name } LIMIT 10 name ---------------- Cab Calloway Cab Kaye Cabum Caca Handika Cachao Cacho Castaña Cachorro López CACTUS Tuccho Cadalack Ron (10 rows) /* * Test SPARQL containing multiple FROM clauses */ CREATE FOREIGN TABLE generic_rdf_table ( uri text OPTIONS (variable '?s', nodetype 'iri'), name text OPTIONS (variable '?o') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX rdfs: SELECT * FROM FROM FROM FROM WHERE { ?s rdfs:label ?o . FILTER (LANG(?o)="de" ) } '); SELECT name FROM generic_rdf_table WHERE uri = 'http://dbpedia.org/resource/Isle_of_Man' LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX rdfs: SELECT ?s ?o FROM FROM FROM FROM { ?s rdfs:label ?o . FILTER (LANG(?o)="de" ) FILTER(?s = IRI("http://dbpedia.org/resource/Isle_of_Man")) } LIMIT 10 name ------------- Isle of Man (1 row) /* * Test SPARQL containing a FROM clause */ CREATE FOREIGN TABLE generic_rdf_table2 ( uri text OPTIONS (variable '?s', nodetype 'iri'), name text OPTIONS (variable '?o') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX rdfs: SELECT * FROM WHERE { ?s rdfs:label ?o . FILTER (LANG(?o)="pl" ) } '); SELECT name FROM generic_rdf_table2 WHERE uri = 'http://dbpedia.org/resource/Brazil' LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX rdfs: SELECT ?s ?o FROM { ?s rdfs:label ?o . FILTER (LANG(?o)="pl" ) FILTER(?s = IRI("http://dbpedia.org/resource/Brazil")) } LIMIT 10 name ---------- Brazylia (1 row) /* * Test SPARQL containing FROM and FROM NAMED clauses */ CREATE FOREIGN TABLE generic_rdf_table3 ( uri text OPTIONS (variable '?s', nodetype 'iri'), name text OPTIONS (variable '?o') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX rdfs: SELECT * FROM FROM NAMED FROM NAMED WHERE { ?s rdfs:label ?o . FILTER (LANG(?o)="es" ) } '); SELECT name FROM generic_rdf_table3 WHERE uri = 'http://dbpedia.org/resource/Japan' LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX rdfs: SELECT ?s ?o FROM FROM NAMED FROM NAMED { ?s rdfs:label ?o . FILTER (LANG(?o)="es" ) FILTER(?s = IRI("http://dbpedia.org/resource/Japan")) } LIMIT 10 name ------- Japón (1 row) CREATE FOREIGN TABLE generic_rdf_table4 ( uri text OPTIONS (variable '?s', nodetype 'iri'), name text OPTIONS (variable '?o') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX dbr: PREFIX rdfs: SELECT * FROMFROM NAMEDFROM NAMED WHERE { ?s rdfs:label ?o . FILTER (LANG(?o)="es" ) } '); SELECT name FROM generic_rdf_table4 WHERE uri = 'http://dbpedia.org/resource/Japan' LIMIT 10; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX dbr: PREFIX rdfs: SELECT ?s ?o FROM FROM NAMED FROM NAMED { ?s rdfs:label ?o . FILTER (LANG(?o)="es" ) FILTER(?s = IRI("http://dbpedia.org/resource/Japan")) } LIMIT 10 name ------- Japón (1 row) CREATE FOREIGN TABLE public.regex_test1 ( txt text OPTIONS (variable '?o', nodetype 'literal') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX foaf: SELECT * { foaf:name ?o} '); /* ILIKE expression containing "^" and "$" */ SELECT txt FROM public.regex_test1 WHERE txt ~~* '%^___2reGeX$'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX foaf: SELECT ?o { foaf:name ?o FILTER(REGEX(?o,".*\\^...2reGeX\\$$","i")) } txt ------------- ^txt2regex$ (1 row) CREATE FOREIGN TABLE public.regex_test ( name text OPTIONS (variable '?name', nodetype 'literal'), abstract text OPTIONS (variable '?abstract', expression 'STR(?abs)') ) SERVER dbpedia OPTIONS ( log_sparql 'true', sparql ' PREFIX foaf: PREFIX dbo: PREFIX dbr: SELECT * {?s a dbo:WrittenWork ; dbo:genre dbr:Computer_magazine; dbo:abstract ?abs ; foaf:name ?name . FILTER(LANG(?abs) = "en") }'); /* LIKE and ILIKE expressions containing "." */ SELECT name FROM regex_test WHERE name LIKE '%P.P.%' AND abstract ILIKE '%wITh_MAC__.'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX foaf: PREFIX dbo: PREFIX dbr: SELECT ?name (STR(?abs) AS ?abstract) {?s a dbo:WrittenWork ; dbo:genre dbr:Computer_magazine; dbo:abstract ?abs ; foaf:name ?name . FILTER(LANG(?abs) = "en") FILTER(REGEX(?name,".*P\\.P\\..*")) FILTER(REGEX(STR(?abs),".*wITh.MAC..\\.$","i")) } name ----------------- Call-A.P.P.L.E. (1 row) /* LIKE and ILIKE expressions containing "(" and "[" */ SELECT name FROM regex_test WHERE abstract ~~ '%(IT)%' AND abstract ~~* '%(ABBREVIATED AS cw)%' ; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX foaf: PREFIX dbo: PREFIX dbr: SELECT ?name (STR(?abs) AS ?abstract) {?s a dbo:WrittenWork ; dbo:genre dbr:Computer_magazine; dbo:abstract ?abs ; foaf:name ?name . FILTER(LANG(?abs) = "en") FILTER(REGEX(STR(?abs),".*\\(IT\\).*")) FILTER(REGEX(STR(?abs),".*\\(ABBREVIATED AS cw\\).*","i")) } name --------------- Computerworld (1 row) /* LIKE and ILIKE expressions containing "-" */ SELECT name FROM regex_test WHERE abstract ~~ '%VIC-_0%' AND abstract ~~* '%__-MoNtHlY%' AND name ~~ 'Your___'; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX foaf: PREFIX dbo: PREFIX dbr: SELECT ?name (STR(?abs) AS ?abstract) {?s a dbo:WrittenWork ; dbo:genre dbr:Computer_magazine; dbo:abstract ?abs ; foaf:name ?name . FILTER(LANG(?abs) = "en") FILTER(REGEX(STR(?abs),".*VIC\\-.0.*")) FILTER(REGEX(STR(?abs),".*..\\-MoNtHlY.*","i")) FILTER(REGEX(?name,"^Your...")) } name --------- Your 64 (1 row) /* LIKE and ILIKE expressions containing single and double quotes */ SELECT name FROM regex_test WHERE abstract ~~ '%"went digital."%' AND abstract ~~* E'%cOuNtRy\'s%' ; INFO: SPARQL query sent to 'https://dbpedia.org/sparql': PREFIX foaf: PREFIX dbo: PREFIX dbr: SELECT ?name (STR(?abs) AS ?abstract) {?s a dbo:WrittenWork ; dbo:genre dbr:Computer_magazine; dbo:abstract ?abs ; foaf:name ?name . FILTER(LANG(?abs) = "en") FILTER(REGEX(STR(?abs),".*\"went digital\\.\".*")) FILTER(REGEX(STR(?abs),".*cOuNtRy's.*","i")) } name --------------- Computerworld (1 row) DROP SERVER dbpedia CASCADE; NOTICE: drop cascades to 20 other objects DETAIL: drop cascades to foreign table film drop cascades to foreign table politicians drop cascades to foreign table party_members drop cascades to foreign table chanceler_candidates drop cascades to foreign table german_public_universities drop cascades to foreign table person1 drop cascades to foreign table person2 drop cascades to foreign table person3 drop cascades to foreign table politicians_germany drop cascades to foreign table dbpedia_limit drop cascades to foreign table dbpedia_orderby drop cascades to foreign table dbpedia_distinct drop cascades to foreign table dbpedia_groupby drop cascades to foreign table musical_artists drop cascades to foreign table generic_rdf_table drop cascades to foreign table generic_rdf_table2 drop cascades to foreign table generic_rdf_table3 drop cascades to foreign table generic_rdf_table4 drop cascades to foreign table regex_test1 drop cascades to foreign table regex_test