From: | Professor Flávio Brito <prof(dot)flaviobrito(at)gmail(dot)com> |
---|---|
To: | |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Documenting a DB schema |
Date: | 2008-03-05 13:10:27 |
Message-ID: | 6a5e3a6f0803050510j11a19653h39564369e0427c6f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin pgsql-docs pgsql-general pgsql-sql |
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 <shahaf(at)redfin(dot)com>:
>
> 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 *
>
> shahaf(at)redfin(dot)com | tel: 206.859.2869 | fax: 877.733.3469
>
> Redfin Corporation
> 710 2nd Ave
> Suite 600
> Seattle, WA 98104
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2008-03-05 14:28:40 | Re: Postgres port bindings changed after box restart |
Previous Message | Suresh Gupta VG | 2008-03-05 12:10:30 | Error with PG_MODULE_MAGIC macro |
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2008-03-05 14:37:12 | FAQ on Embedding Postgres |
Previous Message | Mary Anderson | 2008-03-04 22:13:02 | Re: Documenting a DB schema |
From | Date | Subject | |
---|---|---|---|
Next Message | btober | 2008-03-05 13:30:14 | Re: how do you write aggregate function |
Previous Message | Dawid Kuroczko | 2008-03-05 12:30:52 | Re: Find Number Of Sundays Between Two Dates |
From | Date | Subject | |
---|---|---|---|
Next Message | David Fetter | 2008-03-05 15:05:27 | Re: [DOCS] Documenting a DB schema |
Previous Message | Osvaldo Kussama | 2008-03-05 01:02:55 | Re: using copy from in function |