Re: Catalog help

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