Convert PostgreSQL array to PHP array

If you have PostgreSQL 9.2 you can do something like this:

SELECT array_to_json(pg_array_result) AS new_name FROM tbl1;

The result will return the array as JSON

Then on the php side issue:

$array = json_decode($returned_field);

You can also convert back. Here is the JSON functions page

Reliable function to parse PostgreSQL (one-dimensional) array literal into PHP array, using regular expressions:

function pg_array_parse($literal)
{
    if ($literal == '') return;
    preg_match_all('/(?<=^\{|,)(([^,"{]*)|\s*"((?:[^"\\\\]|\\\\(?:.|[0-9]+|x[0-9a-f]+))*)"\s*)(,|(?<!^\{)(?=\}$))/i', $literal, $matches, PREG_SET_ORDER);
    $values = [];
    foreach ($matches as $match) {
        $values[] = $match[3] != '' ? stripcslashes($match[3]) : (strtolower($match[2]) == 'null' ? null : $match[2]);
    }
    return $values;
}

print_r(pg_array_parse('{blah,blah blah,123,,"blah \\"\\\\ ,{\100\x40\t\daő\ő",NULL}'));
// Array
// (
//     [0] => blah
//     [1] => blah blah
//     [2] => 123
//     [3] =>
//     [4] => blah "\ ,{@@ daőő
//     [5] =>
// )

var_dump(pg_array_parse('{,}'));
// array(2) {
//   [0] =>
//   string(0) ""
//   [1] =>
//   string(0) ""
// }

print_r(pg_array_parse('{}'));
var_dump(pg_array_parse(null));
var_dump(pg_array_parse(''));
// Array
// (
// )
// NULL
// NULL

print_r(pg_array_parse('{または, "some string without a comma", "a string, with a comma"}'));
// Array
// (
//     [0] => または
//     [1] => some string without a comma
//     [2] => a string, with a comma
// )

If you can foresee what kind text data you can expect in this field, you can use array_to_string function. It's available in 9.1

E.g. I exactly know that my array field labes will never have symbol '\n'. So I convert array labes into string using function array_to_string

SELECT 
  ...
  array_to_string( labels, chr(10) ) as labes
FROM
  ...

Now I can split this string using PHP function explode:

$phpLabels = explode( $pgLabes, "\n" );

You can use any sequence of characters to separate elements of array.

SQL:

SELECT
  array_to_string( labels, '<--###DELIMITER###-->' ) as labes

PHP:

$phpLabels = explode( $pgLabes, '<--###DELIMITER###-->' );

As neither of these solutions work with multidimentional arrays, so I offer here my recursive solution that works with arrays of any complexity:

<?php
function pg_array_parse($s,$start=0,&$end=NULL){
    if (empty($s) || $s[0]!='{') return NULL;
    $return = array();
    $br = 0;
    $string = false;
    $quote='';
    $len = strlen($s);
    $v = '';
    for($i=$start+1; $i<$len;$i++){
        $ch = $s[$i];

        if (!$string && $ch=='}'){
            if ($v!=='' || !empty($return)){
                $return[] = $v;
            }
            $end = $i;
            break;
        }else
        if (!$string && $ch=='{'){
            $v = pg_array_parse($s,$i,$i);
        }else
        if (!$string && $ch==','){
            $return[] = $v;
            $v = '';
        }else
        if (!$string && ($ch=='"' || $ch=="'")){
            $string = TRUE;
            $quote = $ch;
        }else
        if ($string && $ch==$quote && $s[$i-1]=="\\"){
            $v = substr($v,0,-1).$ch;
        }else
        if ($string && $ch==$quote && $s[$i-1]!="\\"){
            $string = FALSE;
        }else{
            $v .= $ch;
        }
    }
    return $return;
}
?>

I haven't tested it too much, but looks like it works. Here you have my tests with results:

var_export(pg_array_parse('{1,2,3,4,5}'));echo "\n";
/*
array (
  0 => '1',
  1 => '2',
  2 => '3',
  3 => '4',
  4 => '5',
)
*/
var_export(pg_array_parse('{{1,2},{3,4},{5}}'));echo "\n";
/*
array (
  0 => 
  array (
    0 => '1',
    1 => '2',
  ),
  1 => 
  array (
    0 => '3',
    1 => '4',
  ),
  2 => 
  array (
    0 => '5',
  ),
)
*/
var_export(pg_array_parse('{dfasdf,"qw,,e{q\"we",\'qrer\'}'));echo "\n";
/*
array (
  0 => 'dfasdf',
  1 => 'qw,,e{q"we',
  2 => 'qrer',
)
*/
var_export(pg_array_parse('{,}'));echo "\n";
/*
array (
  0 => '',
  1 => '',
)
*/
var_export(pg_array_parse('{}'));echo "\n";
/*
array (
)
*/
var_export(pg_array_parse(null));echo "\n";
// NULL
var_export(pg_array_parse(''));echo "\n";
// NULL

P.S.: I know this is a very old post, but I couldn't find any solution for postgresql pre 9.2

I can see you are using explode(',', trim($pgArray, '{}'));

But explode is used to Split a string by string (and you are supplying it an array!!). something like ..

$string = "A string, with, commas";
$arr = explode(',', $string);

What are you trying to do with array? if you want to concatenate have a look on implode

OR not sure if it is possible for you to specify the delimiter other than a comma? array_to_string(anyarray, text)