pg schema 2 markdown.sql

@set target_schema = 'public'

-- -----------------------------------------------------------------------------
-- USAGE: You MUST set the 'target_schema' variable in your SQL client before
-- running this script, otherwise you will get a syntax error.
--
-- Example in psql:
-- \set target_schema 'public'
--
-- Example in DBeaver:
-- @set target_schema = 'public'
-- -----------------------------------------------------------------------------

-- Drop temporary tables if they exist from a previous run in the same session.
DROP TABLE IF EXISTS schema_parts;
DROP TABLE IF EXISTS tables_md;

-- Create temporary tables.
CREATE TEMP TABLE schema_parts (
                                   table_schema TEXT,
                                   table_name TEXT,
                                   part_order INT,
                                   ordinal_position INT,
                                   md_string TEXT
);

CREATE TEMP TABLE tables_md (
                                md_table TEXT
);

-- Step 1: Insert column definitions for the target schema.
INSERT INTO schema_parts
SELECT
    n.nspname,
    c.relname,
    1 AS part_order,
    a.attnum,
    format('| %s | %s | %s | %s |',
           a.attname,
           format_type(a.atttypid, a.atttypmod),
           CASE WHEN a.attnotnull THEN 'NO' ELSE 'YES' END,
           COALESCE(pg_get_expr(d.adbin, d.adrelid), 'NULL')
    )
FROM pg_class c
         JOIN pg_namespace n ON n.oid = c.relnamespace
         JOIN pg_attribute a ON a.attrelid = c.oid
         LEFT JOIN pg_attrdef d ON d.adrelid = a.attrelid AND d.adnum = a.attnum
WHERE c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND n.nspname = :target_schema
  AND NOT c.relispartition;

-- Step 2: Insert primary key definitions for the target schema.
INSERT INTO schema_parts
SELECT
    pk_info.local_schema,
    pk_info.local_table,
    2 AS part_order,
    1 AS ordinal_position,
    format('**Primary Key**: (`%s`)', pk_info.local_columns)
FROM (
         SELECT
             ns.nspname AS local_schema,
             rel.relname AS local_table,
             (
                 SELECT string_agg(a.attname, '`, `' ORDER BY k.ord)
                 FROM unnest(con.conkey) WITH ORDINALITY AS k(attnum, ord)
                          JOIN pg_attribute AS a ON a.attrelid = con.conrelid AND a.attnum = k.attnum
             ) AS local_columns
         FROM pg_constraint AS con
                  JOIN pg_class AS rel ON rel.oid = con.conrelid
                  JOIN pg_namespace AS ns ON ns.oid = rel.relnamespace
         WHERE con.contype = 'p'
           AND ns.nspname = :target_schema
           AND NOT rel.relispartition
     ) AS pk_info;

-- Step 3: Insert foreign key definitions for the target schema.
INSERT INTO schema_parts
SELECT
    fk_info.local_schema,
    fk_info.local_table,
    3 AS part_order,
    row_number() OVER (PARTITION BY fk_info.local_schema, fk_info.local_table ORDER BY fk_info.constraint_name) AS ordinal_position,
    format(
            '* **Foreign Key**: (`%s`) REFERENCES `%s.%s` (`%s`)',
            fk_info.local_columns,
            fk_info.foreign_schema,
            fk_info.foreign_table,
            fk_info.foreign_columns
    )
FROM (
         SELECT
             con.conname AS constraint_name,
             ns1.nspname AS local_schema,
             rel1.relname AS local_table,
             ns2.nspname AS foreign_schema,
             rel2.relname AS foreign_table,
             (
                 SELECT string_agg(a.attname, '`, `' ORDER BY k.ord)
                 FROM unnest(con.conkey) WITH ORDINALITY AS k(attnum, ord)
                          JOIN pg_attribute AS a ON a.attrelid = con.conrelid AND a.attnum = k.attnum
             ) AS local_columns,
             (
                 SELECT string_agg(a.attname, '`, `' ORDER BY k.ord)
                 FROM unnest(con.confkey) WITH ORDINALITY AS k(attnum, ord)
                          JOIN pg_attribute AS a ON a.attrelid = con.confrelid AND a.attnum = k.attnum
             ) AS foreign_columns
         FROM pg_constraint AS con
                  JOIN pg_class AS rel1 ON rel1.oid = con.conrelid
                  JOIN pg_namespace AS ns1 ON ns1.oid = rel1.relnamespace
                  JOIN pg_class AS rel2 ON rel2.oid = con.confrelid
                  JOIN pg_namespace AS ns2 ON ns2.oid = rel2.relnamespace
         WHERE con.contype = 'f'
           AND ns1.nspname = :target_schema
           AND NOT rel1.relispartition
           AND NOT rel2.relispartition
     ) AS fk_info;

INSERT INTO tables_md VALUES ('# DB Schema');

-- Step 4: Assemble the Markdown documentation for each table.
INSERT INTO tables_md
SELECT
    format(E'## Table: `%s.%s`\n\n| Column | Type | Nullable | Default |\n|---|---|---|---|\n%s\n\n%s%s',
           table_schema,
           table_name,
           string_agg(CASE WHEN part_order = 1 THEN md_string END, E'\n' ORDER BY ordinal_position),
           coalesce(string_agg(CASE WHEN part_order = 2 THEN md_string END, '') || E'\n\n', ''),
           coalesce(string_agg(CASE WHEN part_order = 3 THEN md_string END, E'\n' ORDER BY ordinal_position), '')
    )
FROM schema_parts
GROUP BY table_schema, table_name;


-- if you're using Entity Framework, uncomment the following:
-- INSERT INTO tables_md
-- SELECT 'Latest migration at time of generation was `' || migration_id || '`'
-- FROM "__ef_migrations_history"
-- ORDER BY migration_id desc
-- LIMIT 1;

-- Step 5: Select the final, aggregated result and clean up.
SELECT string_agg(md_table, E'\n\n' ORDER BY md_table) FROM tables_md;

DROP TABLE schema_parts;
DROP TABLE tables_md;