On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfr...@snowman.net> wrote:
> Greetings, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > >There should be a "catalog" that point where tables are stored in > physical > > files... > > > > Here is the query that gives you that information. > > > > SELECT c.oid, > > n.nspname as schema, > > c.relname as table, > > (SELECT oid FROM pg_database WHERE datname = > > current_database() ) as db_dir, > > c.relfilenode as filename > > FROM pg_class c > > JOIN pg_namespace n ON (n.oid = c.relnamespace) > > WHERE relname NOT LIKE 'pg_%' > > AND relname NOT LIKE 'information%' > > AND relname NOT LIKE 'sql_%' > > AND relkind = 'r' > > ORDER BY 2, relname; > > This isn't a terribly good query- it's entirely valid to have 'pg_blah' > and 'informationblah' tables in user schemas. If you'd like to filter > out the catalogs/internal schemas, filter based on schema name instead. > > Also, this doesn't actually provide what Edson is asking for. Edson's > asking for a query that uses pg_ls_dir() or some custom function which > will run 'stat' on each file and return the size, according to the OS. > > Thanks! > > Stephen > *Edson's original request was for a query that shows the FILENAMEs for the table.* *As for "qood" query, that is entirely an opinion. The query WILL show all files associated* *with ALL tables. You are free to edit and reconstruct as you choose. * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
