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.
3 Answers
Answers 1
The problem you are facing is because of interpolation. Dynamic column and table names are not same as injecting dynamic values.
You might want to give this a try:
npm install pg-format var format = require('pg-format'); var sql = format('SELECT * FROM %I WHERE my_col = %L %s', 'my_table', 34, 'LIMIT 10'); console.log(sql); // SELECT * FROM my_table WHERE my_col = '34' LIMIT 10 More details here - https://github.com/datalanche/node-pg-format
A dirty ES6 fix could be:
database.query(`SELECT * FROM ${type} WHERE ${idName} = $3;`, [parseInt(id, 10)], (error, result) => {}); If you plan on building a lot of dynamic queries, consider giving this a try - https://knexjs.org/
Answers 2
I did find a partial solution. While I can not find any documentation about this on the PostGres website, I found some online articles that showed examples of using the shorthand notation for CAST, to cast a string as a column name.
As mentioned in the question, I found several stackoverflow articles that mentioned :name as a solution, but what works for me is ::name which is the shorthand for CAST. Postgres does not document name as a datatype, but this does work for me.
database.query("SELECT * FROM "+ type +" WHERE $1::name = $2;", [idName, parseInt(id, 10)], (error, result) => {}); The same thing does not work for the table name.
Answers 3
You cannot create prepared statements and cannot inject dynamic table or column names in the query because this would disallow preparing the statement completely. If you definitely have to do prepared statements, you need to know that in PreparedStatements the input datatypes (the dynamic variables) AND the return datatypes (the returned columns) MUST be defined. Since * will return differnet return types for different tables the above will never work. If you know for example that you always return an id and a name you might create a function in postgresql like this:
CREATE TYPE idname AS (id int4, name text); CREATE OR REPLACE FUNCTION dynamicidnamequery(tablename text,field text,content text) RETURNS SETOF idname AS $$ DECLARE r idname; BEGIN FOR r IN EXECUTE 'SELECT id,name FROM '||tablename||' WHERE '||field||'='''||content||'''' LOOP return next r; END LOOP; END$$ language 'plpgsql'; select * from dynamicidnamequery('company','name','Amazon'); The last select can be queried dynamic now.
0 comments:
Post a Comment