Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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


pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group