SchemaSpy Postgres Enum

As per this issue, and own requirements, it would be great for SchemaSpy to show enum variants.

Fix for now:

Before running SchemaSpy
Manually introspect pg schema
Insert enum variants into a comment for the column using enum
Make sure to retain column and type comments

Here is example schema:

CREATE TYPE without_comment_type AS ENUM (
    'ONE',
    'TWO'
);

CREATE TYPE with_comment_type AS ENUM (
    'THREE',
    'FOUR'
);

COMMENT ON TYPE with_comment_type IS 'Numbers above _two_ and below _five_';

CREATE TABLE example (
	id TEXT NOT NULL PRIMARY KEY,
	wihout_comments without_comment_type,
	with_type_comment with_comment_type,
	with_column_comment without_comment_type,
 	with_column_and_type_comment with_comment_type
);

COMMENT ON COLUMN example.with_column_comment IS 'Simple column comment';
COMMENT ON COLUMN example.with_column_and_type_comment IS 'Simple column comment, should also see enum type comment';

Now before running schema spy, run the following

-- Helper view
DROP 
    VIEW IF EXISTS column_enum_variants;
CREATE VIEW column_enum_variants AS 
SELECT 
    column_info.table_name AS table_name, 
    column_info.column_name AS column_name, 
    column_comment.description AS column_comment, 
    enum_comment.description AS enum_comment, 
    column_info.udt_name AS enum_name, 
    enum_variant.enumlabel AS enum_variant_name
FROM information_schema.columns AS column_info 
JOIN pg_catalog.pg_type AS enum_type 
    ON column_info.udt_name = enum_type.typname 
JOIN pg_catalog.pg_enum AS enum_variant 
    ON enum_type.oid = enum_variant.enumtypid 
LEFT JOIN pg_catalog.pg_statio_all_tables AS column_comment_info 
    ON column_info.table_name = column_comment_info.relname 
LEFT JOIN pg_catalog.pg_description AS column_comment 
    ON column_comment.objoid = column_comment_info.relid 
    AND column_comment.objsubid = column_info.ordinal_position 
LEFT JOIN pg_catalog.pg_description AS enum_comment 
    ON enum_comment.objoid = enum_type.oid;


DO $$ 
DECLARE 
    table_column RECORD;
    enum_variant RECORD;
    enum_description TEXT;
    new_column_comment TEXT;
BEGIN 
    -- Loops through enum type columns
    FOR table_column IN 
    SELECT 
        table_name, 
        column_name, 
        enum_name, 
        column_comment, 
        enum_comment 
    FROM column_enum_variants 
    GROUP BY 
        table_name, 
        column_name, 
        enum_name, 
        column_comment, 
        enum_comment 
    LOOP 

        enum_description := '`enum: ' || table_column.enum_name || '`';
        -- Add enum comment if exists
        IF table_column.enum_comment IS NOT NULL THEN 
            enum_description := enum_description || '</br>' || table_column.enum_comment || '</br>`variants:`';
        END IF;

        -- Loops through all enum variants
        FOR enum_variant IN 
        SELECT enum_variant_name 
        FROM 
            column_enum_variants 
        WHERE 
            table_name = table_column.table_name 
            AND column_name = table_column.column_name 
        LOOP 
            enum_description := enum_description || '</br>- ' || enum_variant.enum_variant_name;
        END LOOP;

        new_column_comment := '';
        -- Add enum column comment if exists
        IF table_column.column_comment IS NOT NULL THEN 
            new_column_comment := table_column.column_comment || '</br>';
        END IF;

        new_column_comment := new_column_comment || enum_description;

        -- Have to execute, since COMMENT does not allow expressions
        EXECUTE FORMAT(
            'COMMENT ON COLUMN ' || table_column.table_name || '.' || table_column.column_name || ' IS %L', 
            new_column_comment);

    END LOOP;
END $$ LANGUAGE PLPGSQL;

DROP VIEW column_enum_variants;

Markdown is just an example, style at will.

This example does not filter by schema (so if you have same tables/column or enum types between schemas need to add some extra ON conditions). Example is not optimised, was going for simplicity (kind of failed at that too haha).

andreievg/schemaspy_postgres_enum.md