By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,672 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,672 IT Pros & Developers. It's quick & easy.

Are there commands to enquire about table structure?

P: n/a
Ben
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

Nov 22 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.