Hi
2015-01-27 11:41 GMT+01:00 Pavel Stehule <[email protected]>:
>
>
> 2015-01-26 21:44 GMT+01:00 Jim Nasby <[email protected]>:
>
>> On 1/25/15 4:23 AM, Pavel Stehule wrote:
>>
>>>
>>> I tested a concept iteration over array in format [key1, value1, key2,
>>> value2, .. ] - what is nice, it works for [[key1,value1],[key2, value2],
>>> ...] too
>>>
>>> It is only a few lines more to current code, and this change doesn't
>>> break a compatibility.
>>>
>>> Do you think, so this patch is acceptable?
>>>
>>> Ideas, comments?
>>>
>>
>> Aside from fixing the comments... I think this needs more tests on corner
>> cases. For example, what happens when you do
>>
>> foreach a, b, c in array(array(1,2),array(3,4)) ?
>>
>
> it is relative simple behave -- empty values are NULL
>
> array(1,2),array(3,4) -- do you think ARRAY[[1,2],[3,4]] is effectively
> ARRAY[1,2,3,4]
>
>
>>
>> Or the opposite case of
>>
>> foreach a,b in array(array(1,2,3))
>>
>> Also, what about:
>>
>> foreach a,b in '{{{1,2},{3,4}},{{5,6},{7,8}}}'::int[] ?
>
>
>
> postgres=# select array(select
> unnest('{{{1,2},{3,4}},{{5,6},{7,8}}}'::int[]));
> array
> -------------------
> {1,2,3,4,5,6,7,8}
> (1 row)
>
> so it generate pairs {1,2}{3,4},{5,6},{7,8}
>
I fixed situation when array has not enough elements.
More tests, simple doc
Regards
Pavel
>
> Regards
>
> Pavel Stehule
>
>
>> --
>> Jim Nasby, Data Architect, Blue Treble Consulting
>> Data in Trouble? Get it in Treble! http://BlueTreble.com
>>
>
>
diff --git a/contrib/hstore/expected/hstore.out b/contrib/hstore/expected/hstore.out
new file mode 100644
index 9749e45..e44532e
*** a/contrib/hstore/expected/hstore.out
--- b/contrib/hstore/expected/hstore.out
*************** select %% 'aa=>1, cq=>l, b=>g, fg=>NULL'
*** 1148,1153 ****
--- 1148,1169 ----
{b,g,aa,1,cq,l,fg,NULL}
(1 row)
+ -- fast iteration over keys
+ do $$
+ declare
+ key text;
+ value text;
+ begin
+ foreach key, value in array hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore)
+ loop
+ raise notice 'key: %, value: %', key, value;
+ end loop;
+ end;
+ $$;
+ NOTICE: key: b, value: g
+ NOTICE: key: aa, value: 1
+ NOTICE: key: cq, value: l
+ NOTICE: key: fg, value: <NULL>
select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
hstore_to_matrix
---------------------------------
diff --git a/contrib/hstore/sql/hstore.sql b/contrib/hstore/sql/hstore.sql
new file mode 100644
index 5a9e9ee..7b9eb09
*** a/contrib/hstore/sql/hstore.sql
--- b/contrib/hstore/sql/hstore.sql
*************** select avals('');
*** 257,262 ****
--- 257,275 ----
select hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
select %% 'aa=>1, cq=>l, b=>g, fg=>NULL';
+ -- fast iteration over keys
+ do $$
+ declare
+ key text;
+ value text;
+ begin
+ foreach key, value in array hstore_to_array('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore)
+ loop
+ raise notice 'key: %, value: %', key, value;
+ end loop;
+ end;
+ $$;
+
select hstore_to_matrix('aa=>1, cq=>l, b=>g, fg=>NULL'::hstore);
select %# 'aa=>1, cq=>l, b=>g, fg=>NULL';
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
new file mode 100644
index 69a0885..4ef0299
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** NOTICE: row = {7,8,9}
*** 2490,2495 ****
--- 2490,2518 ----
NOTICE: row = {10,11,12}
</programlisting>
</para>
+
+ <para>
+ <literal>FOREACH</> cycle can be used for iteration over record. You
+ need a <xref linkend="hstore"> extension. For this case a clause
+ <literal>SLICE</literal> should not be used. <literal>FOREACH</literal>
+ statements supports list of target variables. When source array is
+ a array of composites, then composite array element is saved to target
+ variables. When the array is a array of scalar values, then target
+ variables are filled item by item.
+ <programlisting>
+ CREATE FUNCTION trig_function() RETURNS TRIGGER AS $$
+ DECLARE
+ key text; value text;
+ BEGIN
+ FOREACH key, value IN ARRAY hstore_to_array(hstore(NEW))
+ LOOP
+ RAISE NOTICE 'key = %, value = %', key, value;
+ END LOOP;
+ RETURN NEW;
+ END;
+ $$ LANGUAGE plpgsql;
+ </programlisting>
+ </para>
</sect2>
<sect2 id="plpgsql-error-trapping">
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
new file mode 100644
index ae5421f..4ab3d90
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2242,2247 ****
--- 2242,2250 ----
Datum value;
bool isnull;
+
+ bool multiassign = false;
+
/* get the value of the array expression */
value = exec_eval_expr(estate, stmt->expr, &isnull, &arrtype);
if (isnull)
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2303,2308 ****
--- 2306,2328 ----
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("FOREACH loop variable must not be of an array type")));
+ /*
+ * Proof concept -- multiassign in FOREACH cycle
+ *
+ * Motivation: FOREACH key, value IN ARRAY hstore_to_array(hstore(NEW)) ...
+ */
+ if (loop_var->dtype == PLPGSQL_DTYPE_ROW
+ && !type_is_rowtype(ARR_ELEMTYPE(arr)))
+ {
+ if (stmt->slice != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("cannot to assign non composite value to composite variable")));
+
+ /* only when target var is composite, SLICE=0 and source is scalar */
+ multiassign = true;
+ }
+
/* Create an iterator to step through the array */
array_iterator = array_create_iterator(arr, stmt->slice);
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2323,2335 ****
{
found = true; /* looped at least once */
! /* Assign current element/slice to the loop variable */
! exec_assign_value(estate, loop_var, value, iterator_result_type,
! &isnull);
! /* In slice case, value is temporary; must free it to avoid leakage */
! if (stmt->slice > 0)
! pfree(DatumGetPointer(value));
/*
* Execute the statements
--- 2343,2387 ----
{
found = true; /* looped at least once */
! if (!multiassign)
! {
! /* Assign current element/slice to the loop variable */
! exec_assign_value(estate, loop_var, value, iterator_result_type,
! &isnull);
! /* In slice case, value is temporary; must free it to avoid leakage */
! if (stmt->slice > 0)
! pfree(DatumGetPointer(value));
! }
! else
! {
! int i;
! bool first = true;
! PLpgSQL_row *row = (PLpgSQL_row *) loop_var;
!
! for (i = 0; i < row->nfields; i++)
! {
! int varno = row->varnos[i];
!
! if (varno != -1)
! {
! PLpgSQL_datum *var = (PLpgSQL_datum *) (estate->datums[varno]);
!
! if (!first)
! {
! if (!array_iterate(array_iterator, &value, &isnull))
! ereport(ERROR,
! (errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
! errmsg("array is not well sized, missing data")));
! }
! else
! first = false;
!
! exec_assign_value(estate, var, value, iterator_result_type,
! &isnull);
! }
! }
! }
/*
* Execute the statements
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
new file mode 100644
index daf3447..3b4e00e
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** NOTICE: {"(35,78)","(88,76)"}
*** 5115,5120 ****
--- 5115,5173 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+ -- multiassign (key,value) tests
+ create or replace function foreach_test_ab(anyarray)
+ returns void as $$
+ declare
+ a text; b text;
+ begin
+ foreach a,b in array $1
+ loop
+ raise notice 'a: %, b: %', a, b;
+ end loop;
+ end
+ $$ language plpgsql;
+ select foreach_test_ab(array[1,2,3,4]);
+ NOTICE: a: 1, b: 2
+ NOTICE: a: 3, b: 4
+ foreach_test_ab
+ -----------------
+
+ (1 row)
+
+ select foreach_test_ab(array[[1,2],[3,4]]);
+ NOTICE: a: 1, b: 2
+ NOTICE: a: 3, b: 4
+ foreach_test_ab
+ -----------------
+
+ (1 row)
+
+ select foreach_test_ab(array[[1,2,3]]);
+ NOTICE: a: 1, b: 2
+ ERROR: array is not well sized, missing data
+ CONTEXT: PL/pgSQL function foreach_test_ab(anyarray) line 5 at FOREACH over array
+ select foreach_test_ab('{{{1,2},{3,4}},{{5,6},{7,8}}}'::int[]);
+ NOTICE: a: 1, b: 2
+ NOTICE: a: 3, b: 4
+ NOTICE: a: 5, b: 6
+ NOTICE: a: 7, b: 8
+ foreach_test_ab
+ -----------------
+
+ (1 row)
+
+ select foreach_test_ab(array[null,null, 1,null, 1,1, null,1]);
+ NOTICE: a: <NULL>, b: <NULL>
+ NOTICE: a: 1, b: <NULL>
+ NOTICE: a: 1, b: 1
+ NOTICE: a: <NULL>, b: 1
+ foreach_test_ab
+ -----------------
+
+ (1 row)
+
+ drop function foreach_test_ab(anyarray);
--
-- Assorted tests for array subscript assignment
--
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
new file mode 100644
index a0840c9..38fc07e
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select foreach_test(ARRAY[[(10,20),(40,6
*** 4067,4072 ****
--- 4067,4093 ----
drop function foreach_test(anyarray);
drop type xy_tuple;
+ -- multiassign (key,value) tests
+ create or replace function foreach_test_ab(anyarray)
+ returns void as $$
+ declare
+ a text; b text;
+ begin
+ foreach a,b in array $1
+ loop
+ raise notice 'a: %, b: %', a, b;
+ end loop;
+ end
+ $$ language plpgsql;
+
+ select foreach_test_ab(array[1,2,3,4]);
+ select foreach_test_ab(array[[1,2],[3,4]]);
+ select foreach_test_ab(array[[1,2,3]]);
+ select foreach_test_ab('{{{1,2},{3,4}},{{5,6},{7,8}}}'::int[]);
+ select foreach_test_ab(array[null,null, 1,null, 1,1, null,1]);
+
+ drop function foreach_test_ab(anyarray);
+
--
-- Assorted tests for array subscript assignment
--
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
