base.d2

direction: left

legend: {
  style.fill: "#eee"
  SET NULL.style.fill: "lightyellow"
  NO ACTION.style.fill: "lightpink"
  RESTRICT.style.fill: "red"
  RESTRICT.style.font-color: "white"
  CASCADE.style.fill: "#c987ee"
  DEFAULT.style.fill: "lightblue"
}

classes: {
  set_null.style.fill: "lightyellow"
  no_action.style.fill: "lightpink"
  restrict.style.fill: "red"
  restrict.style.font-color: "white"
  cascade.style.fill: "#c987ee"
  set_default.style.fill: "lightblue"
}

main.sh

#!/usr/bin/env bash
set -euxo pipefail

psql "postgresql://postgres:postgres@localhost:5432/$1" \
  --file ./main.sql \
  --no-align --tuples-only \
  --variable=table_name="$2"

d2 fmt "$2.d2"
d2 "$2.d2"
open "$2.svg"

main.sql

\o :table_name.d2

with recursive cte (table_name, constraint_name, referenced_table, constraint_def, delete_type, path) as
                   (
                       SELECT cl.relname                    AS table_name,
                              con.conname                   AS constraint_name,
                              ft.relname                    AS referenced_table,
                              pg_get_constraintdef(con.oid) AS constraint_def,
                              con.confdeltype               AS delete_action,
                              array [cl.relname]            AS delete_action
                       FROM pg_constraint con
                                JOIN pg_class cl ON cl.oid = con.conrelid
                                JOIN pg_class ft ON ft.oid = con.confrelid
                       WHERE con.contype = 'f'
                         and ft.relname = :'table_name'
                       UNION
                       SELECT cl.relname                    AS table_name,
                              con.conname                   AS constraint_name,
                              ft.relname                    AS referenced_table,
                              pg_get_constraintdef(con.oid) AS constraint_def,
                              con.confdeltype               AS delete_action,
                              cl.relname || parent.path     AS delete_action
                       FROM pg_constraint con
                                JOIN pg_class cl ON cl.oid = con.conrelid
                                JOIN pg_class ft ON ft.oid = con.confrelid
                                JOIN cte parent on parent.table_name = ft.relname
                       WHERE con.contype = 'f'
                         and parent.delete_type = 'c'
                         and cl.relname != any (parent.path)
                   )
select '...@base'
union all
select FORMAT('%s -> %s : %s', table_name, referenced_table, constraint_name)
from cte
union all
select FORMAT(
               '%s.class: %s',
               table_name,
               case
                   when delete_type = 'a' then 'no_action'
                   when delete_type = 'c' then 'cascade'
                   when delete_type = 'r' then 'restrict'
                   when delete_type = 'd' then 'set_default'
                   when delete_type = 'n' then 'set_null'
               end)
from cte
;