Re: Calling on all SQL guru's

On Monday 01 November 2004 16:13, Ian Barwick wrote:
> 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 ;-)
>
God bless you!  It works as expected.  But is it possible to create a SQL
statement using only the pg files.  This will allow it to be used with 7.3.x
and later.  I have been trying for a full day.  Actually, I really need to
understand the relationship between the pg files.  Is there a description
somewhere???
From the bottom of my heart thanks.
John
John