Pg: exec_params does not replace parameters?

Question

First time using pg gem to access the postgres database. I have successfully connected and can run queries using #exec , but now creating a simple query using #exec_params does not seem to replace the parameters. I.e:

 get '/databases/:db/tables/:table' do |db_name, table_name| conn = connect(db_name) query_result = conn.exec_params("SELECT * FROM $1;", [table_name]) end 

leads to #<PG::SyntaxError: ERROR: syntax error at or near "$1" LINE 1: SELECT * FROM $1; ^ > #<PG::SyntaxError: ERROR: syntax error at or near "$1" LINE 1: SELECT * FROM $1; ^ >

It seems like such a simple example to work with - I fundamentally misunderstand how to use this method?

Answer 1

You can use placeholders for values, not identifiers (for example, table and column names). This is one place where you are stuck using string interpolation to build your SQL. Of course, if you use line-breaking for your SQL, you must be sure to correctly point out / avoid things; for identifiers, which means using quote_ident :

+ (Object) quote_ident(str)

Returns a safe string for inclusion in the SQL query as an identifier. Note: this is not a quote function for values, but for identifiers.

So you would say something like:

 table_name = conn.quote_ident(table_name) query_result = conn.exec("SELECT * FROM #{table_name}")