Generalized array_position to get at most n element and start from certain position.

[jayzhan211]

Is your feature request related to a problem or challenge?

While cleanup array_position I find we can extend it have better feature easily #7988

Background

array_position is the early function that follows on Postgresql.

There are three types of interface now.

array_position(array, element) 
array_position(array, element, index)
array_positions(array, element)

array_position(array, element) is the basic one.

For example array_position([1,2,3], 3) return 3 (1-indexed).

array_position(array, element, index) finds the element start_from index.

For example array_position([1,2,3,4,3], 3, 4) return 5.

array_positions(array, element) return all the occurrence of the elements.

For example array_positions([1,2,2,3,3,3], 2) return [2,3].

Problem

We are not able to find n element start from certain index.

Proposal 1 Extend the current interface

array_position(array, element, Option<index>, Option<n>) that able us to find n elements start from index and
array_positions(array, element, Option<index>) that able us to find all the elements start from index.

Proposal 2 Introduce array_position_n

array_position(array, element, Option<index>) that able us to find 1 element start from index.
array_position_n(array, element, n, Option<index>) that able us to find n elements start from index.
array_positions(array, element, Option<index>) that able us to find all the elements start from index.

I think either of two are good for me, so I would like to collect feedbacks from yours.

Others than PostgreSQL

Duckdb

list_position(list, element) has only the basic one.

Clickhouse

None

Spark

array_position(column: Column, value: Any) has only the basic one.

Azure

array_position(array, element) has only the basic one.

Note

I had not yet find other SQL have the similar function, so I'm not sure if this feature is helpful or not.

Describe the solution you'd like

Extend array_position

Describe alternatives you've considered

Keep it as it is.

Additional context

No response

[tustvold]

FWIW this likely can just be implemented by slicing the list values and creating a new set of offsets

[alamb]

I had not yet find other SQL have the similar function, so I'm not sure if this feature is helpful or not.

I recommend we don't implement this ahead of someone actually asking for it / having a concrete usecase. "if it is good enough for spark / DuckDB it is probably good enough for DataFuaion"