PHP array to postgres array

Question

Now php can't work directly wit Postgresql array. For example, php taking postgresql array like '{"foo","bar"}'

I need simple php function to create multidimensional postgresql array from php array.

I think that experimental pg_convert() isn't optimal because it needs of extra data to form simple array string for database output, maybe I misunderstood the idea of this function.

For example, I need to 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"}}';

Can I use array_walk_recursive() to convert the deepest elements of array?

Solution 1

Here's a simple function for converting a PHP array to PG array.

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
}

Solution 2

A little edit that uses pg_escape_string for quoting and that support PHP NULLS and booleans:

/**
 * 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
}

Solution 3

This is the same as mstefano80's answer, but more human-readable, universal and modern (at least for me):

<?php

class Sql
{
    /**
     * Convert PHP-array to SQL-array
     * https://stackoverflow.com/questions/5631387/php-array-to-postgres-array
     * 
     * @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));
    }
}

Tests:

<?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);
        }));
    }
}

Comments

[faintsignal]

Warning: This will not quote a string which happens to represent a numeric value.

[faintsignal]

It would be safer to perform the is_string check before the is_numeric check otherwise a string containing the representation of a number will be interpreted as a number and cause a syntax error.

[RcoderNY]

Cool works nice! When using with prepared statements, I changed the last line to return sprintf('{%s}', implode(',', $result)); to get an array literal.