PostgreSQL:将ARRAY []传递给pg_query_params [英] PostgreSQL: pass ARRAY[] to pg_query_params
问题描述
我需要执行以下查询:
SELECT * FROM property_select(ARRAY[8,9,10,11,12,13,14,15,16,17,19,20,26,28])
使用PHP函数 pg_query_params($ prepared,$ params)
。
准备好的查询是:
SELECT * FROM property_select($1);
参数为: [ ARRAY [8,9,10,11, 12,13,14,15,16,17,19,20,26,28]]
如何将参数传递给 pg_query_params()
作为数组?
不可能使用'{{8,9,10,11,12,13,14 ,15,16,17,19,20,26,28}'
postgres数组(因为它可能包含字符串,并且该数组中的字符串可能包含和)。
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 ").
推荐答案
查找PostgreSQL的详细要求手册中的数组输入和输出语法。
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.
有一个用户在手册中发布的那段PHP代码:
//$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) . "}'";
还有一个此处的相关答案:
也请考虑 standard_conforming_strings
。反斜杠可能需要加倍,但是PHP的pg-modules应该自动为您完成。
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.
这篇关于PostgreSQL:将ARRAY []传递给pg_query_params的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!