Hi
here is rebased patch.
It contains both patches - row_to_array function and foreach array support.
This design is in conformity with hstore functions. There can be good
synergy.
Regards
Pavel
2015-03-28 23:53 GMT+01:00 Jeff Janes <[email protected]>:
> On Tue, Jan 27, 2015 at 10:58 AM, Pavel Stehule <[email protected]>
> wrote:
>
>> 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.
>>
>
>
> This no longer applies due to conflicts in src/pl/plpgsql/src/pl_exec.c
> caused by e524cbdc45ec6d677b1dd49
>
> Also, what is the relationship of this patch to the row_to_array patch?
> Are they independent, or does one depend on the other? row_to_array by
> itself applies but doesn't compile.
>
> Cheers,
>
> Jeff
>
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 d36acf6..e4abb97
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
*************** NOTICE: row = {7,8,9}
*** 2505,2510 ****
--- 2505,2533 ----
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/backend/utils/adt/rowtypes.c b/src/backend/utils/adt/rowtypes.c
new file mode 100644
index a65e18d..1a64d8e
*** a/src/backend/utils/adt/rowtypes.c
--- b/src/backend/utils/adt/rowtypes.c
***************
*** 21,26 ****
--- 21,27 ----
#include "catalog/pg_type.h"
#include "funcapi.h"
#include "libpq/pqformat.h"
+ #include "utils/array.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
#include "utils/typcache.h"
*************** btrecordimagecmp(PG_FUNCTION_ARGS)
*** 1810,1812 ****
--- 1811,1900 ----
{
PG_RETURN_INT32(record_image_cmp(fcinfo));
}
+
+ /*
+ * transform any record to array in format [key1, value1, key2, value2 [, ...]]
+ *
+ * This format is compatible with hstore_to_array function
+ */
+ Datum
+ row_to_array(PG_FUNCTION_ARGS)
+ {
+ HeapTupleHeader rec = PG_GETARG_HEAPTUPLEHEADER(0);
+ TupleDesc rectupdesc;
+ Oid rectuptyp;
+ int32 rectuptypmod;
+ HeapTupleData rectuple;
+ int ncolumns;
+ Datum *recvalues;
+ bool *recnulls;
+ ArrayBuildState *builder;
+ int i;
+
+ /* Extract type info from the tuple itself */
+ rectuptyp = HeapTupleHeaderGetTypeId(rec);
+ rectuptypmod = HeapTupleHeaderGetTypMod(rec);
+ rectupdesc = lookup_rowtype_tupdesc(rectuptyp, rectuptypmod);
+ ncolumns = rectupdesc->natts;
+
+ /* Build a temporary HeapTuple control structure */
+ rectuple.t_len = HeapTupleHeaderGetDatumLength(rec);
+ ItemPointerSetInvalid(&(rectuple.t_self));
+ rectuple.t_tableOid = InvalidOid;
+ rectuple.t_data = rec;
+
+ recvalues = (Datum *) palloc(ncolumns * sizeof(Datum));
+ recnulls = (bool *) palloc(ncolumns * sizeof(bool));
+
+ /* Break down the tuple into fields */
+ heap_deform_tuple(&rectuple, rectupdesc, recvalues, recnulls);
+
+ /* Prepare target array */
+ builder = initArrayResult(TEXTOID, CurrentMemoryContext, true);
+
+ for (i = 0; i < ncolumns; i++)
+ {
+ Oid columntyp = rectupdesc->attrs[i]->atttypid;
+ Datum value;
+ bool isnull;
+
+ /* Ignore dropped columns */
+ if (rectupdesc->attrs[i]->attisdropped)
+ continue;
+
+ builder = accumArrayResult(builder,
+ CStringGetTextDatum(NameStr(rectupdesc->attrs[i]->attname)),
+ false,
+ TEXTOID,
+ CurrentMemoryContext);
+
+ if (!recnulls[i])
+ {
+ char *outstr;
+ bool typIsVarlena;
+ Oid typoutput;
+ FmgrInfo proc;
+
+ getTypeOutputInfo(columntyp, &typoutput, &typIsVarlena);
+ fmgr_info_cxt(typoutput, &proc, CurrentMemoryContext);
+ outstr = OutputFunctionCall(&proc, recvalues[i]);
+
+ value = CStringGetTextDatum(outstr);
+ isnull = false;
+ }
+ else
+ {
+ value = (Datum) 0;
+ isnull = true;
+ }
+
+ builder = accumArrayResult(builder,
+ value, isnull,
+ TEXTOID,
+ CurrentMemoryContext);
+ }
+
+ ReleaseTupleDesc(rectupdesc);
+
+ PG_RETURN_DATUM(makeArrayResult(builder, CurrentMemoryContext));
+ }
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
new file mode 100644
index a96d369..1b4c578
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
*************** DATA(insert OID = 376 ( string_to_array
*** 891,896 ****
--- 891,898 ----
DESCR("split delimited text into text[], with null string");
DATA(insert OID = 384 ( array_to_string PGNSP PGUID 12 1 0 0 0 f f f f f f s 3 0 25 "2277 25 25" _null_ _null_ _null_ _null_ array_to_text_null _null_ _null_ _null_ ));
DESCR("concatenate array elements, using delimiter and null string, into text");
+ DATA(insert OID = 4057 ( row_to_array PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 1009 "2249" _null_ _null_ _null_ _null_ row_to_array _null_ _null_ _null_ ));
+ DESCR("transform any record to text[]");
DATA(insert OID = 515 ( array_larger PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 2277 "2277 2277" _null_ _null_ _null_ _null_ array_larger _null_ _null_ _null_ ));
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_ ));
diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h
new file mode 100644
index 6310641..7aabfe1
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
*************** extern Datum record_image_gt(PG_FUNCTION
*** 668,673 ****
--- 668,674 ----
extern Datum record_image_le(PG_FUNCTION_ARGS);
extern Datum record_image_ge(PG_FUNCTION_ARGS);
extern Datum btrecordimagecmp(PG_FUNCTION_ARGS);
+ extern Datum row_to_array(PG_FUNCTION_ARGS);
/* ruleutils.c */
extern bool quote_all_identifiers;
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
new file mode 100644
index deefb1f..5c34a03
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2261,2266 ****
--- 2261,2269 ----
Datum value;
bool isnull;
+
+ bool multiassign = false;
+
/* get the value of the array expression */
value = exec_eval_expr(estate, stmt->expr, &isnull, &arrtype, &arrtypmod);
if (isnull)
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2322,2327 ****
--- 2325,2345 ----
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("FOREACH loop variable must not be of an array type")));
+ /*
+ * it is multiassign? Don't support slicing yet.
+ */
+ 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, NULL);
*************** exec_stmt_foreach_a(PLpgSQL_execstate *e
*** 2344,2356 ****
{
found = true; /* looped at least once */
! /* Assign current element/slice to the loop variable */
! exec_assign_value(estate, loop_var, value, isnull,
! iterator_result_type, iterator_result_typmod);
! /* In slice case, value is temporary; must free it to avoid leakage */
! if (stmt->slice > 0)
! pfree(DatumGetPointer(value));
/*
* Execute the statements
--- 2362,2406 ----
{
found = true; /* looped at least once */
! if (!multiassign)
! {
! /* Assign current element/slice to the loop variable */
! exec_assign_value(estate, loop_var, value, isnull,
! iterator_result_type, iterator_result_typmod);
! /* 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, isnull,
! iterator_result_type, iterator_result_typmod);
! }
! }
! }
/*
* Execute the statements
diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out
new file mode 100644
index 78e5a85..92d448d
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
*************** NOTICE: {"(35,78)","(88,76)"}
*** 5127,5132 ****
--- 5127,5185 ----
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/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
new file mode 100644
index 54525de..0e249be
*** a/src/test/regress/expected/rowtypes.out
--- b/src/test/regress/expected/rowtypes.out
*************** select row_to_json(r) from (select q2,q1
*** 634,636 ****
--- 634,644 ----
{"q2":0,"q1":0}
(3 rows)
+ select row_to_array(r) from (select q2,q1 from tt1 offset 0) r;
+ row_to_array
+ ------------------------------
+ {q2,456,q1,123}
+ {q2,4567890123456789,q1,123}
+ {q2,0,q1,0}
+ (3 rows)
+
diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql
new file mode 100644
index e19e415..7640f5d
*** a/src/test/regress/sql/plpgsql.sql
--- b/src/test/regress/sql/plpgsql.sql
*************** select foreach_test(ARRAY[[(10,20),(40,6
*** 4075,4080 ****
--- 4075,4101 ----
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
--
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
new file mode 100644
index bc3f021..3450417
*** a/src/test/regress/sql/rowtypes.sql
--- b/src/test/regress/sql/rowtypes.sql
*************** create temp table tt1 as select * from i
*** 271,273 ****
--- 271,274 ----
create temp table tt2 () inherits(tt1);
insert into tt2 values(0,0);
select row_to_json(r) from (select q2,q1 from tt1 offset 0) r;
+ select row_to_array(r) from (select q2,q1 from tt1 offset 0) r;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
