CODEWITHSUNDEEP
sqlpostgresql
tnibbles
tnibbles

Reputation: 125

List of tables/columns touched during SELECT statement

With postgres, is there a means of determining what tables/columns will be touched given an SQL query? I am attempting to see if given an SQL query, the user (a student) attempted to return data from a particular set of tables.

I want to do this programmatically such that having a human review the code is not necessary. I am also trying to avoid situations where invalid input might pass simple string search checks, for example having all the table names within a comment.

Upvotes: 1

Views: 261

Answers (2)

PotatoManager
PotatoManager

Reputation: 1775

Use the Explain operator to give you an idea of the calculations and index searches the database has done in the background.

Check this link https://www.postgresql.org/docs/9.6/static/using-explain.html

PostgreSQL devises a query plan for each query it receives. Choosing the right plan to match the query structure and the properties of the data is absolutely critical for good performance, so the system includes a complex planner that tries to choose good plans. You can use the EXPLAIN command to see what query plan the planner creates for any query.

EXPLAIN SELECT * FROM tenk1;

                     QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

Upvotes: 1

yanman1234
yanman1234

Reputation: 1019

To do so solely through Postgres, EXPLAIN should help you here. EXPLAIN will show tables accessed by a given query, with some details on columns/indexes used as well.

You could adapt this problem into one that works with string matching however. By simply parsing off any comment sections on a given query you should be able to fairly reliably string search for any columns/tables you want to know about.

Upvotes: 2

Related Questions