Hi
here is a proof concept of array_offset function
possible question:
* used comparation "=" or "IS NOT DISTINCT FROM"
In this initial proof concept I used "IS NOT DISTINCT FROM" operator - but
my opinion is not strong in this question. Both has some advantages and
disadvantages.
Regards
Pavel
2015-01-16 19:12 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>
> 2015-01-16 18:37 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>:
>
>> On 1/16/15 11:16 AM, Pavel Stehule wrote:
>>
>>>
>>>
>>> 2015-01-16 17:57 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com <mailto:
>>> jim.na...@bluetreble.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
>>
>
>
commit 9562ae2bf9d0e8afdf745e50857052f6b9052086
Author: Pavel Stehule <pavel.steh...@gooddata.com>
Date: Sat Jan 17 23:35:34 2015 +0100
initial implementation of array_offset function.
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
index 9ea1068..b3630b4 100644
--- a/doc/src/sgml/array.sgml
+++ b/doc/src/sgml/array.sgml
@@ -600,6 +600,15 @@ SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];
index, as described in <xref linkend="indexes-types">.
</para>
+ <para>
+ You can also search any value in array using the <function>array_offset</>
+ function (It returns a position of first occurrence of value in the array):
+
+<programlisting>
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], 'mon');
+</programlisting>
+ </para>
+
<tip>
<para>
Arrays are not sets; searching for specific array elements
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5e7b000..4aac21b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11474,6 +11474,9 @@ SELECT NULLIF(value, '(none)') ...
<primary>array_lower</primary>
</indexterm>
<indexterm>
+ <primary>array_offset</primary>
+ </indexterm>
+ <indexterm>
<primary>array_prepend</primary>
</indexterm>
<indexterm>
@@ -11593,6 +11596,18 @@ SELECT NULLIF(value, '(none)') ...
<row>
<entry>
<literal>
+ <function>array_offset</function>(<type>anyarray</type>, <type>anyelement</type>)
+ </literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>returns a offset of first occurrence of some element in a array. It uses
+ a <literal>IS NOT DISTINCT FROM</> operator for comparation.</entry>
+ <entry><literal>array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], 'mon')</literal></entry>
+ <entry><literal>2</literal></entry>
+ </row>
+ <row>
+ <entry>
+ <literal>
<function>array_prepend</function>(<type>anyelement</type>, <type>anyarray</type>)
</literal>
</entry>
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 600646e..62768fb 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -15,6 +15,7 @@
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+#include "utils/typcache.h"
/*-----------------------------------------------------------------------------
@@ -612,3 +613,116 @@ array_agg_array_finalfn(PG_FUNCTION_ARGS)
PG_RETURN_DATUM(result);
}
+
+
+/*
+ * array_offset - returns a offset of entered element in a array.
+ * Returns NULL when values is not a element of the array. It allow
+ * searching a NULL value due using a NOT DISTINCT FROM operator.
+ *
+ * Biggest difference against width_array is unsorted input array.
+ */
+Datum
+array_offset(PG_FUNCTION_ARGS)
+{
+ ArrayType *array;
+ Oid collation = PG_GET_COLLATION();
+ Oid element_type;
+ Datum searched_element = (Datum) 0,
+ value;
+ bool isnull;
+ int32 offset = 0;
+ bool found = false;
+ TypeCacheEntry *typentry;
+ ArrayMetaState *my_extra;
+ bool null_search;
+ ArrayIterator array_iterator;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ array = PG_GETARG_ARRAYTYPE_P(0);
+ element_type = ARR_ELEMTYPE(array);
+
+ if (PG_ARGISNULL(1))
+ {
+ if (!array_contains_nulls(array))
+ PG_RETURN_NULL();
+ null_search = true;
+ }
+ else
+ {
+ searched_element = PG_GETARG_DATUM(1);
+ null_search = false;
+ }
+
+ element_type = ARR_ELEMTYPE(array);
+
+ /* cache operator info */
+ my_extra = (ArrayMetaState *) fcinfo->flinfo->fn_extra;
+ if (my_extra == NULL)
+ {
+ fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(ArrayMetaState));
+ my_extra = (ArrayMetaState *) fcinfo->flinfo->fn_extra;
+ my_extra->element_type = ~element_type;
+ }
+
+ if (my_extra->element_type != element_type)
+ {
+ get_typlenbyvalalign(element_type,
+ &my_extra->typlen,
+ &my_extra->typbyval,
+ &my_extra->typalign);
+
+ typentry = lookup_type_cache(element_type,
+ TYPECACHE_EQ_OPR_FINFO);
+
+ if (!OidIsValid(typentry->eq_opr_finfo.fn_oid))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_FUNCTION),
+ errmsg("could not identify an equality operator for type %s",
+ format_type_be(element_type))));
+
+ my_extra->element_type = element_type;
+ fmgr_info(typentry->eq_opr_finfo.fn_oid, &my_extra->proc);
+ }
+
+ array_iterator = array_create_iterator(array, 0, my_extra);
+ offset = 0;
+
+ while (array_iterate(array_iterator, &value, &isnull))
+ {
+ offset += 1;
+
+ if (isnull || null_search)
+ {
+
+ if (isnull && null_search)
+ {
+ found = true;
+ break;
+ }
+ else
+ continue;
+ }
+
+ if (DatumGetBool(FunctionCall2Coll(&my_extra->proc, collation,
+ searched_element,
+ value)))
+ {
+ found = true;
+ break;
+ }
+ }
+
+ array_free_iterator(array_iterator);
+
+ /* Avoid leaking memory when handed toasted input */
+ PG_FREE_IF_COPY(array, 0);
+
+ if (!found)
+ PG_RETURN_NULL();
+
+ PG_RETURN_INT32(offset);
+}
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index 5591b46..9ab1da0 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -3945,7 +3945,7 @@ arraycontained(PG_FUNCTION_ARGS)
* The passed-in array must remain valid for the lifetime of the iterator.
*/
ArrayIterator
-array_create_iterator(ArrayType *arr, int slice_ndim)
+array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate)
{
ArrayIterator iterator = palloc0(sizeof(ArrayIteratorData));
@@ -3962,10 +3962,20 @@ array_create_iterator(ArrayType *arr, int slice_ndim)
iterator->arr = arr;
iterator->nullbitmap = ARR_NULLBITMAP(arr);
iterator->nitems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
- get_typlenbyvalalign(ARR_ELEMTYPE(arr),
- &iterator->typlen,
- &iterator->typbyval,
- &iterator->typalign);
+
+ if (mstate != NULL)
+ {
+ Assert(mstate->element_type == ARR_ELEMTYPE(arr));
+
+ iterator->typlen = mstate->typlen;
+ iterator->typbyval = mstate->typbyval;
+ iterator->typalign = mstate->typalign;
+ }
+ else
+ get_typlenbyvalalign(ARR_ELEMTYPE(arr),
+ &iterator->typlen,
+ &iterator->typbyval,
+ &iterator->typalign);
/*
* Remember the slicing parameters.
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 9edfdb8..f23684d 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -895,6 +895,8 @@ DATA(insert OID = 515 ( array_larger PGNSP PGUID 12 1 0 0 0 f f f f t f i 2
DESCR("larger of two");
DATA(insert OID = 516 ( array_smaller PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_smaller _null_ _null_ _null_ ));
DESCR("smaller of two");
+DATA(insert OID = 3277 ( array_offset PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 23 "2277 2283" _null_ _null_ _null_ _null_ array_offset _null_ _null_ _null_ ));
+DESCR("returns a offset of value in array");
DATA(insert OID = 1191 ( generate_subscripts PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 23 "2277 23 16" _null_ _null_ _null_ _null_ generate_subscripts _null_ _null_ _null_ ));
DESCR("array subscripts generator");
DATA(insert OID = 1192 ( generate_subscripts PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 23 "2277 23" _null_ _null_ _null_ _null_ generate_subscripts_nodir _null_ _null_ _null_ ));
diff --git a/src/include/utils/array.h b/src/include/utils/array.h
index 694bce7..999b4af 100644
--- a/src/include/utils/array.h
+++ b/src/include/utils/array.h
@@ -314,7 +314,7 @@ extern ArrayBuildStateAny *accumArrayResultAny(ArrayBuildStateAny *astate,
extern Datum makeArrayResultAny(ArrayBuildStateAny *astate,
MemoryContext rcontext, bool release);
-extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim);
+extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate);
extern bool array_iterate(ArrayIterator iterator, Datum *value, bool *isnull);
extern void array_free_iterator(ArrayIterator iterator);
@@ -348,6 +348,7 @@ extern Datum array_agg_finalfn(PG_FUNCTION_ARGS);
extern Datum array_agg_array_transfn(PG_FUNCTION_ARGS);
extern Datum array_agg_array_finalfn(PG_FUNCTION_ARGS);
+extern Datum array_offset(PG_FUNCTION_ARGS);
/*
* prototypes for functions defined in array_typanalyze.c
*/
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index ae5421f..3d87520 100644
--- a/src/pl/plpgsql/src/pl_exec.c
+++ b/src/pl/plpgsql/src/pl_exec.c
@@ -2304,7 +2304,7 @@ exec_stmt_foreach_a(PLpgSQL_execstate *estate, PLpgSQL_stmt_foreach_a *stmt)
errmsg("FOREACH loop variable must not be of an array type")));
/* Create an iterator to step through the array */
- array_iterator = array_create_iterator(arr, stmt->slice);
+ array_iterator = array_create_iterator(arr, stmt->slice, NULL);
/* Identify iterator result type */
if (stmt->slice > 0)
diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index d33c9b9..f94edf9 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -366,6 +366,60 @@ SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
{{3,4},{5,6},{1,2}}
(1 row)
+SELECT array_offset(ARRAY[1,2,3,4,5], 4);
+ array_offset
+--------------
+ 4
+(1 row)
+
+SELECT array_offset(ARRAY[5,3,4,2,1], 4);
+ array_offset
+--------------
+ 3
+(1 row)
+
+SELECT array_offset(ARRAY[[1,2],[3,4]], 3);
+ array_offset
+--------------
+ 3
+(1 row)
+
+SELECT array_offset(ARRAY[[1,2],[3,4]], 4);
+ array_offset
+--------------
+ 4
+(1 row)
+
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], 'mon');
+ array_offset
+--------------
+ 2
+(1 row)
+
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], 'sat');
+ array_offset
+--------------
+ 7
+(1 row)
+
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], NULL);
+ array_offset
+--------------
+
+(1 row)
+
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu',NULL,'fri','sat'], NULL);
+ array_offset
+--------------
+ 6
+(1 row)
+
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu',NULL,'fri','sat'], 'sat');
+ array_offset
+--------------
+ 8
+(1 row)
+
-- operators
SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
a
diff --git a/src/test/regress/sql/arrays.sql b/src/test/regress/sql/arrays.sql
index 733c19b..dac0882 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -185,6 +185,16 @@ SELECT array_cat(ARRAY[1,2], ARRAY[3,4]) AS "{1,2,3,4}";
SELECT array_cat(ARRAY[1,2], ARRAY[[3,4],[5,6]]) AS "{{1,2},{3,4},{5,6}}";
SELECT array_cat(ARRAY[[3,4],[5,6]], ARRAY[1,2]) AS "{{3,4},{5,6},{1,2}}";
+SELECT array_offset(ARRAY[1,2,3,4,5], 4);
+SELECT array_offset(ARRAY[5,3,4,2,1], 4);
+SELECT array_offset(ARRAY[[1,2],[3,4]], 3);
+SELECT array_offset(ARRAY[[1,2],[3,4]], 4);
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], 'mon');
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], 'sat');
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu','fri','sat'], NULL);
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu',NULL,'fri','sat'], NULL);
+SELECT array_offset(ARRAY['sun','mon','tue','wen','thu',NULL,'fri','sat'], 'sat');
+
-- operators
SELECT a FROM arrtest WHERE b = ARRAY[[[113,142],[1,147]]];
SELECT NOT ARRAY[1.1,1.2,1.3] = ARRAY[1.1,1.2,1.3] AS "FALSE";
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
