Message-ID: <4766B323.6030102@denterprises.org>
Date: Mon, 17 Dec 2007 12:34:27 -0500
From: Colin Wetherbee <cww@denterprises.org>
User-Agent: Thunderbird 2.0.0.9 (Windows/20071031)
MIME-Version: 1.0
To: danap@dandymadeproductions.com
CC: pgsql-sql@postgresql.org
Subject: Re: Describe Table
References: <20071217165211.E18222E3202@postgresql.org>
In-Reply-To: <20071217165211.E18222E3202@postgresql.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

danap@dandymadeproductions.com wrote:
> I've reviewed much of the documentation and the forums,
> but unable to seem to find a easy way to obtain the same
> thing as the 'psql \d table' through sql. I know I can create
> through collecting info on table, but seems there must be an
> easier way. I desire to create a standard type SQL dump
> syntax.

Briefly, you use the special pg_ tables [0].

The following query is probably not the most efficient way of doing it, 
but it shows the column names for the "wines" table.  The first seven 
listed are system columns (tableoid - ctid), and the rest are data 
columns (name - score).

You can look at the descriptions for each of the pg_ tables to refine 
your query a bit, exclude system columns, figure out data types, and so 
forth.

cww=# SELECT pg_class.relname, attname FROM pg_attribute, pg_class WHERE 
attrelid = pg_class.reltype::integer - 1 AND pg_class.relname = 'wines';
  relname |     attname
---------+-----------------
  wines   | tableoid
  wines   | cmax
  wines   | xmax
  wines   | cmin
  wines   | xmin
  wines   | oid
  wines   | ctid
  wines   | name
  wines   | vintage
  wines   | origin
  wines   | specific_origin
  wines   | color
  wines   | type
  wines   | description
  wines   | vintner
  wines   | entry_date
  wines   | score
(17 rows)

This query works on 8.1.9.

Colin

[0] 
http://www.postgresql.org/files/documentation/books/aw_pgsql/node183.html