Thanks for the quick response. Except I don't know what you are referring
too. Is there something other than the system tables (pg_*)?
John
On Sunday 31 October 2004 08:35, Markus Bertheau wrote:
> Be sure to look at information_schema for easier access to such data.
>
> В Вск, 31.10.2004, в 17:26, John Fabiani пишет:
> > Hi,
> >
> > I'm attempting to move some SQL code from a MySQL database to a Postgres
> > database. First, I bet I'm not the only person ever to this - so I bet
> > there is a conversion white paper somewhere (if there's not - there
> > should be). Could someone point out where I might find it on the web -
> > google didn't help (most likely the wrong search string).
> >
> > Most of the issues I have are around Metadata and how MySQL uses a simple
> > statement to produce Metadata:
> >
> > 'SHOW tables'
> > 'describe table' ....
> >
> > At the moment I'm having trouble with 'describe table' which produces
> > three columns- column name, column type, column attributes (like PK).
> > The following works for the first two columns but I can't figure out how
> > to get the third column.
> > "SELECT a.attname, t.typname FROM pg_class c, pg_attribute a, pg_type t
> > WHERE c.relname = 'tablename' AND a.attnum > 0 AND a.attrelid = c.oid
> > AND a.atttypid = t.oid ORDER BY a.attnum"
> >
> > So will someone help out a novice? I need the third column in the
> > statement. I did find I could use '-E' when starting the psql client
> > which reproduces what Postgres uses for the '\d tablename' but it uses
> > three statements and it does not combine the index information until the
> > end.
> >
> > Which brings up another question. How does the statement 2 know about
> > the results of statement 1 (same how does statement 3 know results of
> > statement 2). Could it be that internally it created a temporary cursor?
> >
> > John
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 9: the planner will ignore your desire to choose an index scan if
> > your joining column's datatypes do not match
