PostgreSQL: pass ARRAY[] to pg_query_params
arrays, php, postgresql
Solution
Find the detailed requirements for PostgreSQL Array Input and Output Syntax in the manual.
Basically, you need to enclose array elements with special characters in double quotes `""`. You can double-quote all elements, but you don't have to. And, I quote the manual (see above):
To put a double quote or backslash in a quoted array element value, use escape string syntax and precede it with a backslash.
There is a piece of PHP code posted by a user in the manual:
//$t is array to be escaped. $u will be string literal.
$tv=array();
foreach($t as $key=>$val){
$tv[$key]="\"" .
str_replace("\"",'\\"', str_replace('\\','\\\\',$val)) . "\"
";
}
$u= implode(",",$tv) ;
$u="'{" . pg_escape_string($u) . "}'";
There is also a related answer here on SO:
Also consider the setting of `standard_conforming_strings`. Backslashes may need to be doubled, but PHP's pg-modules should do that for you automatically.
Problem
I need to do this query: ``` SELECT * FROM property_select(ARRAY[8,9,10,11,12,13,14,15,16,17,19,20,26,28]) ``` using PHP function `pg_query_params($prepared, $params)`. Prepared query is: ``` SELECT * FROM property_select($1); ``` Params are: `["ARRAY[8,9,10,11,12,13,14,15,16,17,19,20,26,28]"]` How can I pass the params to `pg_query_params()` as array? It's not possible to use `'{8,9,10,11,12,13,14,15,16,17,19,20,26,28}'` postgres arrays (because it may contain strings and strings inside this array may contain , and ").