Re: proposal: searching in array function - array_position

2015-01-16 18:37 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>:

> On 1/16/15 11:16 AM, Pavel Stehule wrote:
>
>>
>>
>> 2015-01-16 17:57 GMT+01:00 Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com <mailto:
>> Jim(dot)Nasby(at)bluetreble(dot)com>>:
>>
>> On 1/16/15 3:39 AM, Pavel Stehule wrote:
>>
>> I am proposing a simple function, that returns a position of
>> element in array.
>>
>>
>> Yes please!
>>
>> FUNCTION array_position(anyarray, anyelement) RETURNS int
>>
>>
>> That won't work on a multi-dimensional array. Ideally it needs to
>> accept a slice or an element and return the specifier for the slice.
>>
>>
>> It is question, what is a result - probably, there can be a
>> multidimensional variant, where result will be a array
>>
>> array_position([1,2,3],2) --> 2
>> array_position([[1,2],[2,3],[3,4]], [2,3]) --> 2 /* 2nd parameter should
>> to have N-1 dimension of first parameter */
>>
>
> The problem with that is you can't actually use '2' to get [2,3] back:
>
> select (array[[1,2,3],[4,5,6],[7,8,9]])[1] IS NULL;
> ?column?
> ----------
> t
> (1 row)
>

yes, but when you are searching a array in array you can use a full slice
selection:

postgres=# select (ARRAY[[1,2],[4,5]])[1][1:2]; -- [1:2] should be a
constant every time in this case -- so it should not be returned
array
---------
{{1,2}}
(1 row)

>
> I think the bigger problem here is we need something better than slices
> for handling subsets of arrays. Even if the function returned [2:2] it's
> still going to behave differently than it will in the non-array case
> because you won't be getting the expected number of dimensions back. :(
>

you cannot to return a slice and I don't propose it, although we can return
a range type or array of range type - but still we cannot to use range for
a arrays.

>
> array_position_md([1,2,3],2) --> [2]
>> array_position_md([[1,2],[2,3],[3,4]], 2) --> [2,1]
>>
>> another question is how to solve more than one occurrence on one value -
>> probably two sets of functions - first returns first occurrence of value,
>> second returns set of occurrence
>>
>
> Gee, if only way had some way to return multiple elements of something...
> ;P
>
> In other words, I think all of these should actually return an array of
> positions. I think it's OK for someone that only cares about the first
> instance to just do [1].

there can be two functions - "position" - returns first and "positions"
returns all as a array

>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>