Search code examples
pg-promise

Dynamic table names in pg-promise

I would like to dynamically select the table in a pg-promise query. The following code does not work, any help is appreciated!

return db.any('SELECT * from $1', table_name)

The error message I get is:

error:  error: syntax error at or near

I am not sure if I should be using a helper, as described in this link: https://vitaly-t.github.io/pg-promise/helpers.TableName.html

Solution

  • The direct approach is via SQL Names:

    await db.any('SELECT * from $1:name', [table]);
// or
await db.any('SELECT * from ${table:name}', {table});

    And with the schema:

    await db.any('SELECT * from $1:name.$2:name', [schema, table]);
// or
await db.any('SELECT * from ${schema:name}.${table:name}', {schema, table});

    A more generic approach is via TableName:

    const tn = new pgp.helpers.TableName({schema, table});

await db.any('SELECT * from $1', [tn]);
// or
await db.any('SELECT * from ${tn}', {tn});

    UPDATE

    With pg-promise v11.8.0 and later, you can use _TN parser:

    const {_TN} = pgp.helpers;

await db.any('SELECT * from ${schema:name}.${table:name}', _TN`schema.table`);