views.sql

--Drop views
DO
$$
DECLARE
    rec   record;
BEGIN
   FOR rec IN
      (select *, replace(table_schema, 'datalink_', '') as target_schema from information_schema.tables

      where table_schema LIKE 'datalink_%')
   LOOP
      EXECUTE 'DROP SCHEMA IF EXISTS '
      || quote_ident(rec.target_schema)
      || ' CASCADE '
      ;
   END LOOP;
END$$;

--Create views
DO
$$
DECLARE
    rec   record;
BEGIN
   FOR rec IN
      (select *, replace(table_schema, 'datalink_', '') as target_schema from information_schema.tables

      where table_schema LIKE 'datalink_%')
   LOOP
      EXECUTE 'CREATE SCHEMA IF NOT EXISTS '
      || quote_ident(rec.target_schema)
      ;
      EXECUTE 'create materialized view IF NOT EXISTS '
        || quote_ident(rec.target_schema)
        || '.'
        || quote_ident(rec.table_name)
        || ' AS (SELECT * FROM '
        || quote_ident(rec.table_schema)
        || '.'
        || quote_ident(rec.table_name)
        || ' ) WITH NO DATA'
      ;
   END LOOP;
END$$;

--refresh views
DO
$$
DECLARE
    rec   record;
BEGIN
   FOR rec IN
      (
        SELECT
          n.nspname as table_schema,
          pg_class.relname as table_name
        FROM   pg_class
        JOIN   pg_catalog.pg_namespace n ON n.oid = pg_class.relnamespace
        WHERE  relkind = 'm'
        ORDER BY random()
      )
   LOOP
      EXECUTE 'refresh materialized view '
        || quote_ident(rec.table_schema)
        || '.'
        || quote_ident(rec.table_name)
      ;
   END LOOP;
END$$;


-- Importing salesforce schemas:
create schema datalink_auth_salesforce;
import foreign schema salesforce
FROM SERVER postgresql_colorful_82741 INTO datalink_auth_salesforce;