PHP array to postgres array

Question 1

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?

Question 2

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
}

Question 3

Try something like this:

// collects all nodes that belong to a certain parent id
function findChildren($nodeList, $parentId = null) {
    $nodes = array();

    foreach ($nodeList as $node) {
        if ($node['parent_id'] == $parentId) {
            $node['children'] = findChildren($nodeList, $node['id']);
            $nodes[] = $node;
        }
    }

    return $nodes;
}

Use it like this:

$nestedNodes = findChildren($nodeList);

This code recursively searches for the given parent_id in the original $nodeList. If a matching node is found, it searches for the children of this node, and so on. If no children for the given parent_id are found, an empty array is retuned.

You could reduce the memory usage of this approach by using references for $nodeList.

Question 4

Here is some code to handle what you had originally proposed as output.

/**
 * Give it and array, and an array of parents, it will decent into the
 * nested arrays and set the value.
 */
function set_nested_value(array &$arr, array $ancestors, $value) {
  $current = &$arr;
  foreach ($ancestors as $key) {

    // To handle the original input, if an item is not an array, 
    // replace it with an array with the value as the first item.
    if (!is_array($current)) {
      $current = array( $current);
    }

    if (!array_key_exists($key, $current)) {
      $current[$key] = array();
    }
    $current = &$current[$key];
  }

  $current = $value;
}


$education = array(
  'x[1]'     => 'Georgia Tech',
  'x[1][1]'  => 'Mechanical Engineering',
  'x[1][2]'  => 'Computer Science',
  'x[2]'     => 'Agnes Scott',
  'x[2][1]'  => 'Religious History',
  'x[2][2]'  => 'Women's Studies',
  'x[3]'     => 'Georgia State',
  'x[3][1]'  => 'Business Administration',
);

$neweducation = array();

foreach ($education as $path => $value) {
  $ancestors = explode('][', substr($path, 2, -1));
  set_nested_value($neweducation, $ancestors, $value);
}

Basically, split your array keys into a nice array of ancestor keys, then use a nice function to decent into the $neweducation array using those parents, and set the value.

If you want the output that you have updated your post to have, add this in the foreach loop after the line with 'explode'.

$ancestors[] = 0;

Question 5

The left expressions must be pretty simply in PLpgSQL. The combination of array and composite type is not supported. You should to set a value of composite type, and then this value assign to array.

CREATE OR REPLACE FUNCTION playx(OUT mod playz[]) AS $$
DECLARE r playz;
BEGIN
  FOR i in 1..5 LOOP
    r.a = 1;
    r.b = 12.2;
    r.c = 1;
    r.d = 0.02;
    mod[i] = r;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

There is possible a shortcut:

CREATE OR REPLACE FUNCTION public.playx(OUT mod playz[])
LANGUAGE plpgsql
AS $function$
BEGIN
  FOR i in 1..5 LOOP
    mod[i] = ROW(1, 12.2, 1, 0.02);
  END LOOP;
END;
$function$;

Question 6

Since PostgreSQL will allow asking for a slice outside of the array size, and assuming there will never be more than 999 subarrays, we can use this monstrosity

WITH data AS (
  SELECT array[array[1,2,3], array[2,15,32], array[5,16,14]] as arr)
SELECT array_agg(arr)
  FROM (SELECT unnest(arr[1:999][1]) as arr from data) data2;

You can of course make the constant 999 larger if needed, it is just a random large number I threw in there.

The reason why this is so complicated is that if you would use just arr[1:999][1] you would still get a two-dimensional array, but with only the first elements. In this case {{1}, {2}, {5}}. If we use unnest() we can make it into a set, which can then be fed into array_agg() via subselect.

It would be nice to use array_agg(unnest(arr[1:999][1])) but the aggregation function doesn't like sets and I don't know if there is a way to convert it on the fly.

You can also use the actual array length, but it might cause unnecessary computation

SELECT unnest(arr[1:array_length(arr, 1)][1]) as arr from data

Note

If the arrays could be unnested by one level, you could just index the arrays and then use array_agg() to convert it back into an array with a lot simpler syntax

WITH data AS
  (SELECT array[1,2,3] as arr
   UNION ALL SELECT array[2,15,32] as arr
   UNION ALL SELECT array[5,16,14] as arr)
SELECT array_agg(arr[1]) from data;

The CTE is there just for input data, the actual meat is the array_agg(arr[1]). This will of course work for any number of input arrays.

PHP array to postgres array

Answers

php,arrays,postgresql,plpgsql