Lista de tabelas e atributos

# Retorna lista de tabelas schema IN ('optim','osmc','download','natcod','api','tmp_orig','geouri_ext').
# Sobre pg_catalog ver https://www.postgresql.org/docs/15/catalogs.html

psql postgres://postgres@localhost/dl05s_main << EOF
COPY (

    SELECT
        n.nspname,
        c.relname,
        d.description
    FROM pg_class c

    LEFT JOIN pg_namespace n
        ON
            c.relnamespace = n.oid

    LEFT JOIN pg_description d
        ON
            c.oid = d.objoid AND
            objsubid = 0

    WHERE
            n.nspname IN ('optim','osmc','download','natcod','api','tmp_orig','geouri_ext') AND
            c.relkind IN ('r') --r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

    ORDER BY nspname, relname

) TO '/tmp/pg_io/doc_table_optim.csv' CSV HEADER
;
EOF
cat /tmp/pg_io/doc_table_optim.csv

# Retorna lista de atributos das tabelas schema IN ('optim','osmc','download','natcod','api','tmp_orig','geouri_ext').
# Sobre pg_catalog ver https://www.postgresql.org/docs/15/catalogs.html

psql postgres://postgres@localhost/dl05s_main << EOF
COPY (

    SELECT
        n.nspname,
        c.relname,
        a.attname,
        t.typname,
        r.contype,
        d.description
    FROM pg_class c

    LEFT JOIN pg_namespace n
        ON
            c.relnamespace = n.oid

    LEFT JOIN pg_attribute a
        ON
            a.attrelid = c.oid

    LEFT JOIN pg_type t
        ON
            a.atttypid = t.oid

    LEFT JOIN pg_constraint r
        ON
            c.oid     = r.conrelid AND
            a.attnum  = ANY (r.conkey) AND
            r.contype IN ('p','f')

    LEFT JOIN pg_description d
        ON
            a.attnum   = d.objsubid AND
            a.attrelid = d.objoid

    WHERE
            -- c.relname = 'donor'
            n.nspname IN ('optim','osmc','download','natcod','api','tmp_orig','geouri_ext') AND
            a.attnum > 0 AND
            c.relkind IN ('r') --r = ordinary table, i = index, S = sequence, t = TOAST table, v = view, m = materialized view, c = composite type, f = foreign table, p = partitioned table, I = partitioned index

    ORDER BY nspname, relname, contype, attname

) TO '/tmp/pg_io/doc_att_optim.csv' CSV HEADER
;
EOF

cat /tmp/pg_io/doc_att_optim.csv