Hello
here is a initial version of row_to_array function - transform any row to
array in format proposed by Andrew.
Regards
Pavel
2015-01-27 19:58 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
> Hi
>
> 2015-01-27 11:41 GMT+01:00 Pavel Stehule <pavel.steh...@gmail.com>:
>
>>
>>
>> 2015-01-26 21:44 GMT+01:00 Jim Nasby <jim.na...@bluetreble.com>:
>>
>>> 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/src/backend/utils/adt/rowtypes.c b/src/backend/utils/adt/rowtypes.c
new file mode 100644
index 3dc9a84..d758d2d
*** 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,1898 ----
{
PG_RETURN_INT32(record_image_cmp(fcinfo));
}
+
+ /*
+ * transform any record to array in format [key1, value1, key2, value2 [, ...]]
+ */
+ 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);
+
+ 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 9edfdb8..a27cf4a
*** 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 bc4517d..315a1fc
*** 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/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/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 (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
