[efxhoy]

I had to implement a workaround for that for our DW which imports from two different app databases. IMO this should be part of

  import foreign schema

but then you would have to qualify the names somehow if you were importing from more than one db. I wrote this as a workaround, it runs every day as part of our data import job.

  DROP SCHEMA IF EXISTS fdw_enum CASCADE;
  CREATE SCHEMA fdw_enum;
  
  -- Get names of the enums using ::regtype casting and label and sort_order from app_a and app_b.
  CREATE VIEW fdw_enum.app_a_enums AS SELECT * FROM dblink('fdw_app_a',
  $QUERY$
    SELECT
      enumtypid::regtype AS name,
      enumsortorder sort_order,
      enumlabel label
    FROM pg_enum;
  $QUERY$
  ) AS t (name text, sort_order integer, label text);
  
  CREATE VIEW fdw_enum.app_b_enums AS SELECT * FROM dblink('fdw_app_b',
  $QUERY$
    SELECT
      enumtypid::regtype AS name,
      enumsortorder sort_order,
      enumlabel label
    FROM pg_enum;
  $QUERY$
  ) AS t (name text, sort_order integer, label text);
  
  -- Ensure enums with the same names aren't defined in both app_a and app_b.
  DO
  $DO$
  DECLARE
    matching_count integer;
  BEGIN
    SELECT COUNT(*) into matching_count
    FROM fdw_enum.app_a_enums INNER JOIN fdw_enum.app_b_enums USING (name);
    ASSERT
      matching_count = 0,
      'app_a and app_b-NG have identically named enums. Implement a check that they are identically defined.';
  END
  $DO$;
  
  CREATE VIEW fdw_enum.upstream_enums AS
  SELECT * FROM fdw_enum.app_a_enums
  UNION ALL
  SELECT * FROM fdw_enum.app_b_enums;
  
  
  CREATE PROCEDURE fdw_enum.create_type_if_not_exists(name text)
  LANGUAGE plpgsql
  AS $PROC$
    BEGIN
      EXECUTE format('CREATE TYPE %s AS ENUM ()', name);
      EXCEPTION WHEN duplicate_object THEN RAISE NOTICE 'Enum type % already exists, skipping', name;
    END;
  $PROC$
  ;
  
  
  -- To make this idempotent we create the enums empty then alter them by adding values.
  -- So instead of `CREATE TYPE foo AS ENUM ('bar', 'baz');` we do
  -- `CREATE TYPE IF NOT EXISTS foo AS ENUM ();
  -- `ALTER TYPE foo ADD VALUE IF NOT EXISTS ('bar');
  -- `ALTER TYPE foo ADD VALUE IF NOT EXISTS ('baz');
  -- This ensure the procedure is re-runnable and can add new values to the enum as they are added upstream.
  -- Order is ensured by the ORDER BY in the loop query.
  CREATE PROCEDURE fdw_enum.refresh_upsteram_enums()
  LANGUAGE plpgsql
  AS $PROC$
  DECLARE
    rec record;
    ddl text;
  BEGIN
    FOR rec IN SELECT DISTINCT(name) AS name FROM fdw_enum.upstream_enums
    LOOP
      CALL fdw_enum.create_type_if_not_exists(rec.name);
    END LOOP;
  
    FOR rec in
      SELECT *
      FROM fdw_enum.upstream_enums
      ORDER BY name, sort_order
    LOOP
      ddl := FORMAT('ALTER TYPE %s ADD VALUE IF NOT EXISTS %s', rec.name, quote_literal(rec.label));
      EXECUTE ddl;
    END LOOP;
  END
  $PROC$
  ;
  
  CALL fdw_enum.refresh_upsteram_enums();