Sorry, it's not about querying. I am implementing an invalidation mechanism for Postgres Query Cache as part of my masters project. In order to this, I need to store details(like name) of each table the query uses. In essence, I need to store the table names of the cached queries.
Initially, I thought of writing a code that could extract the table names but later discovered that it is a gargantuan task as I shall have to include around 600 production rules as was hinted in a Stackoverflow Exchange post. Hence, I thought of getting hold of the data structure used for storing table names of a DB but I couldn't get it.
Sorry for the long post but do you know where these tables information of a DB gets stored? Or can you suggest me a mechanism(needn't be fully perfect) to extract the table names? I went through the parser of postgres but it was confusing.
Thanks
Date: Fri, 9 Jan 2015 00:46:30 +1300 Subject: Re: [HACKERS] List of table names of a DB From: dgrowleyml@gmail.com To: in.live.in@live.in CC: pgsql-hackers@postgresql.org
Reason: I need a mechanism by which I can extract the names of the tables used in a query which has been parsed. My plan is to check for a match of each word in the query with a list of names of the tables used in the current DB so that each hit confirms a 'table name' in the query in most cases.
This sounds rather flaky.
Maybe it would be better to just EXPLAIN the query and see if you get error code 42P01
postgres=# \set VERBOSITY verbose
postgres=# explain select * from doesnotexist;
ERROR: 42P01: relation "doesnotexist" does not exist
LINE 1: explain select * from doesnotexist;
Unfortunately this won't help you much if you want to know all of the tables that don't exist.