Обсуждение: Re: Are there commands to enquire about table structure?

Поиск
Список
Период
Сортировка

Re: Are there commands to enquire about table structure?

От
"Lee Harr"
Дата:
>PostgreSQL mavens, can I ask the database, in a normal database query
>command, or in other words,
>essentially using the same environment where I'd say...
>
>     SELECT count(*) FROM mytable [WHERE myconditions_obtain];
>
>...to get # records in my table,
>
>o How many fields mytable has as in:
>     SELECT fieldcount(*) from mytable
>

In 7.4 anyhow, I bet you can do much of this with the
information_schema. This one would be ...

SELECT count(*)
    FROM information_schema.columns
    WHERE table_name='mytable';

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail


Re: Are there commands to enquire about table structure?

От
"Ben"
Дата:
On Sat, 31 Jan 2004 02:22:09 +0000, Lee Harr wrote:

> In 7.4 anyhow, I bet you can do much of this with the
> information_schema. This one would be ...
>
> SELECT count(*)
>     FROM information_schema.columns
>     WHERE table_name='mytable';

Sounds very good; however, the DB in question is 7.3.2 - no such namespace
exists there. If we can get the IP people to sign off on upgrading, I'll
look into this mechanism. We've got megabytes of SQL designed for
Postgres, though, and any changes have to be vetted against the whole
system before we can use them, something that takes many months.

--Ben



Re: Are there commands to enquire about table structure?

От
Doug McNaught
Дата:
"Ben" <reply@to-the-newsgroup.com> writes:

> On Sat, 31 Jan 2004 02:22:09 +0000, Lee Harr wrote:
>
>> In 7.4 anyhow, I bet you can do much of this with the
>> information_schema. This one would be ...
>>
>> SELECT count(*)
>>     FROM information_schema.columns
>>     WHERE table_name='mytable';
>
> Sounds very good; however, the DB in question is 7.3.2 - no such namespace
> exists there. If we can get the IP people to sign off on upgrading, I'll
> look into this mechanism. We've got megabytes of SQL designed for
> Postgres, though, and any changes have to be vetted against the whole
> system before we can use them, something that takes many months.

For 7.3, the info you need is available in the system catalogs, which
have a somewhat hairier layout than the SQL-standard information_schema.

-Doug

Re: Are there commands to enquire about table structure?

От
"Ben"
Дата:
On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote:

> For 7.3, the info you need is available in the system catalogs, which
> have a somewhat hairier layout than the SQL-standard information_schema.

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.

Thanks a million!

--Ben


Re: Are there commands to enquire about table structure?

От
Doug McNaught
Дата:
"Ben" <reply@to-the-newsgroup.com> writes:

> On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote:
>
>> For 7.3, the info you need is available in the system catalogs, which
>> have a somewhat hairier layout than the SQL-standard information_schema.
>
> 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.  I don't actually know whether anything major
changed between 7.3 and 7.4 that would break what you're trying to do,
but I doubt it.

-Doug


Re: Are there commands to enquire about table structure?

От
Tom Lane
Дата:
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

Re: Are there commands to enquire about table structure?

От
"Ben"
Дата:
On Sat, 31 Jan 2004 23:41:54 -0500, Doug McNaught wrote:

> For 7.3, the info you need is available in the system catalogs, which
> have a somewhat hairier layout than the SQL-standard information_schema.

Using Doug's pointer, I came up with this for 7.3...

SELECT
    a.relname,b.attname,c.typname,b.attlen,b.atttypmod
FROM
    pg_class AS a
    LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
    LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)

WHERE
    c.typname ILIKE '%XX%'
    AND b.attname ILIKE '%YY%'
    AND a.relname ILIKE '%ZZ%'
    AND b.attisdropped=false

ORDER BY
    a.relname,b.attname

...you replace XX, YY and ZZ with a substring you want to find in the
field, and/or table, and/or type. The select will return all matching
fields in a reasonble fashion.

What I've not figured out yet is how this relates to a particular
database; if a table and field match in two databases, you'll see them
both, which (probably) isn't what you'd want.

Further pointers are welcome, otherwise if I figure it out myself, I'll
post the results.

--Ben


Re: Are there commands to enquire about table structure?

От
Gregory Wood
Дата:
> Further pointers are welcome, otherwise if I figure it out myself, I'll
> post the results.

Just an opinion here, but I would implement these queries as views
similar to the information schema. That way you won't have to port
everything when the database changes. Once you upgrade to 7.4 you can
just keep on chugging away.

Greg

Re: Are there commands to enquire about table structure?

От
Tom Lane
Дата:
"Ben" <reply@to-the-newsgroup.com> writes:
> Using Doug's pointer, I came up with this for 7.3...

> SELECT
>     a.relname,b.attname,c.typname,b.attlen,b.atttypmod
> FROM
>     pg_class AS a
>     LEFT OUTER JOIN pg_attribute AS b ON (b.attrelid = a.oid)
>     LEFT OUTER JOIN pg_type AS c ON (b.atttypid = c.oid)
> WHERE
>     c.typname ILIKE '%XX%'
>     AND b.attname ILIKE '%YY%'
>     AND a.relname ILIKE '%ZZ%'
>     AND b.attisdropped=false
> ORDER BY
>     a.relname,b.attname

> What I've not figured out yet is how this relates to a particular
> database; if a table and field match in two databases, you'll see them
> both, which (probably) isn't what you'd want.

No, you won't, because each database has its own copy of pg_class et al.
Tables that are in other databases simply won't be in the copy of the
catalogs that you are looking at.

It is true that this query will produce multiple hits if you have
similarly named tables in different schemas of one database.  To deal
with that, you probably want to extend the thing to join against
pg_namespace and show the schema name.

            regards, tom lane