postgres创建外部表

CREATE EXTENSION IF NOT EXISTS "postgres_fdw"; 

CREATE SERVER zy   
FOREIGN DATA WRAPPER postgres_fdw 
OPTIONS (host '127.0.0.1', port '5432', dbname 'postgres');

CREATE USER MAPPING FOR postgres   
SERVER zy 
OPTIONS (user 'postgres', password '123456'); 

CREATE FOREIGN TABLE zy_sys_config (
    id SERIAL8,
    owner text NOT NULL,
    key text NOT NULL,
    value jsonb NOT NULL
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'sys_config'); 

CREATE FOREIGN TABLE zy_enum (
    id SERIAL8 ,
    cate text NOT NULL,
    kind text NOT NULL,
    symbol text NOT NULL,
    comment text,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'enum'); 

CREATE FOREIGN TABLE zy_group (
    id int8 ,
    active int DEFAULT 1,
    kind text NOT NULL,
    sn text,
    name text DEFAULT '',
    remark text DEFAULT '',
    data jsonb DEFAULT '{}',
    parent_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'group'); 

CREATE FOREIGN TABLE zy_place (
    id int8 ,
    active int DEFAULT 1,
    kind text NOT NULL,
    sn text,
    name text DEFAULT '',
    remark text DEFAULT '',
    data jsonb DEFAULT '{}',
    parent_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'place'); 

CREATE FOREIGN TABLE zy_place_group (
    id serial ,
    kind text NOT NULL,
    place_id int8 ,
    group_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'zy_place_group'); 

CREATE FOREIGN TABLE zy_user (
    id int8 ,
    active int DEFAULT 1,
    kind text NOT NULL,
    sn text,
    name text DEFAULT '',
    remark text DEFAULT '',
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'user'); 

CREATE FOREIGN TABLE zy_user_fprint (
    id serial ,
    feature text NOT NULL,
    user_id int8 NOT NULL
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'user_fprint'); 

CREATE FOREIGN TABLE zy_user_group (
    id serial ,
    kind text NOT NULL,
    user_id int8 ,
    group_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'user_group'); 

CREATE FOREIGN TABLE zy_user_place (
    id serial ,
    kind text NOT NULL,
    user_id int8 ,
    place_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'user_place'); 

CREATE FOREIGN TABLE zy_account (
    id int8 ,
    active int DEFAULT 1,
    username text NOT NULL,
    password text NOT NULL DEFAULT '@Fsy123456',
    role text NOT NULL,
    avatar text,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'account'); 

CREATE FOREIGN TABLE zy_account_privilege (
    id int8 NOT NULL ,
    oid int8 NOT NULL
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'account_privilege'); 

CREATE FOREIGN TABLE zy_account_src (
    id int8 NOT NULL ,
    src text NOT NULL
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'account_src'); 

CREATE FOREIGN TABLE zy_asset (
    id int8 ,
    active int DEFAULT 1,
    kind text NOT NULL,
    sn text,
    name text DEFAULT '',
    remark text DEFAULT '',
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'asset'); 

CREATE FOREIGN TABLE zy_asset_group (
    id serial ,
    kind text NOT NULL,
    asset_id int8 ,
    group_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'asset_group'); 

CREATE FOREIGN TABLE zy_asset_place (
    id serial ,
    kind text NOT NULL,
    asset_id int8 ,
    place_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'asset_place'); 

CREATE FOREIGN TABLE zy_asset_user (
    id serial ,
    kind text NOT NULL,
    asset_id int8 ,
    user_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'asset_user'); 

CREATE FOREIGN TABLE zy_asset_asset (
    id serial ,
    kind text NOT NULL,
    asset_aid int8 ,
    asset_bid int8
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'asset_asset'); 

CREATE FOREIGN TABLE zy_device (
    id int8 ,
    active int DEFAULT 1,
    kind text NOT NULL,
    sn text NOT NULL,
    name text DEFAULT '',
    remark text DEFAULT '',
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device'); 

CREATE FOREIGN TABLE zy_device_version (
    id serial8 ,
    kind text NOT NULL,
    module text NOT NULL,
    version text NOT NULL,
    applicant_id int8 ,
    remark text,
    data jsonb DEFAULT '{}',
        created_at timestamp ,
        updated_at timestamp 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_version'); 

CREATE FOREIGN TABLE zy_device_group (
    id serial ,
    kind text NOT NULL,
    device_id int8 ,
    group_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_group'); 

CREATE FOREIGN TABLE zy_device_place (
    id serial ,
    kind text NOT NULL,
    device_id int8 ,
    place_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_place'); 

CREATE FOREIGN TABLE zy_device_user (
    id serial ,
    kind text NOT NULL,
    device_id int8 ,
    user_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_user');

CREATE FOREIGN TABLE zy_device_asset (
    id serial ,
    kind text NOT NULL,
    device_id int8 ,
    asset_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_asset');

CREATE FOREIGN TABLE zy_device_device (
    id serial ,
    kind text NOT NULL,
    device_aid int8 ,
    device_bid int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_device');

CREATE FOREIGN TABLE zy_area (
    id int8 ,
    active int DEFAULT 1,
    kind text NOT NULL,
    sn text,
    name text DEFAULT '',
    remark text DEFAULT '',
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'area');

CREATE FOREIGN TABLE zy_form (
    id int8 ,
    kind text NOT NULL,
    sn text,
    name text DEFAULT '',
    remark text DEFAULT '',
    status text NOT NULL,
    data jsonb DEFAULT '{}',
        created_at timestamp ,
        updated_at timestamp 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'form');

CREATE FOREIGN TABLE zy_form_group (
    id serial ,
    kind text NOT NULL,
    form_id int8 ,
    group_id int8 
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'form_group');

CREATE FOREIGN TABLE zy_form_item (
    id int8 ,
    form_id int8 ,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'form_item');

CREATE FOREIGN TABLE zy_event (
    ts timestamp ,
    id int8 NOT NULL ,
    parent_id int8,
    ori int8 NOT NULL,
    kind text NOT NULL,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'event');

CREATE FOREIGN TABLE zy_alarm (
    ts timestamp NOT NULL DEFAULT NOW(),
    id int8 NOT NULL ,
    ori int8 NOT NULL,
    kind text NOT NULL,
    severity int DEFAULT 0,
    ack_ts timestamp,
    acked boolean DEFAULT FALSE,
    clear_ts timestamp,
    cleared boolean DEFAULT FALSE,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'alarm');

CREATE FOREIGN TABLE zy_series (
    ts timestamp NOT NULL DEFAULT NOW(),
    id int8 NOT NULL ,
    ori int8 NOT NULL,
    kind text NOT NULL,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'series');

CREATE FOREIGN TABLE zy_status (
    id int8 ,
    ts timestamp NOT NULL DEFAULT NOW(),
    cate text NOT NULL,
    kind text NOT NULL,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'status');

CREATE FOREIGN TABLE zy_device_event (
    ts timestamp NOT NULL DEFAULT NOW(),
    id int8 NOT NULL ,
    parent_id int8,
    ori int8 NOT NULL,
    kind text NOT NULL,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_event');

CREATE FOREIGN TABLE zy_device_series (
    ts timestamp NOT NULL DEFAULT NOW(),
    id int8 NOT NULL ,
    ori int8 NOT NULL,
    kind text NOT NULL,
    data jsonb DEFAULT '{}'
        ) 
SERVER zy 
OPTIONS (schema_name 'public', table_name 'device_series');