Dynamic table names in pg-promise

Question

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

Answer 1

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