Details
-
Type:
New Feature
-
Status:
Postponed
-
Priority:
Minor
-
Resolution: Unresolved
-
Affects Version/s: 0.1.3, 0.1.4
-
Fix Version/s: Next Minor Release
-
Component/s: Zend_Db
-
Labels:None
Description
The array syntax for a sql array is '{1,2,3}'::int[] for example. Zend_Db cannot properly bind to an inline array from a php array.
Current workaround is to avoid the prepared statement route and implode the array concatenating the query.
This is confirmed to affect operation with PGSQL, though I suspect it may affect all drivers.
:bindname::int[] results in 'array value must start with "{" or dimension information' error from the db. adding {:bindname}::int[] will result in a problem preparing the statement.
This may be a problem / limitation with the underlying pdo implementation.
Issue Links
Activity
The syntax is highly useful in sequence generation and the repeatable ordering of lists. It's also highly useful for performing IN/ANY predicate matching.
My specific use is for use with the generate_series() postgresql function.
Observe.
=# SELECT ('{a,b,c}'::varchar[])[i] AS demo FROM generate_series(1,3) s
;
demo
------
a
b
c
(3 rows)
Fixing this would be highly useful.
;
demo
------
a
b
c
(3 rows)
Fixing this would be highly useful.
I think this is an 80/20 rule thing. Single dimensional arrays of numbers or varchars should be easy to do and cover 80%+ of uses.
Your first idea does actually work.
public function testFunction() {
$phpArray = array('a','b','c');
$ret = $this->_db->query('SELECT (:bindname::varchar[])[i] AS demo FROM generate_series(1,3) s', array('bindname' => '{' . implode(',', $phpArra
y) . '}' ));
print_r(iterator_to_array($ret));
}
Array
(
[0] => Array
(
[demo] => a
)
[1] => Array
(
[demo] => b
)
[2] => Array
(
[demo] => c
)
)
(Note the s is important)
The latter is however what I'm looking for. It should either bind natively to int[] or varchar[], depending if the values in the array are numbers... defaulting to varchar if not... (might be a good case to add a cidr catch too)
We could use isInt from filter to do a quick check on the array and determine if any of the values arent numbers.
ideally the syntax would be
SELECT (:bindname)[i] AS demo FROM generate_series(1,3) s
and bind in 'bindname'=>(array) $phpArray;
a implicit ::type shouldnt fail it, and if you provide a int and :: cast it, pgsql should be smart enough to do that convert.
I'm most concerned however, with each element being properly escaped, which I'm not sure any of these solutions address yet.
K
', array('bindname' => '{' . implode(',', $phpArra
y) . '}' ));
print_r(iterator_to_array($ret));
}
Array
(
[0] => Array
(
[demo] => a
)
[1] => Array
(
[demo] => b
)
[2] => Array
(
[demo] => c
)
)
(Note the s is important)
The latter is however what I'm looking for. It should either bind natively to int[] or varchar[], depending if the values in the array are numbers... defaulting to varchar if not... (might be a good case to add a cidr catch too)
We could use isInt from filter to do a quick check on the array and determine if any of the values arent numbers.
ideally the syntax would be
SELECT (:bindname)[i] AS demo FROM generate_series(1,3) s
and bind in 'bindname'=>(array) $phpArray;
a implicit ::type shouldnt fail it, and if you provide a int and :: cast it, pgsql should be smart enough to do that convert.
I'm most concerned however, with each element being properly escaped, which I'm not sure any of these solutions address yet.
K
Changing fix version to 0.8.0.