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