Re: Calling on all SQL guru's

On Mon, 1 Nov 2004 09:59:44 -0800, John Fabiani <jfabiani(at)yolo(dot)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(at)gmail(dot)com