在交叉表的select语句中绑定参数

Question

I have a statement that creates a pivot table with the crosstab function. My goal is to have the user input a list of values, i.e. customer ids and for the query to return a pivot table with the values for each customer and month. I create a token for each entered customer_id and want to bind each token to the corresponding value.

The resulting query looks something like this:

SELECT * FROM crosstab (
  $$SELECT customer_id, month, value FROM tcustomers WHERE customer_id IN (:id_0, :id_1, :id_2)
  GROUP BY month, customer_id
  ORDER 1,2$$, $$SELECT UNNEST('{1,2,3,4,5,6,7,8,9,10,11,12}'::text[])$$
) AS ct("Customer" text, "1" text, "2" text, "3" text, "4" text, "5" text, "6" text, "7" text, "8" text, "9" text, "10" text, "11" text, "12" text)

The result is something along these lines:

          |1|2|3|4|5|6|7|8|9|10|11|12
customer_1|0|0|0|0|100|0|1|...
customer_2|1|0|2|200|0|0|1|...
customer_3|1|0|2|200|0|0|1|...
....

In this example the user entered three customer ids (customer_1, customer_2, customer_3) bound to the three tokens. Upon executing I get the error Message: 'ERROR: could not determine data type of parameter $1'

I tried replacing the $$ quoting with single quotes and escaping the single quotes within the statement with double quotes ('') but then I get a syntax error at the position where my tokens are.

I can get it to work without binding by simply putting the input values directly into the statement but I'd really prefer to use binding.

Is this at all possible?

Answer 1

This piece of code:

$$SELECT customer_id, month, value
FROM tcustomers
WHERE customer_id IN (:id_0, :id_1, :id_2)
GROUP BY month, customer_id
ORDER 1,2$$

is just a regular string insofar as the crosstab() function is concerned. Rather than do the binding at the level of the SQL statement, you could define a string and then sprintf() the parameter values in and pass that to the SQL statement:

$sql = sprintf('SELECT customer_id, month, value ' .
               'FROM tcustomers ' .
               'WHERE customer_id IN (%s, %s, %s) ' .
               'GROUP BY month, customer_id ' .
               'ORDER 1,2', $id_0, $id_1, $id_2);

$result = pg_query_params($dbconn,
  'SELECT * FROM crosstab ($1, ' .
      '$$SELECT unnest(\'{1,2,3,4,5,6,7,8,9,10,11,12}\'::text[])$$ ' .
  ') AS ct("Customer" text, "1" text, "2" text, "3" text, "4" text, "5" text, "6" text, "7" text, "8" text, "9" text, "10" text, "11" text, "12" text);',
  array($sql));