>the query is outright wrong. Really? I submit a query to help and all you can do is criticize? Yes it is legal to create a table that starts with pg_, but any PostgreSQL developer should know that is not a good idea.
If you don't like the query, write a better one yourself, but kindly do not try to degrade the efforts of others. On Fri, Dec 29, 2017 at 9:22 PM, Stephen Frost <sfr...@snowman.net> wrote: > Melvin, > > * Melvin Davidson (melvin6...@gmail.com) wrote: > > On Fri, Dec 29, 2017 at 9:07 PM, Stephen Frost <sfr...@snowman.net> > wrote: > > > * 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. > > > > *Edson's original request was for a query that shows the FILENAMEs for > the > > table.* > > As quoted previously, he request included: > > --- > > Based on information from this catalog, would I have a tool (perhaps, a C > > function) that check that data is really there? > --- > > Which is asking about having a function to 'stat' the files and check > their length ('data is really there'). > > > *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. * > > No, it won't, it'll filter out tables which exist in user schemas that > happen to start with one of the strings that the query includes ('pg_', > 'information', and 'sql_'). > > I encourage you to test it- create a table in the public schema called > 'pg_whatever' and see if your query picks it up or not. This isn't a > minor complaint about style, the query is outright wrong. > > Thanks! > > Stephen > -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.