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