-- CREATE DATABASES
CREATE DATABASE local;
CREATE DATABASE remote;

-- Set password to postgres
ALTER USER postgres PASSWORD 'postgres';

-- -- -- -- -- -- -- -- -- -- 
-- REMOTE DB
-- -- -- -- -- -- -- -- -- -- 
\c remote
-- Create schema for destination tables
CREATE SCHEMA data;
-- Create table with setting
CREATE TABLE setting AS SELECT 'max_value'::text AS name, 5::integer AS value; 

-- -- -- -- -- -- -- -- -- -- 
-- LOCAL DB
-- -- -- -- -- -- -- -- -- -- 
\c local
-- Create extensions
CREATE EXTENSION IF NOT EXISTS postgres_fdw ;
CREATE EXTENSION IF NOT EXISTS dblink ;

-- Create server, user mapping and foreign table for setting
DROP SERVER IF EXISTS test_server CASCADE;
CREATE SERVER test_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'remote', port '5432');
CREATE USER MAPPING FOR postgres SERVER test_server OPTIONS (user 'postgres', password 'postgres');
CREATE FOREIGN TABLE IF NOT EXISTS remote_setting (name text, value integer) SERVER test_server OPTIONS (schema_name 'public', table_name 'setting');

-- Create source table
CREATE TABLE input (value integer);
INSERT INTO input VALUES (1),(5),(10),(1),(9),(4);

-- Create function for copying
CREATE OR REPLACE FUNCTION copy_table(in_table character varying) RETURNS bigint
    LANGUAGE plpgsql AS $$
DECLARE
    rows_count BIGINT;
BEGIN
    -- reconnect dblink connection
    IF 'test_server_link' = ANY (dblink_get_connections()) THEN
        PERFORM dblink_disconnect('test_server_link');
    END IF;
    EXECUTE format('SELECT dblink_connect(''test_server_link'', ''test_server'')');

    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
    -- PROBLEMATIC LINE
    -- (dummy) load setting from foreign table
    -- if next line is performed copying does not work on first calling 
    PERFORM * FROM remote_setting;
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 

    -- Creating destination table in remote database
    PERFORM * FROM dblink('test_server_link', 'CREATE TABLE IF NOT EXISTS data.output (value integer)') AS x(x TEXT);


    -- Create local schema and import remote schema
    EXECUTE 'DROP SCHEMA IF EXISTS fs CASCADE';
    EXECUTE 'CREATE SCHEMA fs';
    EXECUTE 'IMPORT FOREIGN SCHEMA data FROM SERVER test_server INTO fs';


    -- push data to remote table
    EXECUTE format('INSERT INTO fs.output TABLE %s', in_table);
    GET DIAGNOSTICS rows_count = ROW_COUNT;

    -- disconnect session
    PERFORM dblink_disconnect('test_server_link');
    RETURN rows_count;

    EXCEPTION WHEN others THEN
        IF 'test_server_link' = ANY (dblink_get_connections()) THEN
            PERFORM dblink_disconnect('test_server_link');
        END IF;
        RAISE WARNING '%', SQLERRM;
        RETURN -1;
END;
$$;

ALTER FUNCTION copy_table(character varying) OWNER TO postgres;

-- Calling function for copying 'ipnut' table
SELECT copy_table('input');
