1

I am trying to use a ParameterizedQuery with SQL Names inside its text parameter.

I know that the docs read that this parameter must be a string or a QueryFile.

Basically, what I'd like to do is something like:

import pgPromise from 'pg-promise';

const pgp = pgPromise();
const pq = new pgp.ParameterizedQuery({
      text: `
        SELECT $1:name from my_table
        where $2:name = $3;
        `,
      rowMode: 'array'
    });
const params = {user_col: 'user', id_col: 'id', id_value: 'XXX'};
try {
  return await this.db.any(pq, Object.values(params));
} catch (e) {
  console.error(e);

  return e;
}

What I get is an error like:

QUERY: {
[start:run]   text: '\n' +
[start:run]     '        SELECT $1:name from my_table\n' +
[start:run]     '        where $2:name = $3;\n' +
[start:run]     '        ',
[start:run]   values: [ 'user', 'id', 'XXX' ],
[start:run]   rowMode: 'array'
[start:run] }
[start:run] error: syntax error at or near ":"

Is it possible to use :name (or ~) inside a ParameterizedQuery? The thing is, I really want my query result would be an Array of rows rather than an array of row objects, and ParameterizedQuery's parmater rowMode set to array seems the only way I can do this.

umbe1987
  • 2,894
  • 6
  • 35
  • 63
  • I guess it is not possible, as text queries as `Only the basic variables ($1, $2, etc) can be used in the query, because Parameterized Queries are formatted on the server side.` (see [here](http://vitaly-t.github.io/pg-promise/ParameterizedQuery.html#text)). Am I right? Is there any alternative to return an array of rows instead an array of row objects from a query? – umbe1987 Sep 03 '20 at 09:18
  • 2
    [Formatting Filters](https://github.com/vitaly-t/pg-promise#formatting-filters) do not work within Parameterized Queries or Prepared Statements, as those are formatted on the server side. – vitaly-t Sep 03 '20 at 13:21

1 Answers1

1

From the Formatting Filters docs:

Note that formatting filters work only for normal queries, and are not available within PreparedStatement or ParameterizedQuery, because those are, by definition, formatted on the server side.

That note within official documentation was added following this question, because it came up a few times before. Hopefully, it will be more clear from now on.

vitaly-t
  • 24,279
  • 15
  • 116
  • 138