I agree with this, I don't use enums they are always more trouble than they are worth.
They break FDW unless they are pre-created on the importing side. Super inconvenient.
They break FDW unless they are pre-created on the importing side. Super inconvenient.
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();