Representing Enums in PostgreSQL(making.close.com)

    CREATE TABLE my_enum ( name TEXT PRIMARY KEY );

    CREATE TABLE foo ( my_enum TEXT REFERENCES my_enum (name) );

  import foreign schema

  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();