Are there commands to enquire about table structure?
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
o What the names of the fields are as in:
SELECT fieldname(n) from mytable = one name ...OR...
SELECT fieldnames() from mytable = a row per field
o What type of field "fieldname" is as in:
SELECT fieldtype(fieldnumber) from mytable ...OR...
SELECT fieldtype(fieldname) from mytable ...OR...
SELECT fieldtypes() from mytable = a row per field
o How long a field is as in:
SELECT fieldlength(fieldnumber) from mytable ...OR...
SELECT fieldlength(fieldname) from mytable
SELECT fieldlengths() from mytable = a row per field
Ideally, I envision being able to say...
SELECT fieldnames(),fieldtypes(),fieldlengths() FROM customers;
...and I might get back:
[firstname],[char],[15]
[lastname],[char],[20]
[custnum],[int],NULL
etc...
...or maybe...
SELECT fieldnames(),fieldtypes() FROM customers;
...and I might get back:
[firstname],[char(15)]
[lastname],[char(20)]
[custnum],[int]
etc...
Maybe I could even say:
SELECT fieldnames(),fieldtypes() from customers WHERE fieldnames() ilike '%name%';
...and I might get back:
[firstname],[char(15)]
[lastname],[char(20)]
...where [custnum],[int] is left out because of the WHERE clause.
I would find this kind of capability very useful for certain types of flexible table
processing I'd like to do.
Thanks for any insight into this, I appreciate any and all input.
--Ben
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
Import Notes
Resolved by subject fallback
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
"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
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
"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
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
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
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
"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