Friday 29 June 2018

Postgres Javascript (pg.js) dynamic column names

I have searched and searched and am just not finding the answer to this one.

I am using pg.js to run queries from my Node.js server to a Postgres database. I would like to use 1 function to run queries on two different tables. So I would like to do this:

database.query("SELECT * FROM $1 WHERE $2 = $3;",
  [type, idName, parseInt(id, 10)],
  (error, result) => {});

This results in a syntax error.

error: syntax error at or near "$1"

I found some SO articles that use either :name or a ~ to cast the variable as a "name". Like this:

database.query("SELECT * FROM $1~ WHERE $2~ = $3;",
  [type, idName, parseInt(id, 10)],
  (error, result) => {});

This results in the same error.

If I hard-code the table name and try to use the ~ on just the column name. I get the error:

error: operator does not exist: unknown ~

The only thing that seems to work is this very bad solution:

database.query("SELECT * FROM "+type+" WHERE "+idName+" = $1;",
  [parseInt(id, 10)],
  (error, result) => {});

Any help appreciated.



from Postgres Javascript (pg.js) dynamic column names

No comments:

Post a Comment