Hi,

One of my application requirements is to create a view to display:

table_id, tablename, column,col type,size and description.

how do i do that?

thank you,
OcaVid

Sorry for this one... this should be in Postgres.

Take a look at the information_schema.

CREATE OR REPLACE VIEW x_field_descriptions AS
SELECT pg_description.objoid AS tableid, pg_description.objsubid AS seq, pg_description.description
FROM pg_description;

CREATE OR REPLACE VIEW x_fields AS
SELECT pg_attribute.attrelid AS tableid, pg_attribute.attname AS fieldname, pg_attribute.attnum AS seq
FROM pg_attribute;

CREATE OR REPLACE VIEW x_table_fields AS
SELECT x_fields.tableid, x_tables.tablename, x_fields.fieldname, x_fields.seq
FROM x_tables x_tables
JOIN x_fields x_fields ON x_fields.tableid = x_tables.tableid
WHERE x_fields.fieldname !~~ '%...%'::text AND x_fields.seq > 0;

CREATE OR REPLACE VIEW x_tables AS
SELECT c.oid AS tableid, c.relname AS tablename, n.nspname AS schemaname
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE n.nspname = 'public'::name AND c.relkind = 'r'::"char";

CREATE OR REPLACE VIEW x_table_field_descriptions AS
SELECT x_table_fields.tableid, x_table_fields.tablename, x_table_fields.fieldname, x_table_fields.seq, x_field_descriptions.description
FROM x_table_fields x_table_fields
JOIN x_field_descriptions x_field_descriptions ON x_field_descriptions.tableid = x_table_fields.tableid AND x_field_descriptions.seq = x_table_fields.seq
ORDER BY x_table_fields.tablename, x_table_fields.seq;

x_table_field_descriptions this where the information i needed.

any one might have a better way than this i am sure there is, but i don't know.

You NEED the oid?

If not, Information_schema.columns has everything that else... And, it's portable to many other database types...