Small tool to render postgresql schema as plantuml input

pg-schema-to-plantuml.sh

#!/bin/sh

if [ -z "$1" ]; then
	(>&2 echo "Usage: $0 \"host=.. user=.. dbname=..\"")
	exit 1
fi

CONNSTRING=$1

cat - <<PRELUDE
@startuml
!define Table(name) class name << (T,#FFAAAA) >>
!define primary_key(x) <b>x</b>
!define unique(x) <color:green>x</color>
!define not_null(x) <u>x</u>

hide methods
hide stereotypes
PRELUDE

psql "$CONNSTRING" -Atf - <<EOF
SELECT format(E'package "%s" {\n%s\n}\n', n.nspname,
    (SELECT string_agg(format(E'Table(%s) {\n%s}\n', 
        relname, 
        (SELECT string_agg(format(E'  %s\n',
                format(case when attnotnull then 'not_null(%s)' else '%s' end,
                    format('%s: %s',
                        attname,
                        (SELECT typname FROM pg_type WHERE oid = atttypid)
                    )
    	    )
            ),'' ORDER BY attnum)
         FROM pg_attribute 
         WHERE attnum > 0 AND attislocal AND attrelid = c.oid AND NOT attisdropped
         )
       ), '' ORDER BY relname)
    FROM pg_class c
    WHERE relnamespace = n.oid
        AND relkind = 'r'
    ))
FROM pg_namespace n
WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname NOT LIKE 'pg_temp%' AND nspname NOT LIKE 'pg_toast%';

SELECT
    format('%s --> %s', clf.relname, (SELECT relname FROM pg_class WHERE oid = cn.confrelid))
FROM pg_constraint cn, pg_class clf
WHERE cn.conrelid = clf.oid 
    AND clf.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname NOT IN ('pg_catalog', 'information_schema') AND nspname NOT LIKE 'pg_temp%' AND nspname NOT LIKE 'pg_toast%')
    AND relkind = 'r'
    AND cn.contype = 'f'
ORDER BY 1 ASC;

EOF

cat - <<EPILOG
@enduml
EPILOG