On Oct 18, 2009, at 5:02 PM, Scott Ribe wrote:
I've figured out how to use the catalogs to get from table name to
name of
file(s) on disk, and also the toast files. But what I don't see is
how to
handle the case where a cluster contains multiple databases with
tables of
the same name--I fail to follow the oid keys somewhere along the way.
I don't actually have multiple tables with the same name (nor even
multiple
databases in the cluster), I'm just trying to understand how to
generalize
my query to correctly work in all cases.
Well, you can't. Each database has it's own pg_class (and other)
catalog table and tables in any given database are not visible from
others. If you mean table in multiple schemas with the same name then
you can differentiate on the pg_class.relnamespace field which is a
foreign key reference (although without an actual foreign key
constraint declared) to the oid values of rows in pg_namespace catalog
table. For example, say you want to find all of the schemas with a
table named 'foo':
SELECT n.nspname
FROM pg_namespace n, pg_class c
WHERE c.relnamespace = n.oid
AND c.relname = 'foo';
Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
