Обсуждение: Is there anything like DESCRIBE?

Hi,
  I have a number of empty tables and I want to get the column names and
data types with an SQL statement. I want to do this procedurally, not
interactively (so I can't use \d <tablename> in psql). Postgres doesn't
support DESCRIBE... is there any other way to do this?

Thanks!
Mike

This works for me:

SELECT DISTINCT c.relname as table_name, a.attname as column_name, t.typname, pa.adsrc as default  FROM (pg_attribute a
       join pg_class c on a.attrelid = c.oid        join pg_type t on a.atttypid = t.oid)        left join pg_attrdef
paon c.oid = pa.adrelid AND a.attnum = pa.adnum  where exists (select * from pg_tables where tablename = c.relname and
substr(tablename,1,2)<> 'pg')   order by c.relname, a.attname;
 


-----Original Message-----
From:    Mike D'Agosta [SMTP:mdagosta@earthtribe.net]
Sent:    Wednesday, January 24, 2001 12:01 PM
To:    pgsql-sql@postgresql.org
Subject:    Is there anything like DESCRIBE?

Hi,
  I have a number of empty tables and I want to get the column names and
data types with an SQL statement. I want to do this procedurally, not
interactively (so I can't use \d <tablename> in psql). Postgres doesn't
support DESCRIBE... is there any other way to do this?

Thanks!
Mike

Mike D'Agosta wrote:
> 
> Hi,
> 
>    I have a number of empty tables and I want to get the column names and
> data types with an SQL statement. I want to do this procedurally, not
> interactively (so I can't use \d <tablename> in psql). Postgres doesn't
> support DESCRIBE... is there any other way to do this?
> 
> Thanks!
> Mike
like this

drfrog=# \d messages           Table "messages"    Attribute     |  Type   | Modifier 
-------------------+---------+----------id                | integer | not nullmsgid             | text    | boxid
     | integer | accountid         | integer | date              | text    | sentto            | text    | sentfrom
    | text    | subject           | text    | contenttype       | text    | contentxferencode | text    | mimeversion
   | text    | precedence        | text    | approvedby        | text    | inreplyto         | text    | replyto
  | text    | listsub           | text    | listunsub         | text    | status            | text    | xorigip
 | text    | cc                | text    | bcc               | text    | sender            | text    | returnpath
| text    | priority          | text    | xmailer           | text    | xuidl             | text    | xsender
|text    | localdate         | text    | newmsg            | text    | replyf            | integer | friendly
|text    | rreceiptto        | text    | score             | integer | ref               | text    | serverstat
|integer | 
 
Index: messages_pkey

"Mike D'Agosta" wrote: >Hi, > >   I have a number of empty tables and I want to get the column names and >data types
withan SQL statement. I want to do this procedurally, not >interactively (so I can't use \d <tablename> in psql).
Postgresdoesn't >support DESCRIBE... is there any other way to do this?
 


If you run psql with the -E option, it will show you the query it
uses when you type `\d+`.  Use that query in your procedural code.

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "My little children, let us not love in word, neither      in tongue; but in
deedand in truth."                                                     I John 3:18 
 

Mike D'Agosta wrote:
> 
> Hi,
> 
>    I have a number of empty tables and I want to get the column names and
> data types with an SQL statement. I want to do this procedurally, not
> interactively (so I can't use \d <tablename> in psql). Postgres doesn't
> support DESCRIBE... is there any other way to do this?

src/tutorial/syscat.source has some examples you might like to study.

-Ron-

I got a response in email, so I assume the author wants to remain anonymous.
He wrote:

>There's an option to psql (I think -E or -e) that will make it echo all
>the SQL queries it does when you do something like "\d table"

So running a "psql -E <dbname>" and then doing a "\d <table>" will give you
information similar to what you would normally get from a DESCRIBE.

Mike
----------------------------------------------------------------------------
--------------------------
"Mike D'Agosta" <mdagosta@earthtribe.net> wrote in message
news:94n93j$2j6j$1@news.tht.net...
> Hi,
>
>    I have a number of empty tables and I want to get the column names and
> data types with an SQL statement. I want to do this procedurally, not
> interactively (so I can't use \d <tablename> in psql). Postgres doesn't
> support DESCRIBE... is there any other way to do this?
>
> Thanks!
> Mike