How to prepare statement in Postgresql when using crosstab

Question

Using Symfony (2.8) and Doctrine, with Postgresql (9.5). I need to use crosstab to pivot my results using prepared statements in PHP.

My schema is different and complicated so, for the sake of simplicity, let's take an example table straight from Postgresql's documentation:

CREATE TABLES sales(year int, month int, qty int);
INSERT INTO sales VALUES(2007, 1, 1000);
INSERT INTO sales VALUES(2007, 2, 1500);
INSERT INTO sales VALUES(2007, 7, 500);
INSERT INTO sales VALUES(2007, 11, 1500);
INSERT INTO sales VALUES(2007, 12, 2000);
INSERT INTO sales VALUES(2008, 1, 1000);

Now, here is the PHP code:

$sql = "SELECT * from CROSSTAB(
    'SELECT year, month, qty FROM sales WHERE year = :year',
    'SELECT m FROM generate_series(1,12) m'
  ) as (
    year int,
    \"Jan\" int,
    \"Feb\" int,
    \"Mar\" int,
    \"Apr\" int,
    \"May\" int,
    \"Jun\" int,
    \"Jul\" int,
    \"Aug\" int,
    \"Sep\" int,
    \"Oct\" int,
    \"Nov\" int,
    \"Dec\" int
  );";

    $statement = $conn->prepare($sql);
    $params = ['year' => 2008];

    $statement->execute($params);

Executing this will throw an error:

SQLSTATE[HY093]: Invalid parameter number: :year

I think this may be because :year is being considered as part of a string literal and thus, it's unable to bind it. How do I make this work? Is there a workaround?

Answer 1

You're right. Quoted placeholders are not placeholders. They're just part of a string and only LOOK like placeholders. Build the string literal in pieces, instead:

$sql = "select * from crosstab(
    'select year, month, qty from sales order by 1 where year = ' + :year,
                                                                ^^^^^^^^^