-- ===================================================================
-- create FDW objects
-- ===================================================================
CREATE EXTENSION postgres_fdw;
CREATE SERVER testserver1 FOREIGN DATA WRAPPER postgres_fdw;
CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (dbname 'contrib_regression');
CREATE USER MAPPING FOR public SERVER testserver1
	OPTIONS (user 'value', password 'value');
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback;
-- ===================================================================
-- create objects used through FDW loopback server
-- ===================================================================
CREATE TYPE user_enum AS ENUM ('foo', 'bar', 'buz');
CREATE SCHEMA "S 1";
CREATE TABLE "S 1"."T 1" (
	"C 1" int NOT NULL,
	c2 int NOT NULL,
	c3 text,
	c4 timestamptz,
	c5 timestamp,
	c6 varchar(10),
	c7 char(10),
	c8 user_enum,
	CONSTRAINT t1_pkey PRIMARY KEY ("C 1")
);
CREATE TABLE "S 1"."T 2" (
	c1 int NOT NULL,
	c2 text,
	CONSTRAINT t2_pkey PRIMARY KEY (c1)
);
INSERT INTO "S 1"."T 1"
	SELECT id,
	       id % 10,
	       to_char(id, 'FM00000'),
	       '1970-01-01'::timestamptz + ((id % 100) || ' days')::interval,
	       '1970-01-01'::timestamp + ((id % 100) || ' days')::interval,
	       id % 10,
	       id % 10,
	       'foo'::user_enum
	FROM generate_series(1, 1000) id;
INSERT INTO "S 1"."T 2"
	SELECT id,
	       'AAA' || to_char(id, 'FM000')
	FROM generate_series(1, 100) id;
ANALYZE "S 1"."T 1";
ANALYZE "S 1"."T 2";
-- ===================================================================
-- create foreign tables
-- ===================================================================
CREATE FOREIGN TABLE ft1 (
	c0 int,
	c1 int NOT NULL,
	c2 int NOT NULL,
	c3 text,
	c4 timestamptz,
	c5 timestamp,
	c6 varchar(10),
	c7 char(10),
	c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft1 DROP COLUMN c0;
CREATE FOREIGN TABLE ft2 (
	c0 int,
	c1 int NOT NULL,
	c2 int NOT NULL,
	c3 text,
	c4 timestamptz,
	c5 timestamp,
	c6 varchar(10),
	c7 char(10),
	c8 user_enum
) SERVER loopback;
ALTER FOREIGN TABLE ft2 DROP COLUMN c0;
-- ===================================================================
-- tests for validator
-- ===================================================================
-- requiressl, krbsrvname and gsslib are omitted because they depend on
-- configure options
ALTER SERVER testserver1 OPTIONS (
	use_remote_estimate 'false',
	fdw_startup_cost '123.456',
	fdw_tuple_cost '0.123',
	service 'value',
	connect_timeout 'value',
	dbname 'value',
	host 'value',
	hostaddr 'value',
	port 'value',
	--client_encoding 'value',
	application_name 'value',
	--fallback_application_name 'value',
	keepalives 'value',
	keepalives_idle 'value',
	keepalives_interval 'value',
	-- requiressl 'value',
	sslcompression 'value',
	sslmode 'value',
	sslcert 'value',
	sslkey 'value',
	sslrootcert 'value',
	sslcrl 'value'
	--requirepeer 'value',
	-- krbsrvname 'value',
	-- gsslib 'value',
	--replication 'value'
);
ALTER USER MAPPING FOR public SERVER testserver1
	OPTIONS (DROP user, DROP password);
ALTER FOREIGN TABLE ft1 OPTIONS (schema_name 'S 1', table_name 'T 1');
ALTER FOREIGN TABLE ft2 OPTIONS (schema_name 'S 1', table_name 'T 1');
ALTER FOREIGN TABLE ft1 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
ALTER FOREIGN TABLE ft2 ALTER COLUMN c1 OPTIONS (column_name 'C 1');
\det+
                             List of foreign tables
 Schema | Table |  Server  |              FDW Options              | Description 
--------+-------+----------+---------------------------------------+-------------
 public | ft1   | loopback | (schema_name 'S 1', table_name 'T 1') | 
 public | ft2   | loopback | (schema_name 'S 1', table_name 'T 1') | 
(2 rows)

-- Now we should be able to run ANALYZE.
-- To exercise multiple code paths, we use local stats on ft1
-- and remote-estimate mode on ft2.
ANALYZE ft1;
ALTER FOREIGN TABLE ft2 OPTIONS (use_remote_estimate 'true');
-- ===================================================================
-- simple queries
-- ===================================================================
-- single table, with/without alias
EXPLAIN (COSTS false) SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
           QUERY PLAN            
---------------------------------
 Limit
   ->  Sort
         Sort Key: c3, c1
         ->  Foreign Scan on ft1
(4 rows)

SELECT * FROM ft1 ORDER BY c3, c1 OFFSET 100 LIMIT 10;
 c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
-----+----+-------+------------------------------+--------------------------+----+------------+-----
 101 |  1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
 102 |  2 | 00102 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2  | 2          | foo
 103 |  3 | 00103 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo
 104 |  4 | 00104 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4  | 4          | foo
 105 |  5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5  | 5          | foo
 106 |  6 | 00106 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo
 107 |  7 | 00107 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo
 108 |  8 | 00108 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8  | 8          | foo
 109 |  9 | 00109 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9  | 9          | foo
 110 |  0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0  | 0          | foo
(10 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   ->  Sort
         Output: c1, c2, c3, c4, c5, c6, c7, c8
         Sort Key: t1.c3, t1.c1
         ->  Foreign Scan on public.ft1 t1
               Output: c1, c2, c3, c4, c5, c6, c7, c8
               Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(8 rows)

SELECT * FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
-----+----+-------+------------------------------+--------------------------+----+------------+-----
 101 |  1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
 102 |  2 | 00102 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2  | 2          | foo
 103 |  3 | 00103 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo
 104 |  4 | 00104 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4  | 4          | foo
 105 |  5 | 00105 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5  | 5          | foo
 106 |  6 | 00106 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo
 107 |  7 | 00107 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo
 108 |  8 | 00108 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8  | 8          | foo
 109 |  9 | 00109 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9  | 9          | foo
 110 |  0 | 00110 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0  | 0          | foo
(10 rows)

-- whole-row reference
EXPLAIN (VERBOSE, COSTS false) SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Limit
   Output: t1.*, c3, c1
   ->  Sort
         Output: t1.*, c3, c1
         Sort Key: t1.c3, t1.c1
         ->  Foreign Scan on public.ft1 t1
               Output: t1.*, c3, c1
               Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(8 rows)

SELECT t1 FROM ft1 t1 ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
                                             t1                                             
--------------------------------------------------------------------------------------------
 (101,1,00101,"Fri Jan 02 00:00:00 1970 PST","Fri Jan 02 00:00:00 1970",1,"1         ",foo)
 (102,2,00102,"Sat Jan 03 00:00:00 1970 PST","Sat Jan 03 00:00:00 1970",2,"2         ",foo)
 (103,3,00103,"Sun Jan 04 00:00:00 1970 PST","Sun Jan 04 00:00:00 1970",3,"3         ",foo)
 (104,4,00104,"Mon Jan 05 00:00:00 1970 PST","Mon Jan 05 00:00:00 1970",4,"4         ",foo)
 (105,5,00105,"Tue Jan 06 00:00:00 1970 PST","Tue Jan 06 00:00:00 1970",5,"5         ",foo)
 (106,6,00106,"Wed Jan 07 00:00:00 1970 PST","Wed Jan 07 00:00:00 1970",6,"6         ",foo)
 (107,7,00107,"Thu Jan 08 00:00:00 1970 PST","Thu Jan 08 00:00:00 1970",7,"7         ",foo)
 (108,8,00108,"Fri Jan 09 00:00:00 1970 PST","Fri Jan 09 00:00:00 1970",8,"8         ",foo)
 (109,9,00109,"Sat Jan 10 00:00:00 1970 PST","Sat Jan 10 00:00:00 1970",9,"9         ",foo)
 (110,0,00110,"Sun Jan 11 00:00:00 1970 PST","Sun Jan 11 00:00:00 1970",0,"0         ",foo)
(10 rows)

-- empty result
SELECT * FROM ft1 WHERE false;
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 
----+----+----+----+----+----+----+----
(0 rows)

-- with WHERE clause
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c7 >= '1'::bpchar)) AND (("C 1" = 101)) AND ((c6 = '1'::text))
(3 rows)

SELECT * FROM ft1 t1 WHERE t1.c1 = 101 AND t1.c6 = '1' AND t1.c7 >= '1';
 c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
-----+----+-------+------------------------------+--------------------------+----+------------+-----
 101 |  1 | 00101 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
(1 row)

-- aggregate
SELECT COUNT(*) FROM ft1 t1;
 count 
-------
  1000
(1 row)

-- join two tables
SELECT t1.c1 FROM ft1 t1 JOIN ft2 t2 ON (t1.c1 = t2.c1) ORDER BY t1.c3, t1.c1 OFFSET 100 LIMIT 10;
 c1  
-----
 101
 102
 103
 104
 105
 106
 107
 108
 109
 110
(10 rows)

-- subquery
SELECT * FROM ft1 t1 WHERE t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 <= 10) ORDER BY c1;
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
  2 |  2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2  | 2          | foo
  3 |  3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo
  4 |  4 | 00004 | Mon Jan 05 00:00:00 1970 PST | Mon Jan 05 00:00:00 1970 | 4  | 4          | foo
  5 |  5 | 00005 | Tue Jan 06 00:00:00 1970 PST | Tue Jan 06 00:00:00 1970 | 5  | 5          | foo
  6 |  6 | 00006 | Wed Jan 07 00:00:00 1970 PST | Wed Jan 07 00:00:00 1970 | 6  | 6          | foo
  7 |  7 | 00007 | Thu Jan 08 00:00:00 1970 PST | Thu Jan 08 00:00:00 1970 | 7  | 7          | foo
  8 |  8 | 00008 | Fri Jan 09 00:00:00 1970 PST | Fri Jan 09 00:00:00 1970 | 8  | 8          | foo
  9 |  9 | 00009 | Sat Jan 10 00:00:00 1970 PST | Sat Jan 10 00:00:00 1970 | 9  | 9          | foo
 10 |  0 | 00010 | Sun Jan 11 00:00:00 1970 PST | Sun Jan 11 00:00:00 1970 | 0  | 0          | foo
(10 rows)

-- subquery+MAX
SELECT * FROM ft1 t1 WHERE t1.c3 = (SELECT MAX(c3) FROM ft2 t2) ORDER BY c1;
  c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
------+----+-------+------------------------------+--------------------------+----+------------+-----
 1000 |  0 | 01000 | Thu Jan 01 00:00:00 1970 PST | Thu Jan 01 00:00:00 1970 | 0  | 0          | foo
(1 row)

-- used in CTE
WITH t1 AS (SELECT * FROM ft1 WHERE c1 <= 10) SELECT t2.c1, t2.c2, t2.c3, t2.c4 FROM t1, ft2 t2 WHERE t1.c1 = t2.c1 ORDER BY t1.c1;
 c1 | c2 |  c3   |              c4              
----+----+-------+------------------------------
  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 PST
  2 |  2 | 00002 | Sat Jan 03 00:00:00 1970 PST
  3 |  3 | 00003 | Sun Jan 04 00:00:00 1970 PST
  4 |  4 | 00004 | Mon Jan 05 00:00:00 1970 PST
  5 |  5 | 00005 | Tue Jan 06 00:00:00 1970 PST
  6 |  6 | 00006 | Wed Jan 07 00:00:00 1970 PST
  7 |  7 | 00007 | Thu Jan 08 00:00:00 1970 PST
  8 |  8 | 00008 | Fri Jan 09 00:00:00 1970 PST
  9 |  9 | 00009 | Sat Jan 10 00:00:00 1970 PST
 10 |  0 | 00010 | Sun Jan 11 00:00:00 1970 PST
(10 rows)

-- fixed values
SELECT 'fixed', NULL FROM ft1 t1 WHERE c1 = 1;
 ?column? | ?column? 
----------+----------
 fixed    | 
(1 row)

-- user-defined operator/function
CREATE FUNCTION postgres_fdw_abs(int) RETURNS int AS $$
BEGIN
RETURN abs($1);
END
$$ LANGUAGE plpgsql IMMUTABLE;
CREATE OPERATOR === (
    LEFTARG = int,
    RIGHTARG = int,
    PROCEDURE = int4eq,
    COMMUTATOR = ===,
    NEGATOR = !==
);
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = postgres_fdw_abs(t1.c2);
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c1 = postgres_fdw_abs(t1.c2))
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 === t1.c2;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c1 === t1.c2)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = abs(t1.c2);
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = abs(c2)))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = t1.c2;
                                          QUERY PLAN                                          
----------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = c2))
(3 rows)

-- ===================================================================
-- WHERE with remotely-executable conditions
-- ===================================================================
EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 1;         -- Var, OpExpr(b), Const
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 100)) AND ((c2 = 0))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NULL;        -- NullTest
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NULL))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL;    -- NullTest
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((round(abs("C 1"), 0) = 1::numeric))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = -c1;          -- OpExpr(l)
                                             QUERY PLAN                                              
-----------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = (- "C 1")))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE 1 = c1!;           -- OpExpr(r)
                                                QUERY PLAN                                                
----------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((1::numeric = ("C 1" !)))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
                                                                 QUERY PLAN                                                                 
--------------------------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((("C 1" IS NOT NULL) IS DISTINCT FROM ("C 1" IS NOT NULL)))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = ANY(ARRAY[c2, 1, c1 + 0]); -- ScalarArrayOpExpr
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = ANY (ARRAY[c2, 1, ("C 1" + 0)])))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c1 = (ARRAY[c1,c2,3])[1]; -- ArrayRef
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = ((ARRAY["C 1", c2, 3])[1])))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c6 = E'foo''s\\bar';  -- check special chars
                                                 QUERY PLAN                                                  
-------------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c6 = E'foo''s\\bar'::text))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) SELECT * FROM ft1 t1 WHERE c8 = 'foo';  -- can't be sent to remote
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = 'foo'::user_enum)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
(4 rows)

-- ===================================================================
-- parameterized queries
-- ===================================================================
-- simple join
PREPARE st1(int, int) AS SELECT t1.c3, t2.c3 FROM ft1 t1, ft2 t2 WHERE t1.c1 = $1 AND t2.c1 = $2;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st1(1, 2);
                                                  QUERY PLAN                                                  
--------------------------------------------------------------------------------------------------------------
 Nested Loop
   Output: t1.c3, t2.c3
   ->  Foreign Scan on public.ft1 t1
         Output: t1.c3
         Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 1))
   ->  Foreign Scan on public.ft2 t2
         Output: t2.c3
         Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" = 2))
(8 rows)

EXECUTE st1(1, 1);
  c3   |  c3   
-------+-------
 00001 | 00001
(1 row)

EXECUTE st1(101, 101);
  c3   |  c3   
-------+-------
 00101 | 00101
(1 row)

-- subquery using stable function (can't be sent to remote)
PREPARE st2(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c4) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st2(10, 20);
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Sort
   Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
   Sort Key: t1.c1
   ->  Nested Loop Semi Join
         Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
         Join Filter: (t1.c3 = t2.c3)
         ->  Foreign Scan on public.ft1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
               Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20))
         ->  Materialize
               Output: t2.c3
               ->  Foreign Scan on public.ft2 t2
                     Output: t2.c3
                     Filter: (date(t2.c4) = '01-17-1970'::date)
                     Remote SQL: SELECT NULL, NULL, c3, c4, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10))
(15 rows)

EXECUTE st2(10, 20);
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
 16 |  6 | 00016 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6  | 6          | foo
(1 row)

EXECUTE st2(101, 121);
 c1  | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
-----+----+-------+------------------------------+--------------------------+----+------------+-----
 116 |  6 | 00116 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6  | 6          | foo
(1 row)

-- subquery using immutable function (can be sent to remote)
PREPARE st3(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 < $2 AND t1.c3 IN (SELECT c3 FROM ft2 t2 WHERE c1 > $1 AND date(c5) = '1970-01-17'::date) ORDER BY c1;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st3(10, 20);
                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort
   Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
   Sort Key: t1.c1
   ->  Nested Loop Semi Join
         Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
         Join Filter: (t1.c3 = t2.c3)
         ->  Foreign Scan on public.ft1 t1
               Output: t1.c1, t1.c2, t1.c3, t1.c4, t1.c5, t1.c6, t1.c7, t1.c8
               Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 20))
         ->  Materialize
               Output: t2.c3
               ->  Foreign Scan on public.ft2 t2
                     Output: t2.c3
                     Remote SQL: SELECT NULL, NULL, c3, NULL, NULL, NULL, NULL, NULL FROM "S 1"."T 1" WHERE (("C 1" > 10)) AND ((date(c5) = '1970-01-17'::date))
(14 rows)

EXECUTE st3(10, 20);
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
 16 |  6 | 00016 | Sat Jan 17 00:00:00 1970 PST | Sat Jan 17 00:00:00 1970 | 6  | 6          | foo
(1 row)

EXECUTE st3(20, 30);
 c1 | c2 | c3 | c4 | c5 | c6 | c7 | c8 
----+----+----+----+----+----+----+----
(0 rows)

-- custom plan should be chosen initially
PREPARE st4(int) AS SELECT * FROM ft1 t1 WHERE t1.c1 = $1;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(3 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(3 rows)

-- once we try it enough times, should switch to generic plan
EXPLAIN (VERBOSE, COSTS false) EXECUTE st4(1);
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $1::integer))
(3 rows)

-- value of $1 should not be sent to remote
PREPARE st5(user_enum,int) AS SELECT * FROM ft1 t1 WHERE c8 = $1 and c1 = $2;
EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = 'foo'::user_enum)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(4 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = 'foo'::user_enum)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(4 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = 'foo'::user_enum)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(4 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = 'foo'::user_enum)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(4 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = 'foo'::user_enum)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 1))
(4 rows)

EXPLAIN (VERBOSE, COSTS false) EXECUTE st5('foo', 1);
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Foreign Scan on public.ft1 t1
   Output: c1, c2, c3, c4, c5, c6, c7, c8
   Filter: (t1.c8 = $1)
   Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = $2::integer))
(4 rows)

EXECUTE st5('foo', 1);
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
(1 row)

-- cleanup
DEALLOCATE st1;
DEALLOCATE st2;
DEALLOCATE st3;
DEALLOCATE st4;
DEALLOCATE st5;
-- ===================================================================
-- used in pl/pgsql function
-- ===================================================================
CREATE OR REPLACE FUNCTION f_test(p_c1 int) RETURNS int AS $$
DECLARE
	v_c1 int;
BEGIN
    SELECT c1 INTO v_c1 FROM ft1 WHERE c1 = p_c1 LIMIT 1;
    PERFORM c1 FROM ft1 WHERE c1 = p_c1 AND p_c1 = v_c1 LIMIT 1;
    RETURN v_c1;
END;
$$ LANGUAGE plpgsql;
SELECT f_test(100);
 f_test 
--------
    100
(1 row)

DROP FUNCTION f_test(int);
-- ===================================================================
-- conversion error
-- ===================================================================
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE int;
SELECT * FROM ft1 WHERE c1 = 1;  -- ERROR
ERROR:  invalid input syntax for integer: "foo"
CONTEXT:  column "c8" of foreign table "ft1"
ALTER FOREIGN TABLE ft1 ALTER COLUMN c8 TYPE user_enum;
-- ===================================================================
-- subtransaction
--  + local/remote error doesn't break cursor
-- ===================================================================
BEGIN;
DECLARE c CURSOR FOR SELECT * FROM ft1 ORDER BY c1;
FETCH c;
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
(1 row)

SAVEPOINT s;
ERROR OUT;          -- ERROR
ERROR:  syntax error at or near "ERROR"
LINE 1: ERROR OUT;
        ^
ROLLBACK TO s;
FETCH c;
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
  2 |  2 | 00002 | Sat Jan 03 00:00:00 1970 PST | Sat Jan 03 00:00:00 1970 | 2  | 2          | foo
(1 row)

SAVEPOINT s;
SELECT * FROM ft1 WHERE 1 / (c1 - 1) > 0;  -- ERROR
ERROR:  division by zero
CONTEXT:  Remote SQL command: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (((1 / ("C 1" - 1)) > 0))
ROLLBACK TO s;
FETCH c;
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
  3 |  3 | 00003 | Sun Jan 04 00:00:00 1970 PST | Sun Jan 04 00:00:00 1970 | 3  | 3          | foo
(1 row)

SELECT * FROM ft1 ORDER BY c1 LIMIT 1;
 c1 | c2 |  c3   |              c4              |            c5            | c6 |     c7     | c8  
----+----+-------+------------------------------+--------------------------+----+------------+-----
  1 |  1 | 00001 | Fri Jan 02 00:00:00 1970 PST | Fri Jan 02 00:00:00 1970 | 1  | 1          | foo
(1 row)

COMMIT;
-- ===================================================================
-- test handling of collations
-- ===================================================================
create table loct3 (f1 text collate "C", f2 text);
create foreign table ft3 (f1 text collate "C", f2 text)
  server loopback options (table_name 'loct3');
-- can be sent to remote
explain (verbose, costs off) select * from ft3 where f1 = 'foo';
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f1 = 'foo'::text))
(3 rows)

explain (verbose, costs off) select * from ft3 where f1 COLLATE "C" = 'foo';
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f1 = 'foo'::text))
(3 rows)

explain (verbose, costs off) select * from ft3 where f2 = 'foo';
                                QUERY PLAN                                
--------------------------------------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Remote SQL: SELECT f1, f2 FROM public.loct3 WHERE ((f2 = 'foo'::text))
(3 rows)

-- can't be sent to remote
explain (verbose, costs off) select * from ft3 where f1 COLLATE "POSIX" = 'foo';
                  QUERY PLAN                   
-----------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Filter: ((ft3.f1)::text = 'foo'::text)
   Remote SQL: SELECT f1, f2 FROM public.loct3
(4 rows)

explain (verbose, costs off) select * from ft3 where f1 = 'foo' COLLATE "C";
                  QUERY PLAN                   
-----------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Filter: (ft3.f1 = 'foo'::text COLLATE "C")
   Remote SQL: SELECT f1, f2 FROM public.loct3
(4 rows)

explain (verbose, costs off) select * from ft3 where f2 COLLATE "C" = 'foo';
                  QUERY PLAN                   
-----------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Filter: ((ft3.f2)::text = 'foo'::text)
   Remote SQL: SELECT f1, f2 FROM public.loct3
(4 rows)

explain (verbose, costs off) select * from ft3 where f2 = 'foo' COLLATE "C";
                  QUERY PLAN                   
-----------------------------------------------
 Foreign Scan on public.ft3
   Output: f1, f2
   Filter: (ft3.f2 = 'foo'::text COLLATE "C")
   Remote SQL: SELECT f1, f2 FROM public.loct3
(4 rows)