Hi
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?
Regards
Pavel
2015-01-16 22:35 GMT+01:00 Andrew Dunstan <[email protected]>:
>
> On 01/16/2015 12:22 PM, Pavel Stehule wrote:
>
>>
>>
>> There two possible transformations:
>>
>> row_to_array --> [[key1, value1],[key2, value2], ...]
>> row_to_row_array --> [(key1, value1), (key2, value2), ... ]
>>
>>
>> If we're going to go that route, I think it makes more sense to
>> create an actual key/value type (ie:
>> http://pgxn.org/dist/pair/doc/pair.html) and return an array of that.
>>
>>
>> ok
>>
>> <http://BlueTreble.com>
>>
>>
>
> I think we'd possibly be better off with simply returning a flat array,
> [key1, value1, ...]
>
> Thats's what the hstore(text[]) and json_object(text[]) functions accept,
> along with the 2D variant, if we want a precedent.
>
> cheers
>
> andrew
>
>
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/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
new file mode 100644
index ae5421f..e250b32
*** 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,2382 ----
{
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)
! array_iterate(array_iterator, &value, &isnull);
! else
! first = false;
!
! exec_assign_value(estate, var, value, iterator_result_type,
! &isnull);
! }
! }
! }
/*
* Execute the statements
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
