Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1D09B2E00DD for ; Wed, 5 Mar 2008 09:10:42 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 02116-05 for ; Wed, 5 Mar 2008 09:10:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.175]) by postgresql.org (Postfix) with ESMTP id 7C50D2E00D6 for ; Wed, 5 Mar 2008 09:10:29 -0400 (AST) Received: by wf-out-1314.google.com with SMTP id 28so1617084wff.28 for ; Wed, 05 Mar 2008 05:10:27 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; bh=8TuZYtZcJzcGVwW3OUT5Gi6kd6o5aQzXkg3jXrYK/KM=; b=r8Y2rc35z2KjVHaFH7bwLQiDVzdn4hXZ2piEN8URq6zQS5Wku6t+VJ0X78GOHiaD5danS5xALEtJ0qXLhBDv+1BCcxPErjomouug7c2IADBo5GvkfCjw2LVTEemu4fuT3udyjN2XSKccX2Mig8YlUJ2ynRbGdhk7LYHimKniYCw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=MgV5Ok7RaxU4B8CAslEcQI21R0O3X3WixT+bWAPN8cD71zU7mf6QTck5bqL5ZMHCigx6ljxVC1j7UiJ/CTUko2YnkAF2dco3AjgyIBUoV9vfltVhPqVgzwifF7B6YS309t2DHHZ1I+b6cU/U0XMnCItaPxQVnreBqJkBrWJGkFY= Received: by 10.142.211.10 with SMTP id j10mr737367wfg.202.1204722627433; Wed, 05 Mar 2008 05:10:27 -0800 (PST) Received: by 10.142.148.16 with HTTP; Wed, 5 Mar 2008 05:10:27 -0800 (PST) Message-ID: <6a5e3a6f0803050510j11a19653h39564369e0427c6f@mail.gmail.com> Date: Wed, 5 Mar 2008 10:10:27 -0300 From: "=?ISO-8859-1?Q?Professor_Fl=E1vio_Brito?=" Subject: Re: Documenting a DB schema Cc: pgsql-sql@postgresql.org In-Reply-To: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_35927_11538548.1204722627449" References: <082D8A131DF72A4D88C908A1AD3DEB22023B34F2@mail-1.rf.lan> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.433 tagged_above=0 required=5 tests=AWL=-0.149, HTML_MESSAGE=0.001, MISSING_HEADERS=1.581 X-Spam-Level: * X-Archive-Number: 200803/15 X-Sequence-Number: 30343 ------=_Part_35927_11538548.1204722627449 Content-Type: text/plain; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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 :=3D '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 =3D c.relnamesp= ace 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 =3D a.attrelid AND d.adnum =3D a.attn= umAND 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 =3D 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 : > > 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 =96 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 colum= n > 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 * > > shahaf@redfin.com | tel: 206.859.2869 | fax: 877.733.3469 > > Redfin Corporation > 710 2nd Ave > Suite 600 > Seattle, WA 98104 > > > ------=_Part_35927_11538548.1204722627449 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi

You may try this.

CREATE TYPE tabela_estrutu= ra AS
   (esquema text,
    tabela text,
=     campo text,
    tipo text,
 &n= bsp;  valor text,
    autoincremento boolean);
AL= TER TYPE tabela_estrutura OWNER TO postgres;



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

 BEGIN
 eSql :=3D 'SELECT
    &= nbsp;  CAST(rel.nspname as TEXT), CAST(rel.relname AS TEXT) ,
CAST= (attrs.attname AS TEXT), CAST("Type" AS TEXT),
CAST("Def= ault" AS TEXT), attrs.attnotnull
        FROM
    =            (SELECT c.oid,= n.nspname, c.relname
        &n= bsp;      FROM pg_catalog.pg_class c
 &nbs= p;             = LEFT JOIN pg_catalog.pg_namespace n ON n.oid =3D c.relnamespace
 &n= bsp;            = ; WHERE pg_catalog.pg_table_is_visible(c.oid) ) rel
        JOIN
    = ;           (SELECT a.att= name, a.attrelid,
         =       pg_catalog.format_type(a.atttypid, a.atttypm= od) as "Type",
        = ;            &n= bsp; (SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
            &nb= sp;         WHERE d.adrelid =3D a.a= ttrelid AND d.adnum =3D a.attnum AND a.atthasdef)
   &nbs= p;           as "Def= ault", a.attnotnull, a.attnum
      &= nbsp;        FROM pg_catalog.pg_attribut= e a
           &n= bsp;   WHERE a.attnum > 0 AND NOT a.attisdropped ) attrs
        ON (attrs.attrelid =3D rel.oid )=
        WHERE relname LIKE ''= ;%' || vTabela || '%''
     &nb= sp;  ORDER BY attrs.attnum';
FOR r IN EXECUTE eSql
 LOO= P
 RETURN NEXT r;
END LOOP;
 IF NOT FOUND THEN
        RAISE= EXCEPTION 'Table not found', vTabela;
 END IF;
 RE= TURN;
 END
 $BODY$
  LANGUAGE 'plpgsql' VOL= ATILE;
ALTER FUNCTION dados_tabela(character varying) OWNER TO postgres;=




2008/3/4, Shahaf Abileah &= lt;shahaf@redfin.com>:

I'm looking for a systematic way to document the schema for the database behind our website (www.redfin.com), so that the devel= opers 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 =96 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:

 

          &n= bsp; 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 A= bileah | Lead Sof= tware Developer

shahaf@redfin.com = | tel: 206.859.2869 | fax: 877.733.3469

Redfin Corporation
710 2nd Ave
Suite 600
Seattle, WA 98104

 


------=_Part_35927_11538548.1204722627449--