Query formatting for Parameterized Queries

Question

I am using pg-promise to execute select query with like clause in PostgreSQL. Unfortunately the query is failing with error code as 08P01 and error message

bind message supplies 1 parameters, but prepared statement "" requires 0

The query is as follows

select user_name, user_id from users where user_name like '$1#%'

I am using Parameterized query as

var userQuery:pgp.ParameterizedQuery = new pgp.ParameterizedQuery("<above_query>", [userName]);

The API used for executing query is

each(query:TQuery, values:any, cb:(row:any, index:number, data:Array<any>)=>void, thisArg?:any):XPromise<Array<any>>;

I looked at the pg-promise examples but it ain't using LIKE clause with Parameterized Query.

Environment Details are

pg-promise: 4.3.2

PostgreSQL: 9.6

Node: 5.7.1

UPDATE: 1 I am able to run the query using query API (plain text sql) but not with each. Can anyone please explain why the LIKE clause is failing with each while using Parameterized API.

Answer 1

From ParameterizedQuery API:

Only the basic variables ($1, $2, etc) can be used in the query, because Parameterized Queries are formatted by the database server.

Types PreparedStatement and ParameterizedQuery represent the corresponding objects within the node-postgres driver that executes them. Those objects encapsulate both the query and the formatting parameters.

i.e. the whole point of those two objects is to pass both query and the formatting parameters into the server, so they are formatted there, as opposed to using the internal query-formatting engine.

Because of that, you do not have access to the internal query-formatting features of pg-promise, such as $1# syntax. You can use the pg-promise syntax for query formatting when you are using queries directly, as a query string or as a QueryFile object.

With PreparedStatement and ParameterizedQuery you only have access to the basic $1, $2,... type of parameter formatting, as supported by the database server, nothing else.

---

P.S. I am the author of pg-promise.