Re: Are there commands to enquire about table structure?
От | Tom Lane |
---|---|
Тема | Re: Are there commands to enquire about table structure? |
Дата | |
Msg-id | 10495.1075777047@sss.pgh.pa.us обсуждение исходный текст |
Ответ на | Re: Are there commands to enquire about table structure? (Doug McNaught <doug@mcnaught.org>) |
Список | pgsql-general |
Doug McNaught <doug@mcnaught.org> writes: > "Ben" <reply@to-the-newsgroup.com> writes: >> Doug, thanks - do you know if the system catalogs retain the same >> abilities in 7.4? So that if I implement this, will it still work later? I >> don't mind "hairy", but "temporary" is a concern, at least. > The system catalog layouts are not guaranteed to stay the same between > major versions. You will certainly be *able* to get column layout > information from the syscats but your queries might have to change > when you upgrade. Right. If you like, you can get a feeling for the sort of hacks you might need by looking at the source code for pg_dump. Here's pg_dump trying to extract information about column default expressions of a particular table --- it needs different queries for 7.0, 7.1, 7.2, and 7.3 (so far 7.4 and HEAD haven't diverged from 7.3): if (g_fout->remoteVersion >= 70300) { appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, " "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc " "FROM pg_catalog.pg_attrdef " "WHERE adrelid = '%u'::pg_catalog.oid", tbinfo->dobj.catId.oid); } else if (g_fout->remoteVersion >= 70200) { /* 7.2 did not have OIDs in pg_attrdef */ appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, " "pg_get_expr(adbin, adrelid) AS adsrc " "FROM pg_attrdef " "WHERE adrelid = '%u'::oid", tbinfo->dobj.catId.oid); } else if (g_fout->remoteVersion >= 70100) { /* no pg_get_expr, so must rely on adsrc */ appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc " "FROM pg_attrdef " "WHERE adrelid = '%u'::oid", tbinfo->dobj.catId.oid); } else { /* no pg_get_expr, no tableoid either */ appendPQExpBuffer(q, "SELECT " "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, " "oid, adnum, adsrc " "FROM pg_attrdef " "WHERE adrelid = '%u'::oid", tbinfo->dobj.catId.oid); } This particular aspect of the system catalogs has changed more than the core aspects like getting the column names of a table ... but on the other hand this is by no means the hairiest bit of pg_dump. It all depends on what you need to extract. regards, tom lane
В списке pgsql-general по дате отправления: