CREATE SERVER wikidata
FOREIGN DATA WRAPPER rdf_fdw 
OPTIONS (
  endpoint 'https://query.wikidata.org/sparql'
);
CREATE FOREIGN TABLE ft (
  p rdfnode OPTIONS (variable '?p'),
  o rdfnode OPTIONS (variable '?o')
)
SERVER wikidata OPTIONS (
  sparql 'SELECT * {wd:Q192490 ?p ?o}'
);
/* EXPLAIN only */
EXPLAIN
SELECT p, o FROM ft;
                            QUERY PLAN                            
------------------------------------------------------------------
 Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
   Foreign Server: wikidata
   Pushdown: enabled
   Remote Select: ?p ?o 
(4 rows)

EXPLAIN
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100;
                            QUERY PLAN                            
------------------------------------------------------------------
 Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
   Foreign Server: wikidata
   Pushdown: enabled
   Remote Select: ?p ?o 
   Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
(5 rows)

EXPLAIN
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100
ORDER BY o DESC;
                               QUERY PLAN                               
------------------------------------------------------------------------
 Sort  (cost=20049.83..20052.33 rows=1000 width=64)
   Sort Key: o DESC
   ->  Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
         Foreign Server: wikidata
         Pushdown: enabled
         Remote Select: ?p ?o 
         Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
         Remote Sort Key:   DESC (?o)
(8 rows)

EXPLAIN
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100
ORDER BY o DESC
LIMIT 3;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Limit  (cost=20012.92..20012.93 rows=3 width=64)
   ->  Sort  (cost=20012.92..20015.42 rows=1000 width=64)
         Sort Key: o DESC
         ->  Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
               Remote Sort Key:   DESC (?o)
(9 rows)

EXPLAIN
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100 OR p IS NOT NULL -- non-pushable condition
ORDER BY o DESC
LIMIT 3;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Limit  (cost=20012.92..20012.93 rows=3 width=64)
   ->  Sort  (cost=20012.92..20015.42 rows=1000 width=64)
         Sort Key: o DESC
         ->  Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
               Filter: ((sparql.isnumeric(o) AND (o > 100)) OR (p IS NOT NULL))
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: not pushable
               Remote Sort Key:   DESC (?o)
(10 rows)

EXPLAIN
SELECT * FROM ft
WHERE
  sparql.isnumeric(o) AND -- pushable condition
  o::text LIKE '%foo%'    -- non-pushable condition
ORDER BY o DESC
LIMIT 3;
                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Limit  (cost=20012.92..20012.93 rows=3 width=64)
   ->  Sort  (cost=20012.92..20015.42 rows=1000 width=64)
         Sort Key: o DESC
         ->  Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
               Filter: ((o)::text ~~ '%foo%'::text)
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: ((ISNUMERIC(?o)))
               Remote Sort Key:   DESC (?o)
(10 rows)

EXPLAIN
SELECT * FROM ft
WHERE
  p::text ILIKE '%foo%' AND -- non-pushable condition
  o::text LIKE '%bar%'      -- non-pushable condition
ORDER BY o DESC
LIMIT 3;
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Limit  (cost=20012.92..20012.93 rows=3 width=64)
   ->  Sort  (cost=20012.92..20015.42 rows=1000 width=64)
         Sort Key: o DESC
         ->  Foreign Scan on ft  (cost=10000.00..20000.00 rows=1000 width=64)
               Filter: (((p)::text ~~* '%foo%'::text) AND ((o)::text ~~ '%bar%'::text))
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: not pushable
               Remote Sort Key:   DESC (?o)
(10 rows)

/* EXPLAIN (VERBOSE) */
EXPLAIN (VERBOSE, COSTS OFF)
SELECT p, o FROM ft;
         QUERY PLAN         
----------------------------
 Foreign Scan on public.ft
   Output: p, o
   Foreign Server: wikidata
   Pushdown: enabled
   Remote Select: ?p ?o 
(5 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100;
                    QUERY PLAN                    
--------------------------------------------------
 Foreign Scan on public.ft
   Output: p, o
   Foreign Server: wikidata
   Pushdown: enabled
   Remote Select: ?p ?o 
   Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
(6 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100
ORDER BY o DESC;
                       QUERY PLAN                       
--------------------------------------------------------
 Sort
   Output: p, o
   Sort Key: ft.o DESC
   ->  Foreign Scan on public.ft
         Output: p, o
         Foreign Server: wikidata
         Pushdown: enabled
         Remote Select: ?p ?o 
         Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
         Remote Sort Key:   DESC (?o)
(10 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT p, o FROM ft
WHERE sparql.isnumeric(o) AND o > 100
ORDER BY o DESC
LIMIT 3;
                          QUERY PLAN                          
--------------------------------------------------------------
 Limit
   Output: p, o
   ->  Sort
         Output: p, o
         Sort Key: ft.o DESC
         ->  Foreign Scan on public.ft
               Output: p, o
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
               Remote Sort Key:   DESC (?o)
(12 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT p, o, sparql.sum(o) FROM ft
WHERE sparql.isnumeric(o) AND o > 100 
GROUP BY p, o
ORDER BY o DESC
LIMIT 3;
                             QUERY PLAN                             
--------------------------------------------------------------------
 Limit
   Output: p, o, (sparql.sum(o))
   ->  GroupAggregate
         Output: p, o, sparql.sum(o)
         Group Key: ft.o, ft.p
         ->  Sort
               Output: p, o
               Sort Key: ft.o DESC, ft.p
               ->  Foreign Scan on public.ft
                     Output: p, o
                     Foreign Server: wikidata
                     Pushdown: enabled
                     Remote Select: ?p ?o 
                     Remote Filter: ((ISNUMERIC(?o)) && (?o > 100))
                     Remote Sort Key:   DESC (?o)  ASC (?p)
(15 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT p, o, sparql.sum(o) FROM ft
WHERE sparql.isnumeric(o) AND o > 100 OR p IS NOT NULL -- non-pushable condition
GROUP BY p, o
ORDER BY o DESC
LIMIT 3;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Limit
   Output: p, o, (sparql.sum(o))
   ->  GroupAggregate
         Output: p, o, sparql.sum(o)
         Group Key: ft.o, ft.p
         ->  Sort
               Output: p, o
               Sort Key: ft.o DESC, ft.p
               ->  Foreign Scan on public.ft
                     Output: p, o
                     Filter: ((sparql.isnumeric(ft.o) AND (ft.o > 100)) OR (ft.p IS NOT NULL))
                     Foreign Server: wikidata
                     Pushdown: enabled
                     Remote Select: ?p ?o 
                     Remote Filter: not pushable
                     Remote Sort Key:   DESC (?o)  ASC (?p)
(16 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft
WHERE
  sparql.isnumeric(o) AND -- pushable condition
  o::text LIKE '%foo%'    -- non-pushable condition
ORDER BY o DESC
LIMIT 3;
                      QUERY PLAN                       
-------------------------------------------------------
 Limit
   Output: p, o
   ->  Sort
         Output: p, o
         Sort Key: ft.o DESC
         ->  Foreign Scan on public.ft
               Output: p, o
               Filter: ((ft.o)::text ~~ '%foo%'::text)
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: ((ISNUMERIC(?o)))
               Remote Sort Key:   DESC (?o)
(13 rows)

EXPLAIN (VERBOSE, COSTS OFF)
SELECT * FROM ft
WHERE
  p::text ILIKE '%foo%' AND -- non-pushable condition
  o::text LIKE '%bar%'      -- non-pushable condition
ORDER BY o DESC
LIMIT 3;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Limit
   Output: p, o
   ->  Sort
         Output: p, o
         Sort Key: ft.o DESC
         ->  Foreign Scan on public.ft
               Output: p, o
               Filter: (((ft.p)::text ~~* '%foo%'::text) AND ((ft.o)::text ~~ '%bar%'::text))
               Foreign Server: wikidata
               Pushdown: enabled
               Remote Select: ?p ?o 
               Remote Filter: not pushable
               Remote Sort Key:   DESC (?o)
(13 rows)

/*
 * A solution modifier that only looks like one, because it sits inside a
 * string literal, must not be mistaken for a real one - and, more to the
 * point, must not hide the real one that follows it. The query below cannot
 * be rewritten, as it carries a LIMIT of its own: it has to be sent as it
 * stands, with the SQL condition left to the executor. A "Remote Filter"
 * here would mean the query was reconstructed and the user's LIMIT 5
 * silently dropped.
 */
CREATE FOREIGN TABLE ft_quoted_limit (
  s rdfnode OPTIONS (variable '?s'),
  o rdfnode OPTIONS (variable '?o')
)
SERVER wikidata OPTIONS (
  sparql 'SELECT * WHERE { ?s ?p ?o . FILTER(?o != " LIMIT ") } LIMIT 5'
);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s, o FROM ft_quoted_limit
WHERE o = 100;
               QUERY PLAN               
----------------------------------------
 Foreign Scan on public.ft_quoted_limit
   Output: s, o
   Filter: (ft_quoted_limit.o = 100)
   Foreign Server: wikidata
   Pushdown: unsupported SPARQL
(5 rows)

/* the same query without the quoted keyword is rewritten as usual */
CREATE FOREIGN TABLE ft_plain (
  s rdfnode OPTIONS (variable '?s'),
  o rdfnode OPTIONS (variable '?o')
)
SERVER wikidata OPTIONS (
  sparql 'SELECT * WHERE { ?s ?p ?o . FILTER(?o != " nothing ") }'
);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s, o FROM ft_plain
WHERE o = 100;
           QUERY PLAN            
---------------------------------
 Foreign Scan on public.ft_plain
   Output: s, o
   Foreign Server: wikidata
   Pushdown: enabled
   Remote Select: ?s ?o 
   Remote Filter: ((?o = 100))
(6 rows)

/*
 * A trailing VALUES clause is part of a well-formed SELECT (SPARQL 1.1 rule
 * [7]), and its data block ends in '}', so the check that nothing follows the
 * graph pattern does not catch it. Rewriting such a query puts the VALUES
 * clause inside the graph pattern and the pushed-down FILTER inside the data
 * block, which the endpoint cannot parse, so the query is left alone and the
 * condition is evaluated locally.
 */
CREATE FOREIGN TABLE ft_values (
  s rdfnode OPTIONS (variable '?s'),
  o rdfnode OPTIONS (variable '?o')
)
SERVER wikidata OPTIONS (
  sparql 'SELECT * WHERE { ?s ?p ?o } VALUES ?p { <http://example.org/p> }'
);
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s, o FROM ft_values
WHERE o = 100;
            QUERY PLAN            
----------------------------------
 Foreign Scan on public.ft_values
   Output: s, o
   Filter: (ft_values.o = 100)
   Foreign Server: wikidata
   Pushdown: unsupported SPARQL
(5 rows)

/* EXPLAIN (VERBOSE) with pushdown disabled */
ALTER FOREIGN TABLE ft OPTIONS (enable_pushdown 'false');
EXPLAIN (VERBOSE, COSTS OFF)
SELECT sparql.str(o), sparql.datatype(o) FROM ft
WHERE sparql.isnumeric(o) AND o > 100
ORDER BY o DESC
LIMIT 3;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Limit
   Output: (sparql.str(o)), (sparql.datatype(o)), o
   ->  Sort
         Output: (sparql.str(o)), (sparql.datatype(o)), o
         Sort Key: ft.o DESC
         ->  Foreign Scan on public.ft
               Output: sparql.str(o), sparql.datatype(o), o
               Filter: (sparql.isnumeric(ft.o) AND (ft.o > 100))
               Foreign Server: wikidata
               Pushdown: disabled
(10 rows)

/*
 * A server's prefix context is looked up by name when a scan on it is
 * planned, and the lookup has to carry the whole name however long it is. A
 * name that does not survive the lookup intact leaves it malformed rather
 * than merely short, so every query against the server fails and none of its
 * prefixes are reachable. This one is long enough to outrun a lookup assembled
 * in a fixed-size buffer.
 */
SELECT repeat('c', 980) AS long_context \gset
SELECT sparql.add_context(:'long_context', 'name longer than a fixed-size buffer');
 add_context 
-------------
 
(1 row)

INSERT INTO sparql.prefixes (prefix, uri, context)
VALUES ('ex', 'http://example.org/', :'long_context');
CREATE SERVER long_context_server
FOREIGN DATA WRAPPER rdf_fdw
OPTIONS (
  endpoint 'https://example.org/sparql',
  prefix_context :'long_context'
);
CREATE FOREIGN TABLE long_context_ft (
  s rdfnode OPTIONS (variable '?s')
)
SERVER long_context_server OPTIONS (sparql 'SELECT ?s {?s ?p ?o}');
/* planning this reads the context, and must produce a plan rather than fail */
EXPLAIN (VERBOSE, COSTS OFF)
SELECT s FROM long_context_ft;
               QUERY PLAN               
----------------------------------------
 Foreign Scan on public.long_context_ft
   Output: s
   Foreign Server: long_context_server
   Pushdown: enabled
   Remote Select: ?s 
(5 rows)

DROP SERVER long_context_server CASCADE;
NOTICE:  drop cascades to foreign table long_context_ft
DELETE FROM sparql.prefixes WHERE context = :'long_context';
DELETE FROM sparql.prefix_contexts WHERE context = :'long_context';
DROP SERVER wikidata CASCADE;
NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to foreign table ft
drop cascades to foreign table ft_quoted_limit
drop cascades to foreign table ft_plain
drop cascades to foreign table ft_values