link_pg_server.sql v0 - aka SERVICE keyword like in SPARQL

/* =================================================
*
* link_pg_server(...) (aka SERVICE keyword like in SPARQL)
*
* Linking open and remote (PostgreSQL) read-only
* databases easily! Uses postgres_fdw the SQL/MED
* implementation (Foreign Data Wrapper).
* Tested with PostgreSQL 11.
*
* Goal is to achieve aka "SQL Endpoints" for linked data
* similar to SPARQL.
*
* Caveats of this implementation:
* - Must be superuser to create extension postgres_fdw.
* - Joins are slow with large datasets.
*
* Credits: https://robots.thoughtbot.com/postgres-foreign-data-wrapper
*
* Below the original script:

-- Step 1: Preparation
CREATE EXTENSION IF NOT EXISTS postgres_fdw;  -- Must be superuser
DROP SERVER IF EXISTS gis_db_server CASCADE;
CREATE SERVER gis_db_server  -- prepare remote DB server
 FOREIGN DATA WRAPPER postgres_fdw
 OPTIONS (host '152.96.80.44', port '8080', dbname 'gis_db');
CREATE USER MAPPING FOR CURRENT_USER  -- Connect
 SERVER gis_db_server
 OPTIONS (user 'readonly', password 'Vainyils9');

-- Step 2: Import Schema
DROP SCHEMA IF EXISTS gis_db_public CASCADE;
CREATE SCHEMA gis_db_public;
CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION IF NOT EXISTS hstore;  -- needed by gis_db schema
IMPORT FOREIGN SCHEMA public
 FROM SERVER gis_db_server
 INTO gis_db_public;

-- Step 3: Go!
SELECT COUNT(*) FROM gis_db_public.osm_point;
*/

drop function if exists link_pg_server(text, text, text, text, text, text, text);
create or replace function link_pg_server(_host text, _port text, _dbname text, _user text, _pw text, _server text, _schema text default 'public')
returns text as $$
declare
 link_server_schema text := concat(_server, '_', _schema);
begin
 create extension if not exists postgres_fdw;
 -- Server:
 execute format('drop server if exists %I cascade', _server);
 execute format('create server %I
 foreign data wrapper postgres_fdw
 options (host %L, port %L, dbname %L)', _server, _host, _port, _dbname);
 execute format('create user mapping for current_user
 server %I
 options (user %L, password %L)', _server, _user, _pw);
 -- Schema:
 execute format('drop schema if exists %I cascade', link_server_schema);
 execute format('create schema %I', link_server_schema);
 execute format('import foreign schema %I
 from server %I
 into %I', _schema, _server, link_server_schema);
 -- Done:
 raise info 'server "%" created linking to database "%" and schema "%", now available as schema "%".', _server, _dbname, _schema, link_server_schema;
 return link_server_schema;
end;
$$ language plpgsql;


-- Link to remote DB (default schema=public):
create extension if not exists postgis; -- Needed by gis_db
create extension if not exists hstore; -- Needed by gis_db
--select link_pg_server('152.96.80.44', '8080', 'gis_db', 'readonly', 'Vainyils9', 'gis_db_server');
select link_pg_server('152.96.80.41', '8080', 'gis_db', 'readonly', 'Vainyils9', 'gis_db_server');

-- Test/Demo "count":
select count(*) from gis_db_server_public.osm_point;
-- "43460156"

-- Cleanup:
--drop server if exists gis_db_server cascade;
--drop schema if exists gis_db_server_public cascade;
--drop function if exists link_pg_server(text, text, text, text, text, text, text);

-- Test/Demo "filter":
select point.*
from gis_db_server_public.osm_point as point
where osm_id in (4066310784, 1398864548);

-- Test/Demo "Join Tables":
/*
-- Approach 1: Slow since it downloads all remote data in order to do the join.
with tmp (osm_id, name) as (
 values (4066310784, 'Tierpark Chur'), (1398864548, 'Tierpark Aletsch')
)
select point.*
from gis_db_server_public.osm_point as point
left join tmp on tmp.osm_id=point.osm_id;
*/
-- Approach 2 and solution: Send a list of ids to fetch over to
-- the remote as an array. Feasible for thousands of ids, but not millions.
-- Planner doesn't have any kind of join plan that would do that automatically.
with tmp (osm_id, name) as (
 values (4066310784, 'Tierpark Chur'), (1398864548, 'Tierpark Aletsch')
)
select point.*
from gis_db_server_public.osm_point as point
-- Replacing "left join tmp on tmp.osm_id=point.osm_id" with "ANY":
where point.osm_id = any(array(select osm_id from tmp));

--