2015-03-11 22:14 GMT+01:00 Jim Nasby <[email protected]>:
> On 3/11/15 1:29 AM, Pavel Stehule wrote:
>
>>
>> What is this comment supposed to mean? There is no 'width_array'...
>>
>>
>> It is typo (I am sorry) - should be width_bucket(, array)
>>
>> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=
>> e80252d424278abf65b624669c8e6b3fe8587cac
>>
>> The code is similar, but it expect large **sorted** input. array_offset
>> works on unsorted (alphabetical unsorted) data sets - like days of week ..
>>
>
> The functions are serving rather different purposes, so I'm not sure it's
> worth mentioning. If we do want to mention it, then something like the
> following should be added to *both* functions:
>
ok, I removed this note.
I added comment about wrapping and I simplified a code there - this method
is used more time in pg for same purposes.
Merged Jim's changes in doc
Pavel
>
> * This code is similar to width_bucket()
>
> and
>
> * This code is similar to array_offset()
>
> Incidentally, isn't it bad that we're doing all these static assignments
> inside the loop in width_bucket? Or can we count on the compiler to
> recognize this?
>
> http://lnk.nu/github.com/1dvrr.c
>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
diff --git a/doc/src/sgml/array.sgml b/doc/src/sgml/array.sgml
new file mode 100644
index 9ea1068..d90266f
*** a/doc/src/sgml/array.sgml
--- b/doc/src/sgml/array.sgml
*************** SELECT * FROM sal_emp WHERE pay_by_quart
*** 600,605 ****
--- 600,614 ----
index, as described in <xref linkend="indexes-types">.
</para>
+ <para>
+ You can also search for a value in an array using the <function>array_offset</>
+ function. It returns the position of the first occurrence of a value in an 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
new file mode 100644
index c198bea..311f2fe
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT NULLIF(value, '(none)') ...
*** 11480,11485 ****
--- 11480,11488 ----
<primary>array_lower</primary>
</indexterm>
<indexterm>
+ <primary>array_offset</primary>
+ </indexterm>
+ <indexterm>
<primary>array_prepend</primary>
</indexterm>
<indexterm>
*************** SELECT NULLIF(value, '(none)') ...
*** 11598,11603 ****
--- 11601,11637 ----
</row>
<row>
<entry>
+ <literal>
+ <function>array_offset</function>(<type>anyarray</type>, <type>anyelement</type> <optional>, <type>int</type></optional>)
+ </literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>returns the offset of the first occurrence of a value in an
+ array. It uses the <literal>IS NOT DISTINCT FROM</> operator for
+ comparation. The optional third argument specifies an initial offset to
+ begin the search at. Returns NULL when the value is not found. Note:
+ multi-dimensional arrays are squashed to one dimension before
+ searching.</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_offsets</function>(<type>anyarray</type>, <type>anyelement</type>)
+ </literal>
+ </entry>
+ <entry><type>int[]</type></entry>
+ <entry>returns an array of offsets of all occurrences of a value in a array. It uses
+ the <literal>IS NOT DISTINCT FROM</> operator for comparation. Returns an empty array
+ when there are no occurences of the value in the array. Note:
+ multi-dimensional input arrays are squashed to one dimension before
+ searching.</entry>
+ <entry><literal>array_offsets(ARRAY['A','A','B','A'], 'A')</literal></entry>
+ <entry><literal>{1,2,4}</literal></entry>
+ </row>
+ <row>
+ <entry>
<literal>
<function>array_prepend</function>(<type>anyelement</type>, <type>anyarray</type>)
</literal>
diff --git a/src/backend/utils/adt/array_userfuncs.c b/src/backend/utils/adt/array_userfuncs.c
new file mode 100644
index 6679333..a847358
*** a/src/backend/utils/adt/array_userfuncs.c
--- b/src/backend/utils/adt/array_userfuncs.c
***************
*** 12,20 ****
--- 12,24 ----
*/
#include "postgres.h"
+ #include "catalog/pg_type.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+ #include "utils/typcache.h"
+
+ static Datum array_offset_common(FunctionCallInfo fcinfo);
/*
*************** array_agg_array_finalfn(PG_FUNCTION_ARGS
*** 652,654 ****
--- 656,906 ----
PG_RETURN_DATUM(result);
}
+
+
+ /*
+ * array_offset - returns the offset of a value in an array (array_offset and
+ * array_offset_start are wrappers for safe call (look on opr_sanity test) a
+ * array_offset_common function.
+ *
+ * Returns NULL when value is not found. It uses a "NOT DISTINCT FROM" operator
+ * for comparation to be safe against NULL.
+ */
+ Datum
+ array_offset(PG_FUNCTION_ARGS)
+ {
+ return array_offset_common(fcinfo);
+ }
+
+ Datum
+ array_offset_start(PG_FUNCTION_ARGS)
+ {
+ return array_offset_common(fcinfo);
+ }
+
+ /*
+ * Common part for functions array_offset and array_offset_startpos.
+ */
+ static Datum
+ array_offset_common(FunctionCallInfo fcinfo)
+ {
+ ArrayType *array;
+ Oid collation = PG_GET_COLLATION();
+ Oid element_type;
+ Datum searched_element = (Datum) 0,
+ value;
+ bool isnull;
+ int offset = 0,
+ offset_min = 1;
+ 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;
+ }
+
+ if (PG_NARGS() == 3)
+ {
+ if (PG_ARGISNULL(2))
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("initial offset should not be NULL")));
+
+ offset_min = PG_GETARG_INT32(2);
+ }
+
+ /* 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);
+
+ while (array_iterate(array_iterator, &value, &isnull))
+ {
+ offset += 1;
+
+ if (offset < offset_min)
+ continue;
+
+ 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);
+ }
+
+ /*
+ * Returns a array of offsets of some value in array.
+ * Returns NULL, the array is NULL. When searching doesn't produce
+ * any value, then it returns empty array.
+ */
+ Datum
+ array_offsets(PG_FUNCTION_ARGS)
+ {
+ ArrayType *array;
+ Oid collation = PG_GET_COLLATION();
+ Oid element_type;
+ Datum searched_element = (Datum) 0,
+ value;
+ bool isnull;
+ int offset = 0;
+ TypeCacheEntry *typentry;
+ ArrayMetaState *my_extra;
+ bool null_search;
+ ArrayIterator array_iterator;
+ ArrayBuildState *astate = NULL;
+
+ if (PG_ARGISNULL(0))
+ PG_RETURN_NULL();
+
+ array = PG_GETARG_ARRAYTYPE_P(0);
+ element_type = ARR_ELEMTYPE(array);
+
+ astate = initArrayResult(INT4OID, CurrentMemoryContext, false);
+
+ if (PG_ARGISNULL(1))
+ {
+ /* fast return when we have no NULL */
+ if (!array_contains_nulls(array))
+ PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+
+ 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);
+
+ while (array_iterate(array_iterator, &value, &isnull))
+ {
+ offset += 1;
+
+ if (isnull || null_search)
+ {
+
+ if (isnull && null_search)
+ astate = accumArrayResult(astate,
+ Int32GetDatum(offset), false, INT4OID,
+ CurrentMemoryContext);
+
+ continue;
+ }
+
+ if (DatumGetBool(FunctionCall2Coll(&my_extra->proc, collation,
+ searched_element,
+ value)))
+ astate = accumArrayResult(astate,
+ Int32GetDatum(offset), false, INT4OID,
+ CurrentMemoryContext);
+ }
+
+ array_free_iterator(array_iterator);
+
+ /* Avoid leaking memory when handed toasted input */
+ PG_FREE_IF_COPY(array, 0);
+
+ PG_RETURN_DATUM(makeArrayResult(astate, CurrentMemoryContext));
+ }
diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
new file mode 100644
index 54979fa..9117a55
*** a/src/backend/utils/adt/arrayfuncs.c
--- b/src/backend/utils/adt/arrayfuncs.c
*************** arraycontained(PG_FUNCTION_ARGS)
*** 3989,3995 ****
* The passed-in array must remain valid for the lifetime of the iterator.
*/
ArrayIterator
! array_create_iterator(ArrayType *arr, int slice_ndim)
{
ArrayIterator iterator = palloc0(sizeof(ArrayIteratorData));
--- 3989,3995 ----
* The passed-in array must remain valid for the lifetime of the iterator.
*/
ArrayIterator
! array_create_iterator(ArrayType *arr, int slice_ndim, ArrayMetaState *mstate)
{
ArrayIterator iterator = palloc0(sizeof(ArrayIteratorData));
*************** array_create_iterator(ArrayType *arr, in
*** 4006,4015 ****
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);
/*
* Remember the slicing parameters.
--- 4006,4025 ----
iterator->arr = arr;
iterator->nullbitmap = ARR_NULLBITMAP(arr);
iterator->nitems = ArrayGetNItems(ARR_NDIM(arr), ARR_DIMS(arr));
!
! 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
new file mode 100644
index b8a3660..6a757f3
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 515 ( array_larger
*** 895,900 ****
--- 895,906 ----
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 = 3278 ( array_offset 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_offset_start _null_ _null_ _null_ ));
+ 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 = 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
new file mode 100644
index 649688c..b78b42a
*** a/src/include/utils/array.h
--- b/src/include/utils/array.h
*************** extern ArrayBuildStateAny *accumArrayRes
*** 323,329 ****
extern Datum makeArrayResultAny(ArrayBuildStateAny *astate,
MemoryContext rcontext, bool release);
! extern ArrayIterator array_create_iterator(ArrayType *arr, int slice_ndim);
extern bool array_iterate(ArrayIterator iterator, Datum *value, bool *isnull);
extern void array_free_iterator(ArrayIterator iterator);
--- 323,329 ----
extern Datum makeArrayResultAny(ArrayBuildStateAny *astate,
MemoryContext rcontext, bool release);
! 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);
*************** extern Datum array_agg_finalfn(PG_FUNCTI
*** 358,363 ****
--- 358,367 ----
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);
+ extern Datum array_offset_start(PG_FUNCTION_ARGS);
+ extern Datum array_offsets(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
new file mode 100644
index 0ad32f7..80cb58c
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2315,2321 ****
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);
/* Identify iterator result type */
if (stmt->slice > 0)
--- 2315,2321 ----
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, 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
new file mode 100644
index d33c9b9..dafb455
*** a/src/test/regress/expected/arrays.out
--- b/src/test/regress/expected/arrays.out
*************** SELECT array_cat(ARRAY[[3,4],[5,6]], ARR
*** 366,371 ****
--- 366,478 ----
{{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)
+
+ SELECT array_offsets(NULL, 10);
+ array_offsets
+ ---------------
+
+ (1 row)
+
+ SELECT array_offsets(NULL, NULL::int);
+ array_offsets
+ ---------------
+
+ (1 row)
+
+ SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
+ array_offsets
+ ---------------
+ {4,10}
+ (1 row)
+
+ SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
+ array_offsets
+ ---------------
+ {}
+ (1 row)
+
+ SELECT array_offsets(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
+ array_offsets
+ ---------------
+ {4,10}
+ (1 row)
+
+ SELECT array_length(array_offsets(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10 FROM generate_series(1,100) g(i)),
+ 'AAAAAAAAAAAAAAAAAAAAAAAAA5'),1);
+ array_length
+ --------------
+ 10
+ (1 row)
+
+ do $$
+ declare
+ o int;
+ a int[] := ARRAY[1,2,3,2,3,1,2];
+ begin
+ o := array_offset(a, 2);
+ while o is not null
+ loop
+ raise notice '%', o;
+ o := array_offset(a, 2, o + 1);
+ end loop;
+ end
+ $$ language plpgsql;
+ NOTICE: 2
+ NOTICE: 4
+ NOTICE: 7
-- 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
new file mode 100644
index 733c19b..978c981
*** a/src/test/regress/sql/arrays.sql
--- b/src/test/regress/sql/arrays.sql
*************** SELECT array_cat(ARRAY[1,2], ARRAY[3,4])
*** 185,190 ****
--- 185,222 ----
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');
+
+ SELECT array_offsets(NULL, 10);
+ SELECT array_offsets(NULL, NULL::int);
+ SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], 4);
+ SELECT array_offsets(ARRAY[1,2,3,4,5,6,1,2,3,4,5,6], NULL);
+ SELECT array_offsets(ARRAY[1,2,3,NULL,5,6,1,2,3,NULL,5,6], NULL);
+ SELECT array_length(array_offsets(ARRAY(SELECT 'AAAAAAAAAAAAAAAAAAAAAAAAA'::text || i % 10 FROM generate_series(1,100) g(i)),
+ 'AAAAAAAAAAAAAAAAAAAAAAAAA5'),1);
+
+ do $$
+ declare
+ o int;
+ a int[] := ARRAY[1,2,3,2,3,1,2];
+ begin
+ o := array_offset(a, 2);
+ while o is not null
+ loop
+ raise notice '%', o;
+ o := array_offset(a, 2, o + 1);
+ end loop;
+ end
+ $$ language plpgsql;
+
-- 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
