Re: Are there commands to enquire about table structure?

Doug McNaught <doug(at)mcnaught(dot)org> writes:
> "Ben" <reply(at)to-the-newsgroup(dot)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