public inbox for [email protected]  

help / color / mirror / Atom feed

From: =?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?= <[email protected]>
Cc: [email protected]
Subject: Re: Documenting a DB schema
Date: Wed, 5 Mar 2008 10:10:27 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

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.attnumAND
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;




2008/3/4, Shahaf Abileah <[email protected]>:
>
>  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?
>
>
>
> Thanks,
>
>
>
> --S
>
>
>
> *Shahaf Abileah *|* Lead Software Developer *
>
> [email protected] | tel: 206.859.2869 | fax: 877.733.3469
>
> Redfin Corporation
> 710 2nd Ave
> Suite 600
> Seattle, WA 98104
>
>
>

view thread (11+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Documenting a DB schema
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox