CREATE OR REPLACE FUNCTION public.alter_enumtype ( p_enumtypename text, p_values text[] ) RETURNS void AS
$$
    DECLARE
        v_row   record;
        v_query text;
    BEGIN
        v_query := 'CREATE TYPE __' || p_enumtypename || ' AS ENUM (''' || array_to_string( p_values, ''', ''' ) || ''' );';
        EXECUTE v_query;

        FOR v_row IN
            SELECT
                    table_schema,
                    table_name,
                    column_name
                FROM information_schema.columns
                WHERE
                    data_type = 'USER-DEFINED'
                    AND udt_name = p_enumtypename
                GROUP BY
                    table_schema,
                    table_name,
                    column_name
                ORDER BY
                    table_schema,
                    table_name,
                    column_name
        LOOP
            v_query := 'ALTER TABLE ' || v_row.table_schema || '.' || v_row.table_name || ' ALTER COLUMN ' || v_row.column_name || ' TYPE __' || p_enumtypename || ' USING CAST( CAST(' || v_row.column_name || ' AS TEXT) AS __' || p_enumtypename || ');';
            EXECUTE v_query;
        END LOOP;

        v_query := 'DROP TYPE ' || p_enumtypename || ';';
        EXECUTE v_query;

        v_query := 'ALTER TYPE __' || p_enumtypename || ' RENAME TO ' || p_enumtypename || ';';
        EXECUTE v_query;
    END;
$$
LANGUAGE plpgsql;

COMMENT ON FUNCTION public.alter_enumtype ( p_enumtypename text, p_values text[] ) IS 'Modification des valeurs acceptées par un type enum, pour tous les champs qui l''utilisent';

-- ----------------------------------------------------------------------------

SELECT public.alter_enumtype ( 'type_orient', ARRAY['social','prepro','emploi'] );