gis Database

Parameters

Name	Type	Mode
imported_schemas	ARRAY	OUT

Definition

DECLARE
    p_clone_id text;
    p_sync_schema text;
    sqltemplate text;
    rec record;
    p_imported_schemas text[];
BEGIN
    sqltemplate = '';
    p_imported_schemas = ARRAY[]::text[];

-- Get clone id
    SELECT server_id::text INTO p_clone_id
    FROM lizsync.server_metadata
    LIMIT 1;

-- Import foreign tables of given schema
    -- We must first get the unique schemas
    FOR rec IN
        WITH
        synchronized_tables AS (
            SELECT sync_tables
            FROM central_lizsync.synchronized_tables
            WHERE server_id::text = p_clone_id
        ),
        a AS (
            SELECT DISTINCT jsonb_array_elements_text(sync_tables) AS sync_table
            FROM synchronized_tables
        ),
        b AS (
            SELECT regexp_split_to_array(sync_table, E'\\.') AS sync_table_elements
            FROM a
        )
        SELECT sync_table_elements[1] AS t_schema, string_agg(sync_table_elements[2], ',') AS t_tables
        FROM b
        GROUP BY t_schema
    LOOP
        p_sync_schema = trim(replace(rec.t_schema, '"', ''));
        p_imported_schemas = p_imported_schemas || p_sync_schema;
        sqltemplate = concat(
            sqltemplate,
            'DROP SCHEMA IF EXISTS "central_', p_sync_schema, '" CASCADE;',
            'CREATE SCHEMA "central_', p_sync_schema, '";',
            'IMPORT FOREIGN SCHEMA "', p_sync_schema, '"
            LIMIT TO (', rec.t_tables ,')
            FROM SERVER central_server
            INTO "central_', p_sync_schema, '";'
        );
    END LOOP;

EXECUTE sqltemplate;

RETURN QUERY
    SELECT p_imported_schemas;
END;

import_central_server_schemas

Description

Parameters

Definition