gis Database

Parameters

Name	Type	Mode
p_central_host	text	IN
p_central_port	smallint	IN
p_central_database	text	IN
p_central_username	text	IN
p_central_password	text	IN

Definition

DECLARE
    sqltemplate text;
BEGIN
    -- Create extension
    CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create server
    DROP SERVER IF EXISTS central_server CASCADE;
    sqltemplate = '
    CREATE SERVER central_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host ''%1$s'',
        port ''%2$s'',
        dbname ''%3$s'',
        connect_timeout ''5''
    );
    ';
    EXECUTE format(sqltemplate,
        p_central_host,
        p_central_port,
        p_central_database
    );

-- User mapping
    sqltemplate = '
    CREATE USER MAPPING FOR CURRENT_USER
    SERVER central_server
    OPTIONS (
        user ''%1$s'',
        password ''%2$s''
    )
    ';
    EXECUTE format(sqltemplate,
        p_central_username,
        p_central_password
    );

-- Create local schemas
    CREATE SCHEMA IF NOT EXISTS central_lizsync;

-- lizsync
    IMPORT FOREIGN SCHEMA lizsync
    FROM SERVER central_server
    INTO central_lizsync;

-- Manually create modified conflicts table
    -- with no id column to avoid issues
    -- https://stackoverflow.com/a/53361066/13220524
    CREATE FOREIGN TABLE central_lizsync.conflicts_bis(
        object_table text,
        object_uid uuid,
        clone_id uuid,
        central_event_id bigint,
        central_event_timestamp timestamp with time zone,
        central_sql text,
        clone_sql text,
        rejected text,
        rule_applied text
     )
    SERVER central_server
    OPTIONS (
        schema_name 'lizsync',
        table_name 'conflicts'
    );

RETURN True;
END;

create_central_server_fdw

Description

Parameters

Definition