On Monday 01 November 2004 16:13, Ian Barwick wrote:
> 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 ;-)
>
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