向pg_query_params数组添加参数会产生错误

Question

This works:

$entriesResult = pg_query_params("
SELECT * FROM crosstab(
    $$
    SELECT entry_id, field_id, COALESCE(d.data, f.default_val) AS data
    FROM   entry     e
    JOIN   field     f USING (section_id)
    LEFT   JOIN data d USING (field_id, entry_id)
    WHERE  e.section_id = 1 AND f.aggregate = 1 AND f.enabled = 1 AND f.deleted = 0
    ORDER  BY 1, 2
    $$
    ,
    $$
    SELECT field_id FROM field WHERE section_id = 1 AND aggregate = 1 AND enabled = 1 AND deleted = 0 ORDER BY rank
    $$
)
AS ct (entry_id int $fieldPrefixString)
ORDER  BY f1->>'value' DESC
", array());

This produces this error: bind message supplies 1 parameters, but prepared statement "" requires 0

$entriesResult = pg_query_params("
SELECT * FROM crosstab(
    $$
    SELECT entry_id, field_id, COALESCE(d.data, f.default_val) AS data
    FROM   entry     e
    JOIN   field     f USING (section_id)
    LEFT   JOIN data d USING (field_id, entry_id)
    WHERE  e.section_id = $1 AND f.aggregate = 1 AND f.enabled = 1 AND f.deleted = 0
    ORDER  BY 1, 2
    $$
    ,
    $$
    SELECT field_id FROM field WHERE section_id = $1 AND aggregate = 1 AND enabled = 1 AND deleted = 0 ORDER BY rank
    $$
)
AS ct (entry_id int $fieldPrefixString)
ORDER  BY f1->>'value' DESC
", array(3));

The difference is I am using the array parameter in the second, non working example. I'm guessing it's related to using crosstab? How can I make this work with pg_query_params?

As a last resort I can use pg_escape_id, but that's not ideal.

Answer 1

This is not directly related to crosstab, but to $1 not being interpolated inside literal strings. You'd get the same error writing this:

pg_query_params('SELECT $$ $1 $$', array(3));

$N parameters must stand on their own, as if they were expressions.

So it could be written like this instead, still with the $$-style quoting:

$entriesResult = pg_query_params("
SELECT * FROM crosstab(
    $$
    SELECT entry_id, field_id, COALESCE(d.data, f.default_val) AS data
    FROM   entry     e
    JOIN   field     f USING (section_id)
    LEFT   JOIN data d USING (field_id, entry_id)
    WHERE  e.section_id = $$ || cast($1 as text) || $$ AND f.aggregate = 1 AND f.enabled = 1 AND f.deleted = 0
    ORDER  BY 1, 2
    $$
    ,
    $$
    SELECT field_id FROM field WHERE section_id = $$ || cast($1 as text) || $$ AND aggregate = 1 AND enabled = 1 AND deleted = 0 ORDER BY rank
    $$
)
AS ct (entry_id int $fieldPrefixString)
ORDER  BY f1->>'value' DESC
", array(3));

In this version, $1 will be parsed as a parameter at the SQL level. I use the explicit cast cast($1 as text) because it doesn't leave any ambiguity about the parameter becoming a string, no matter what type it was initially.