Doug McNaught <[EMAIL PROTECTED]> writes:
> "Ben" <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
