Are there commands to enquire about table structure?

From: "Ben" <reply(at)to-the-newsgroup(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Are there commands to enquire about table structure?
Date: 2004-01-30 21:06:46
Message-ID: b26aca210516c97d8f584b00d85acac6@news.teranews.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

PostgreSQL mavens, can I ask the database, in a normal database query command, or in other words,
essentially using the same environment where I'd say...

SELECT count(*) FROM mytable [WHERE myconditions_obtain];

...to get # records in my table,

o How many fields mytable has as in:
SELECT fieldcount(*) from mytable

o What the names of the fields are as in:
SELECT fieldname(n) from mytable = one name ...OR...
SELECT fieldnames() from mytable = a row per field

o What type of field "fieldname" is as in:
SELECT fieldtype(fieldnumber) from mytable ...OR...
SELECT fieldtype(fieldname) from mytable ...OR...
SELECT fieldtypes() from mytable = a row per field

o How long a field is as in:
SELECT fieldlength(fieldnumber) from mytable ...OR...
SELECT fieldlength(fieldname) from mytable
SELECT fieldlengths() from mytable = a row per field

Ideally, I envision being able to say...

SELECT fieldnames(),fieldtypes(),fieldlengths() FROM customers;

...and I might get back:

[firstname],[char],[15]
[lastname],[char],[20]
[custnum],[int],NULL
etc...

...or maybe...
SELECT fieldnames(),fieldtypes() FROM customers;

...and I might get back:

[firstname],[char(15)]
[lastname],[char(20)]
[custnum],[int]
etc...

Maybe I could even say:
SELECT fieldnames(),fieldtypes() from customers WHERE fieldnames() ilike '%name%';

...and I might get back:

[firstname],[char(15)]
[lastname],[char(20)]

...where [custnum],[int] is left out because of the WHERE clause.

I would find this kind of capability very useful for certain types of flexible table
processing I'd like to do.

Thanks for any insight into this, I appreciate any and all input.

--Ben

Browse pgsql-general by date

  From Date Subject
Next Message Ed L. 2004-01-30 21:33:51 DB cache size strategies
Previous Message listas 2004-01-30 21:00:38 Re: Triggers, Stored Procedures, PHP. was: Re: PostgreSQL