ZsqzaTri

1-----------------------------
2-- 
3-- Credits: https://robots.thoughtbot.com/postgres-foreign-data-wrapper
4-----------------------------
5
6-- Step 1: Preparation
7CREATE EXTENSION IF NOT EXISTS postgres_fdw;
8
9-- Connect to remote DB server:
10DROP SERVER IF EXISTS gis_db_server CASCADE;
11CREATE SERVER gis_db_server
12  FOREIGN DATA WRAPPER postgres_fdw
13  OPTIONS (host 'xxx', port 'xxx', dbname 'xxx');
14CREATE USER MAPPING FOR CURRENT_USER
15  SERVER gis_db_server
16  OPTIONS (user 'xxx', password 'xxx');
17
18-- Step 2: Import Schema
19DROP SCHEMA IF EXISTS gis_db_public CASCADE;
20CREATE SCHEMA gis_db_public;
21CREATE EXTENSION IF NOT EXISTS postgis;
22CREATE EXTENSION IF NOT EXISTS hstore; -- needed by gis_db schema
23IMPORT FOREIGN SCHEMA public
24  FROM SERVER gis_db_server
25  INTO gis_db_public;
26
27-- Step 3: Go!
28SELECT COUNT(*) FROM gis_db_public.osm_point;
29
30-- Slow - or even never returning! No operator pushdown ??? 
31-- (no "join op nor tmp table sent to linked db) 
32-- See EXPLAIN:  https://explain.depesz.com/s/PStr 
33with tmp(osm_id, name) as (
34 values (4066310784, 'Tierpark Chur'), (1398864548, 'Tierpark Aletsch')
35)
36select point.* 
37from gis_db_public.osm_point as point
38join tmp on tmp.osm_id=point.osm_id;