postgres_showschema.sql

-- This function replicates the functionality of \d table_name
-- It's useful if you work in a sql tool such as intelliJ, where \d does not work.
--- usage: select * from check_schema('table_name');

drop function check_schema(text);
CREATE FUNCTION check_schema(text)
RETURNS TABLE (f1 text, attname name, format_type text, substr text, attnotnull boolean, attnum smallint) AS $$
BEGIN
    RETURN QUERY
SELECT $1 as f1, a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (SELECT c.oid FROM pg_catalog.pg_class c
                        LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
                      WHERE c.relname LIKE $1 AND pg_catalog.pg_table_is_visible(c.oid)
                    )
    AND a.attnum > 0 AND NOT a.attisdropped
    ORDER BY a.attnum
    ;
END;
$$ LANGUAGE PLPGSQL;