代码之家 › 专栏 › 技术社区 › BishopZ

Postgres JavaScript(pg.js)动态列名

postgresql javascript

BishopZ · 技术社区 · 6 年前

我找了又找,就是找不到这个问题的答案。

我使用pg.js运行从node.js服务器到postgres数据库的查询。我想使用1个函数在两个不同的表上运行查询。所以我想这样做:

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

这会导致语法错误。

error: syntax error at or near "$1"

我找到了一些使用 :name 或者 ~ 将变量强制转换为“name”。这样地:

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

这会导致同样的错误。

如果我硬编码表名并尝试仅在列名上使用~的话。我知道错误:

error: operator does not exist: unknown ~

唯一可行的办法是这个非常糟糕的解决方案:

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

感谢任何帮助。

3 回复 | 直到 6 年前

Pruthvi Kumar 6 年前

你面临的问题是因为插值。动态列名和表名与注入动态值不同。

您可能想尝试一下:

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

这里有更多细节- https://github.com/datalanche/node-pg-format

肮脏的ES6修复可能是:

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

如果您计划构建许多动态查询,请考虑尝试- https://knexjs.org/

Daniel 6 年前

不能创建准备好的语句,也不能在查询中插入动态表或列名,因为这将不允许完全准备语句。如果必须执行prepared语句,则需要知道在prepared statements中必须定义输入数据类型(动态变量)和返回数据类型(返回列)。因为*将为不同的表返回不同的返回类型,所以上面的方法永远行不通。例如,如果您知道您总是返回一个id和一个名称,那么您可以在postgresql中创建一个函数,如下所示:

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');

现在可以动态查询最后一个选择。

BishopZ 6 年前

我确实找到了部分解决办法。虽然我在postgres网站上找不到关于这方面的任何文档,但我在网上找到了一些文章,其中展示了使用cast的速记符号将字符串作为列名进行强制转换的示例。

正如在问题中提到的,我发现一些stackoverflow文章提到 :name 作为一个解决方案,但对我有效的是 ::name 这是演员的速记。Postgres不记录 name 作为数据类型,但这对我确实有效。

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

对于表名,同样的事情不起作用。