proposal: row_to_array function

On 1/16/15 3:45 AM, Pavel Stehule wrote:

I am returning back to processing records in plpgsql.

I am thinking so it can be simply processed with transformations to array.

Now we have similar functions - hstore(row), row_to_json, ... but using of these functions can be a useless step. Any row variable can be transformed to 2D text array.

How is it useless? Why wouldn't you just use JSON and be done with it?

Do you have some use cases you can share?

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.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-01-16 18:03 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 1/16/15 3:45 AM, Pavel Stehule wrote:

I am returning back to processing records in plpgsql.

I am thinking so it can be simply processed with transformations to array.

Now we have similar functions - hstore(row), row_to_json, ... but using
of these functions can be a useless step. Any row variable can be
transformed to 2D text array.

How is it useless? Why wouldn't you just use JSON and be done with it?

We can use a FOREACH IN ARRAY iteration in plpgsql (second variant is a
implementation FOREACH for jsonb)

so ROW->ARRAY is shorter than ROW->JSON->ARRAY or ROW->HSTORE->ARRAY

Do you have some use cases you can share?

processing of NEW, OLD variables in triggers

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

On 1/16/15 11:22 AM, Pavel Stehule wrote:

2015-01-16 18:03 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com <mailto:Jim.Nasby@bluetreble.com>>:

On 1/16/15 3:45 AM, Pavel Stehule wrote:

I am returning back to processing records in plpgsql.

I am thinking so it can be simply processed with transformations to array.

Now we have similar functions - hstore(row), row_to_json, ... but using of these functions can be a useless step. Any row variable can be transformed to 2D text array.

How is it useless? Why wouldn't you just use JSON and be done with it?

We can use a FOREACH IN ARRAY iteration in plpgsql (second variant is a implementation FOREACH for jsonb)

so ROW->ARRAY is shorter than ROW->JSON->ARRAY or ROW->HSTORE->ARRAY

I think the real problem here is that we're inventing a bunch of different ways to do the same thing: iterate over a set. Instead of doing that, should we add the idea of an iterator to the type system? That would make sense for arrays, hstore, json and XML.

Do you have some use cases you can share?

processing of NEW, OLD variables in triggers

Note that last time I checked you couldn't do something like NEW.variable, and I don't think you could use EXEC to do it either. So there's more needed here than just converting a record to an array.

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 <http://pgxn.org/dist/pair/doc/pair.html&gt;) and return an array of that.

ok

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-01-16 18:42 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 1/16/15 11:22 AM, Pavel Stehule wrote:

2015-01-16 18:03 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com <mailto:
Jim.Nasby@bluetreble.com>>:

On 1/16/15 3:45 AM, Pavel Stehule wrote:

I am returning back to processing records in plpgsql.

I am thinking so it can be simply processed with transformations
to array.

Now we have similar functions - hstore(row), row_to_json, ... but
using of these functions can be a useless step. Any row variable can be
transformed to 2D text array.

How is it useless? Why wouldn't you just use JSON and be done with it?

We can use a FOREACH IN ARRAY iteration in plpgsql (second variant is a
implementation FOREACH for jsonb)

so ROW->ARRAY is shorter than ROW->JSON->ARRAY or ROW->HSTORE->ARRAY

I think the real problem here is that we're inventing a bunch of different
ways to do the same thing: iterate over a set. Instead of doing that,
should we add the idea of an iterator to the type system? That would make
sense for arrays, hstore, json and XML.

what do you think? How this can be implemented?

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&gt;

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-01-16 22:35 GMT+01:00 Andrew Dunstan <andrew@dunslane.net>:

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&gt;

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.

It can be one of supported variant. I should not be one, because we cannot
to simply iterate over it

Next possibility is teach FOREACH to take key and value in one step.

Regards

Pavel

2015-01-17 7:26 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-01-16 22:35 GMT+01:00 Andrew Dunstan <andrew@dunslane.net>:

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&gt;

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.

It can be one of supported variant. I should not be one, because we cannot
to simply iterate over it

Next possibility is teach FOREACH to take key and value in one step.

I looked to code and iteration over pair (key, value) is more simple

FOREACH supports target list, but source should be composite array.

ostgres=# do $$
declare a int;
b int;
begin
foreach a,b in array ARRAY[(1,2),(3,4)]
loop
raise notice 'a = %, b = %', a,b;
end loop;
end;
$$ language plpgsql;
NOTICE: a = 1, b = 2
NOTICE: a = 3, b = 4
DO

Conversion from ARRAY[k1,v1,k2,v2, ... ] is not well consistent with
current design

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 <andrew@dunslane.net>:

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)) ?

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[] ?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2015-01-26 21:44 GMT+01:00 Jim Nasby <Jim.Nasby@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}

Regards

Pavel Stehule

Hi

2015-01-27 11:41 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-01-26 21:44 GMT+01:00 Jim Nasby <Jim.Nasby@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

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.stehule@gmail.com>:

Example:

postgres=# do $$
declare r record;
declare k text; v text;
begin
for r in select * from foo loop
foreach k,v in array row_to_array(r) loop
raise notice 'k: %, v: %', k, v;
end loop;
end loop;
end;
$$;
NOTICE: k: a, v: 2
NOTICE: k: b, v: NAZDAR
NOTICE: k: c, v: 2015-01-27
NOTICE: k: a, v: 2
NOTICE: k: b, v: AHOJ
NOTICE: k: c, v: 2015-01-27
DO

Regards

Pavel

2015-01-27 21:26 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

On 1/27/15 2:26 PM, Pavel Stehule wrote:

here is a initial version of row_to_array function - transform any row to array in format proposed by Andrew.

Please start a new thread for this... does it depend on the key-value patch?
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 1/27/15 12:58 PM, Pavel Stehule wrote:

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

Hrm, this wasn't what I was expecting:

+ select foreach_test_ab(array[1,2,3,4]);
+ NOTICE:  a: 1, b: 2
+ NOTICE:  a: 3, b: 4

I was expecting that foreach a,b array would be expecting something in the array to have a dimension of 2. :(

I think this is bad, because this:

foreach_test_ab('{{1,2,3},{4,5,6}}'::int[]);

will give you 1,2; 3,4; 5,6. I don't see any way that that makes sense. Even if it did make sense, I'm more concerned that adding this will seriously paint us into a corner when it comes to the (to me) more rational case of returning {1,2,3},{4,5,6}.

I think we need to think some more about this, at least to make sure we're not painting ourselves into a corner for more appropriate array iteration.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dne 28.1.2015 0:25 "Jim Nasby" <Jim.Nasby@bluetreble.com> napsal(a):

On 1/27/15 12:58 PM, Pavel Stehule wrote:

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

Hrm, this wasn't what I was expecting:

+ select foreach_test_ab(array[1,2,3,4]);
+ NOTICE:  a: 1, b: 2
+ NOTICE:  a: 3, b: 4

I was expecting that foreach a,b array would be expecting something in

the array to have a dimension of 2. :(

It is inconsist (your expectation) with current implementation of FOREACH.
It doesnt produce a array when SLICING is missing. And it doesnt calculate
with dimensions.

I would not to change this rule. It is not ambigonuous and it allows to
work with
1d, 2d, 3d dimensions array. You can process Andrew format well and my
proposed format (2d array) well too.

There can be differen behave when SLICING is used. There we can iterate
exactly with dimensions. We can design a behave in this case?

I think this is bad, because this:

foreach_test_ab('{{1,2,3},{4,5,6}}'::int[]);

will give you 1,2; 3,4; 5,6. I don't see any way that that makes sense.

Even if it did make sense, I'm more concerned that adding this will
seriously paint us into a corner when it comes to the (to me) more rational
case of returning {1,2,3},{4,5,6}.

I think we need to think some more about this, at least to make sure

we're not painting ourselves into a corner for more appropriate array
iteration.

2015-01-28 0:16 GMT+01:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 1/27/15 2:26 PM, Pavel Stehule wrote:

here is a initial version of row_to_array function - transform any row to
array in format proposed by Andrew.

Please start a new thread for this... does it depend on the key-value
patch?

partially - a selected format should be well supported by FOREACH statement

Regards

Pavel

2015-01-28 6:49 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

Dne 28.1.2015 0:25 "Jim Nasby" <Jim.Nasby@bluetreble.com> napsal(a):

On 1/27/15 12:58 PM, Pavel Stehule wrote:

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

Hrm, this wasn't what I was expecting:

+ select foreach_test_ab(array[1,2,3,4]);
+ NOTICE:  a: 1, b: 2
+ NOTICE:  a: 3, b: 4

I was expecting that foreach a,b array would be expecting something in

the array to have a dimension of 2. :(

It is inconsist (your expectation) with current implementation of FOREACH.
It doesnt produce a array when SLICING is missing. And it doesnt calculate
with dimensions.

I would not to change this rule. It is not ambigonuous and it allows to
work with
1d, 2d, 3d dimensions array. You can process Andrew format well and my
proposed format (2d array) well too.

one small example

CREATE OR REPLACE FUNCTION iterate_over_pairs(text[])
RETURNS void AS $$
DECLARE v1 text; v2 text; e text; i int := 0;
BEGIN
FOREACH e IN ARRAY $1 LOOP
IF i % 2 = 0 THEN v1 := e;
ELSE v2 := e; RAISE NOTICE 'v1: %, v2: %', v1, v2; END IF;
i := i + 1;
END LOOP;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT iterate_over_pairs(ARRAY[1,2,3,4]::text[]);
NOTICE: v1: 1, v2: 2
NOTICE: v1: 3, v2: 4
iterate_over_pairs
--------------------

(1 row)

postgres=# SELECT iterate_over_pairs(ARRAY[[1,2],[3,4]]::text[]);
NOTICE: v1: 1, v2: 2
NOTICE: v1: 3, v2: 4
iterate_over_pairs
--------------------

(1 row)

I can use iterate_over_pairs for 1D or 2D arrays well -- a FOREACH was
designed in this direction - without SLICE a dimensions data are
unimportant.

Discussed enhancing of FOREACH is faster and shorter (readable)
iterate_over_pairs use case.

FOREACH v1, v2 IN ARRAY $1 LOOP
..
END LOOP;

It is consistent with current design

You can look to patch - in this moment a SLICE > 0 is disallowed for
situation, when target variable is ROW and source is not ROW.

Regards

Pavel

There can be differen behave when SLICING is used. There we can iterate

On Tue, Jan 27, 2015 at 10:58 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

2015-01-27 11:41 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2015-01-26 21:44 GMT+01:00 Jim Nasby <Jim.Nasby@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.

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