Re: proposal: row_to_array function

2015-01-28 6:49 GMT+01:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:

>
> Dne 28.1.2015 0:25 "Jim Nasby" <Jim(dot)Nasby(at)bluetreble(dot)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
> 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.
> >
> > --
> > Jim Nasby, Data Architect, Blue Treble Consulting
> > Data in Trouble? Get it in Treble! http://BlueTreble.com
>