CREATE EXTENSION postgres_fdw;

DROP server loopback1 cascade;
DROP server loopback2 cascade;

DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback1 FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE USER MAPPING FOR CURRENT_USER SERVER loopback1;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;

create table lt1 (val int, val2 int);
create table lt2 (val int, val2 int);

create foreign table ft1_s1 (val int, val2 int) server loopback1 options (table_name 'lt1');
create foreign table ft2_s1 (val int, val2 int) server loopback1 options (table_name 'lt2');
create foreign table ft1_s2 (val int, val2 int) server loopback2 options (table_name 'lt1');
create foreign table ft2_s2 (val int, val2 int) server loopback2 options (table_name 'lt2');

-- join between foreign tables on same server - pushable
explain verbose select * from ft1_s1 join ft2_s1 using (val);
-- join between foreign tables on different servers - not pushable
explain verbose select * from ft1_s1 join ft2_s2 using (val);
-- join between foreign tables on same server, with non-pushable quals - pushable
explain verbose select * from ft1_s1 a join ft2_s1 b on (a.val * random() = b.val);
-- outer join between foreign tables on same server, with non-pushable quals -
-- not pushable
explain verbose select * from ft1_s1 a left join ft2_s1 b on (a.val * random() = b.val);
-- outer join between foreign tables on same server, with pushable join
-- quals, but non-pushable where condition
explain verbose select * from ft1_s1 a join ft2_s1 b using (val) where a.val * random() = b.val;
-- aggregation on top of join to check setref
explain verbose select sum(a.val), avg(b.val) from ft1_s1 a join ft2_s1 b using (val) group by a.val2;
