Thread: Documenting a DB schema

Hi,

> I'm looking for a systematic way to document the schema for the database
> behind our website (www.redfin.com <http://www.redfin.com/> ), so that
> the developers using this database have a better idea what all the
> tables and columns mean and what data to expect.  Any recommendations?

I am using Case Studio to document DB structures. I think it is pretty 
good tool.

http://www.casestudio.com/enu/default.aspx

To browse DB objects only, try DbVisualizer:
http://www.minq.se/products/dbvis/

- Ly

Shahaf Abileah wrote:

I’m looking for a systematic way to document the schema for the database behind our website (www.redfin.com), so that the developers using this database have a better idea what all the tables and columns mean and what data to expect.  Any recommendations?

 

It would be great if the documentation could be kept as close to the code as possible – that way we stand a chance of keeping it up to date.  So, in the same way that Java docs go right there on top of the class or method definitions, it would be great if I could attach my comments to the table definitions. It looks like MySQL has that kind of capability:

 

            create table table_with_comments(a int comment 'this is column a...');

 

(see http://dev.mysql.com/doc/refman/5.0/en/create-table.html)

 

However, Postgres doesn’t support the “comment” keyword.  Is there an alternative?

You mean like:
COMMENT ON mytable IS 'This is my table. Mine, mine, mine';

You can also comment columns, databases, functions, schemas, domains, etc.

Cheers,
Steve

Hi

You may try this.

CREATE TYPE tabela_estrutura AS
   (esquema text,
    tabela text,
    campo text,
    tipo text,
    valor text,
    autoincremento boolean);
ALTER TYPE tabela_estrutura OWNER TO postgres;



CREATE OR REPLACE FUNCTION dados_tabela(character varying)
  RETURNS SETOF tabela_estrutura AS
$BODY$
 DECLARE
 r tabela_estrutura%ROWTYPE;
 rec RECORD;
 vTabela alias for $1;
 eSql TEXT;

 BEGIN
 eSql := 'SELECT
       CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) ,
CAST(attrs.attname AS TEXT), CAST("Type" AS TEXT),
CAST("Default" AS TEXT), attrs.attnotnull
        FROM
               (SELECT c.oid, n.nspname, c.relname
               FROM pg_catalog.pg_class c
               LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
               WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
        JOIN
               (SELECT a.attname, a.attrelid,
               pg_catalog.format_type(a.atttypid, a.atttypmod) as "Type",
                      (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
                      WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
               as "Default", a.attnotnull, a.attnum
               FROM pg_catalog.pg_attribute a
               WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
        ON (attrs.attrelid = rel.oid )
        WHERE relname LIKE ''%' || vTabela || '%''
        ORDER BY attrs.attnum';
FOR r IN EXECUTE eSql
 LOOP
 RETURN NEXT r;
END LOOP;
 IF NOT FOUND THEN
        RAISE EXCEPTION 'Table not found', vTabela;
 END IF;
 RETURN;
 END
 $BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION dados_tabela(character varying) OWNER TO postgres;