2015-03-21 0:27 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>:
> On 3/20/15 2:48 PM, Pavel Stehule wrote:
>
>>
>>
>> 2015-03-20 18:47 GMT+01:00 Tom Lane <t...@sss.pgh.pa.us
>> <mailto:t...@sss.pgh.pa.us>>:
>>
>> Alvaro Herrera <alvhe...@2ndquadrant.com
>> <mailto:alvhe...@2ndquadrant.com>> writes:
>> > Pavel Stehule wrote:
>> >> I am thinking, so it is ok - it returns a offset, not position.
>>
>> > So you can't use it as a subscript? That sounds unfriendly. Almost
>> > every function using this will be subtly broken.
>>
>> I concur; perhaps "offset" was the design intention, but it's wrong.
>> The result should be a subscript.
>>
>>
>> do you have any idea about name for this function? array_position is ok?
>>
>
> +1 on array_position. It's possible at some point we'll actually want
> array_offset that does what it claims.
>
additional implementation of array_position needs few lines more
>
> On another note, you mentioned elsewhere that it's not possible to return
> anything other than an integer. Why can't there be a variation of this
> function that returns an array of ndims-1 that is the slice where a value
> was found?
We talked about it, when we talked about MD searching - and we moved it to
next stage.
I am thinking so array_postions can support MD arrays due returning a array
Regards
Pavel
>
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
commit 8ceb761fcd4bca3859c0ec371ec783a36795dd49
Author: Pavel Stehule <pavel.steh...@gooddata.com>
Date: Sat Mar 21 07:01:51 2015 +0100
initial
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
index 57074e0..91e2824 100644
--- a/src/backend/utils/adt/array_userfuncs.c
+++ b/src/backend/utils/adt/array_userfuncs.c
@@ -19,8 +19,8 @@
#include "utils/typcache.h"
-static Datum array_offset_common(FunctionCallInfo fcinfo);
-
+static Datum array_offset_common(FunctionCallInfo fcinfo, bool expected_position);
+static Datum array_offsets_common(FunctionCallInfo fcinfo, bool expected_position);
/*
* fetch_array_arg_replace_nulls
@@ -669,13 +669,33 @@ array_agg_array_finalfn(PG_FUNCTION_ARGS)
Datum
array_offset(PG_FUNCTION_ARGS)
{
- return array_offset_common(fcinfo);
+ return array_offset_common(fcinfo, false);
}
Datum
array_offset_start(PG_FUNCTION_ARGS)
{
- return array_offset_common(fcinfo);
+ return array_offset_common(fcinfo, false);
+}
+
+/*-----------------------------------------------------------------------------
+ * array_positiob, array_position_start :
+ * return the position of a value in an array.
+ *
+ * IS NOT DISTINCT FROM semantics are used for comparisons. Return NULL when
+ * the value is not found.
+ *-----------------------------------------------------------------------------
+ */
+Datum
+array_position(PG_FUNCTION_ARGS)
+{
+ return array_offset_common(fcinfo, true);
+}
+
+Datum
+array_position_start(PG_FUNCTION_ARGS)
+{
+ return array_offset_common(fcinfo, true);
}
/*
@@ -686,7 +706,7 @@ array_offset_start(PG_FUNCTION_ARGS)
* They are not strict so we have to test for null inputs explicitly.
*/
static Datum
-array_offset_common(FunctionCallInfo fcinfo)
+array_offset_common(FunctionCallInfo fcinfo, bool expected_positions)
{
ArrayType *array;
Oid collation = PG_GET_COLLATION();
@@ -701,6 +721,7 @@ array_offset_common(FunctionCallInfo fcinfo)
ArrayMetaState *my_extra;
bool null_search;
ArrayIterator array_iterator;
+ int lb = 1;
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
@@ -731,6 +752,8 @@ array_offset_common(FunctionCallInfo fcinfo)
null_search = false;
}
+ lb = expected_positions ? (ARR_LBOUND(array))[0] : 1;
+
/* figure out where to start */
if (PG_NARGS() == 3)
{
@@ -739,7 +762,7 @@ array_offset_common(FunctionCallInfo fcinfo)
(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
errmsg("initial offset should not be NULL")));
- offset_min = PG_GETARG_INT32(2);
+ offset_min = PG_GETARG_INT32(2) - lb + 1;
}
else
offset_min = 1;
@@ -818,9 +841,25 @@ array_offset_common(FunctionCallInfo fcinfo)
if (!found)
PG_RETURN_NULL();
- PG_RETURN_INT32(offset);
+ PG_RETURN_INT32(offset + lb - 1);
+}
+
+/*
+ * simple wrappers over array_offsets_common
+ */
+Datum
+array_offsets(PG_FUNCTION_ARGS)
+{
+ return array_offsets_common(fcinfo, false);
}
+Datum
+array_positions(PG_FUNCTION_ARGS)
+{
+ return array_offsets_common(fcinfo, true);
+}
+
+
/*-----------------------------------------------------------------------------
* array_offsets :
* return an array of offsets of a value in an array.
@@ -833,7 +872,7 @@ array_offset_common(FunctionCallInfo fcinfo)
*-----------------------------------------------------------------------------
*/
Datum
-array_offsets(PG_FUNCTION_ARGS)
+array_offsets_common(FunctionCallInfo fcinfo, bool expected_positions)
{
ArrayType *array;
Oid collation = PG_GET_COLLATION();
@@ -847,6 +886,7 @@ array_offsets(PG_FUNCTION_ARGS)
bool null_search;
ArrayIterator array_iterator;
ArrayBuildState *astate = NULL;
+ int lb;
if (PG_ARGISNULL(0))
PG_RETURN_NULL();
@@ -879,6 +919,8 @@ array_offsets(PG_FUNCTION_ARGS)
null_search = false;
}
+ lb = expected_positions ? (ARR_LBOUND(array))[0] : 1;
+
/*
* We arrange to look up type info for array_create_iterator only once per
* series of calls, assuming the element type doesn't change underneath us.
@@ -937,7 +979,7 @@ array_offsets(PG_FUNCTION_ARGS)
if (DatumGetBool(FunctionCall2Coll(&my_extra->proc, collation,
searched_element, value)))
astate =
- accumArrayResult(astate, Int32GetDatum(offset), false,
+ accumArrayResult(astate, Int32GetDatum(offset + lb - 1), false,
INT4OID, CurrentMemoryContext);
}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 3c218a3..64e768c 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -901,6 +901,12 @@ DATA(insert OID = 3278 ( array_offset PGNSP PGUID 12 1 0 0 0 f f f f f f i
DESCR("returns a offset of value in array with start index");
DATA(insert OID = 3279 ( array_offsets PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 1007 "2277 2283" _null_ _null_ _null_ _null_ array_offsets _null_ _null_ _null_ ));
DESCR("returns a array of offsets of some value in array");
+DATA(insert OID = 3394 ( array_position PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 23 "2277 2283" _null_ _null_ _null_ _null_ array_position _null_ _null_ _null_ ));
+DESCR("returns a position of value in array");
+DATA(insert OID = 3395 ( array_position PGNSP PGUID 12 1 0 0 0 f f f f f f i 3 0 23 "2277 2283 23" _null_ _null_ _null_ _null_ array_position_start _null_ _null_ _null_ ));
+DESCR("returns a position of value in array with start index");
+DATA(insert OID = 3396 ( array_positions PGNSP PGUID 12 1 0 0 0 f f f f f f i 2 0 1007 "2277 2283" _null_ _null_ _null_ _null_ array_positions _null_ _null_ _null_ ));
+DESCR("returns a array of positions of some 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/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out
index 14d6d32..5c46719 100644
--- a/src/test/regress/expected/arrays.out
+++ b/src/test/regress/expected/arrays.out
@@ -466,6 +466,18 @@ $$ LANGUAGE plpgsql;
NOTICE: 2
NOTICE: 4
NOTICE: 7
+SELECT array_position('[2:4]={1,2,3}'::int[], 1);
+ array_position
+----------------
+ 2
+(1 row)
+
+SELECT array_positions('[2:4]={1,2,3}'::int[], 1);
+ array_positions
+-----------------
+ {2}
+(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 40950a2..435e242 100644
--- a/src/test/regress/sql/arrays.sql
+++ b/src/test/regress/sql/arrays.sql
@@ -218,6 +218,9 @@ BEGIN
END
$$ LANGUAGE plpgsql;
+SELECT array_position('[2:4]={1,2,3}'::int[], 1);
+SELECT array_positions('[2:4]={1,2,3}'::int[], 1);
+
-- 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
