现在PHP不能直接使用PostgreSQL数组。例如，PHP将像'{"foo"，“bar"}'这样的PostgreSQL数组
我需要一个简单的PHP函数，从PHP数组创建多维PostgreSQL数组。
我认为实验性的pg_convert()并不是最优的，因为它需要额外的数据来形成数据库输出的简单数组字符串，也许我误解了这个函数的思想。
例如，我需要转换
$from=array( array( "par_1_1","par_1_2" ), array( "array_2_1", "array_2_2" ) );
$to='{{"par_1_1","par_1_2"},{"par_2_1","par_2_2"}}';
我能否使用array_walk_recursive()来转换数组最深层的元素？
function to_pg_array($set) {
settype($set, 'array'); // can be called with a scalar or array
$result = array();
foreach ($set as $t) {
if (is_array($t)) {
$result[] = to_pg_array($t);
} else {
$t = str_replace('"', '\\"', $t); // escape double quote
if (! is_numeric($t)) // quote only non-numeric values
$t = '"' . $t . '"';
$result[] = $t;
}
}
return '{' . implode(",", $result) . '}'; // format
}
进行一些修改以使用pg_escape_string进行引用，并支持PHP的NULL和布尔值：
/**
* Converts a php array into a postgres array (also multidimensional)
*
* Each element is escaped using pg_escape_string, only string values
* are enclosed within single quotes, numeric values no; special
* elements as php nulls or booleans are literally converted, so the
* php NULL value is written literally 'NULL' and becomes a postgres
* NULL (the same thing is done with TRUE and FALSE values).
*
* Examples :
* VARCHAR VERY BASTARD ARRAY :
* $input = array('bla bla', 'ehi "hello"', 'abc, def', ' \'VERY\' "BASTARD,\'value"', NULL);
*
* to_pg_array($input) ==>> 'ARRAY['bla bla','ehi "hello"','abc, def',' ''VERY'' "BASTARD,''value"',NULL]'
*
* try to put this value in a query (you will get a valid result):
* select unnest(ARRAY['bla bla','ehi "hello"','abc, def',' ''VERY'' "BASTARD,''value"',NULL]::varchar[])
*
* NUMERIC ARRAY:
* $input = array(1, 2, 3, 8.5, null, 7.32);
* to_pg_array($input) ==>> 'ARRAY[1,2,3,8.5,NULL,7.32]'
* try: select unnest(ARRAY[1,2,3,8.5,NULL,7.32]::numeric[])
*
* BOOLEAN ARRAY:
* $input = array(false, true, true, null);
* to_pg_array($input) ==>> 'ARRAY[FALSE,TRUE,TRUE,NULL]'
* try: select unnest(ARRAY[FALSE,TRUE,TRUE,NULL]::boolean[])
*
* MULTIDIMENSIONAL ARRAY:
* $input = array(array('abc', 'def'), array('ghi', 'jkl'));
* to_pg_array($input) ==>> 'ARRAY[ARRAY['abc','def'],ARRAY['ghi','jkl']]'
* try: select ARRAY[ARRAY['abc','def'],ARRAY['ghi','jkl']]::varchar[][]
*
* EMPTY ARRAY (is different than null!!!):
* $input = array();
* to_pg_array($input) ==>> 'ARRAY[]'
* try: select unnest(ARRAY[]::varchar[])
*
* NULL VALUE :
* $input = NULL;
* to_pg_array($input) ==>> 'NULL'
* the functions returns a string='NULL' (literally 'NULL'), so putting it
* in the query, it becomes a postgres null value.
*
* If you pass a value that is not an array, the function returns a literal 'NULL'.
*
* You should put the result of this functions directly inside a query,
* without quoting or escaping it and you cannot use this result as parameter
* of a prepared statement.
*
* Example:
* $q = 'INSERT INTO foo (field1, field_array) VALUES ($1, ' . to_pg_array($php_array) . '::varchar[])';
* $params = array('scalar_parameter');
*
* It is recommended to write the array type (ex. varchar[], numeric[], ...)
* because if the array is empty or contains only null values, postgres
* can give an error (cannot determine type of an empty array...)
*
* The function returns only a syntactically well-formed array, it does not
* make any logical check, you should consider that postgres gives errors
* if you mix different types (ex. numeric and text) or different dimensions
* in a multidim array.
*
* @param array $set PHP array
*
* @return string Array in postgres syntax
*/
function to_pg_array($set) {
if (is_null($set) || !is_array($set)) {
return 'NULL';
}
// can be called with a scalar or array
settype($set, 'array');
$result = array();
foreach ($set as $t) {
// Element is array : recursion
if (is_array($t)) {
$result[] = to_pg_array($t);
}
else {
// PHP NULL
if (is_null($t)) {
$result[] = 'NULL';
}
// PHP TRUE::boolean
elseif (is_bool($t) && $t == TRUE) {
$result[] = 'TRUE';
}
// PHP FALSE::boolean
elseif (is_bool($t) && $t == FALSE) {
$result[] = 'FALSE';
}
// Other scalar value
else {
// Escape
$t = pg_escape_string($t);
// quote only non-numeric values
if (!is_numeric($t)) {
$t = '\'' . $t . '\'';
}
$result[] = $t;
}
}
}
return 'ARRAY[' . implode(",", $result) . ']'; // format
}
这与mstefano80的答案相同，但更易于理解、通用和现代化（至少对我来说是这样）:
<?php
class Sql
{
/**
* Convert PHP-array to SQL-array
* https://dev59.com/3W035IYBdhLWcg3wGL5T
*
* @param array $data
* @return string
*/
public static function toArray(array $data, $escape = 'pg_escape_string')
{
$result = [];
foreach ($data as $element) {
if (is_array($element)) {
$result[] = static::toArray($element, $escape);
} elseif ($element === null) {
$result[] = 'NULL';
} elseif ($element === true) {
$result[] = 'TRUE';
} elseif ($element === false) {
$result[] = 'FALSE';
} elseif (is_numeric($element)) {
$result[] = $element;
} elseif (is_string($element)) {
$result[] = "'" . $escape($element) . "'";
} else {
throw new \InvalidArgumentException("Unsupported array item");
}
}
return sprintf('ARRAY[%s]', implode(',', $result));
}
}
测试:
<?php
use Sql;
class SqlTest extends \PHPUnit_Framework_TestCase
{
public function testToArray()
{
$this->assertSame("ARRAY['foo','bar']", Sql::toArray(['foo', 'bar']));
$this->assertSame("ARRAY[1,2]", Sql::toArray([1, 2]));
$this->assertSame("ARRAY[1,2]", Sql::toArray(['1', '2']));
$this->assertSame("ARRAY['foo\\\"bar','bar\'foo']", Sql::toArray(['foo"bar', 'bar\'foo'], function($str){
return addslashes($str);
}));
$this->assertSame("ARRAY[ARRAY['foo\\\"bar'],ARRAY['bar\'foo']]", Sql::toArray([['foo"bar'], ['bar\'foo']], function($str){
return addslashes($str);
}));
}
}
is_numeric检查之前执行is_string检查，否则包含数字表示的字符串将被解释为数字并导致语法错误。 - faintsignalreturn sprintf('{%s}', implode(',', $result));以获取一个数组字面量。 - RcoderNY
array_2_1改成par_2_1吗？还是这个打错了？ - xkeshav