Re: Calling on all SQL guru's

On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jfabiani@yolo.com> wrote:
> Hi,
>
> First I'm trying to move a MySQL database to Postgres.  I have to emulate a
> MySQL sql statement - ''Describe tablename'  which in general is '\d
> tablename' from psql.  If I use '-E' my 7.3.x provides three sql statements
> and by 7.4.x produces four statements.  But what I want is a single SQL
> statement that produces the following:
>
> ------------------------------
> fieldname | field type | isPK
> -----------------------------------
> clientid        int             true
> last            char            false
> first           char            false

Unfortunately the guru certificate is still "in the post", but below
is a nasty kludge which might be going in the general direction you
want:

SELECT c.column_name AS fieldname,
       c.data_type AS fieldtype,
       COALESCE(i.indisprimary,FALSE) AS is_pkey
  FROM information_schema.columns c
  LEFT JOIN information_schema.key_column_usage cu
       ON (c.table_name=cu.table_name AND c.column_name=cu.column_name)
  LEFT JOIN pg_class cl ON(cl.relname=cu.table_name)
  LEFT JOIN pg_index i ON(cl.oid= i.indrelid)
 WHERE c.table_name='insert_tablename_here'

Caveats:
- this is _not_ schema-aware.
- requires the information schema, e.g. 7.4 and later
- might just be horribly wrong anyway, but you get the general idea ;-)

HTH

Ian Barwick
barwick@gmail.com