Re: Are there commands to enquire about table structure? - Mailing list 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