Thread: Proposal: generate_iterator functions
Hello this function can help with array's iteration. create function generate_iterator(anyarray) returns setof integer as $$ select i from generate_series(array_lower($1,1), array_upper($1,1)) g(i) $$ language sql; -- multidimensional create function generate_iterator(anyarray, integer) returns setof integer as $$ select generate_series(array_lower($1,$2), array_upper($1,$2)) g(i) $$ language sql; It can be internal function, not only shortcut for generate_series sample: create function array_sort(anyarray) returns anyarray as $$ select array(select $1[i] from generate_iterator($1) order by 1) $$ language sql;
On 10/18/07, Pavel Stehule <pavel.stehule@gmail.com> wrote: > this function can help with array's iteration. > > create function generate_iterator(anyarray) > returns setof integer > as $$ > select i > from generate_series(array_lower($1,1), > array_upper($1,1)) g(i) > $$ language sql; There was a very similar proposal a little while back (google: array_to_set). I think I like those names better since you are returning a set, not an iterator :-). Also, this should be internal as you suggest (there is an undocumented builtin that already does this, _pg_expandarray). merlin
2007/10/18, Merlin Moncure <mmoncure@gmail.com>: > On 10/18/07, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > this function can help with array's iteration. > > > > create function generate_iterator(anyarray) > > returns setof integer > > as $$ > > select i > > from generate_series(array_lower($1,1), > > array_upper($1,1)) g(i) > > $$ language sql; > > There was a very similar proposal a little while back (google: > array_to_set). I think I like those names better since you are > returning a set, not an iterator :-). Also, this should be internal > as you suggest (there is an undocumented builtin that already does > this, _pg_expandarray). > I remember. There is only one important difference. What is behave of array_to_set with multidim. array? the name "generate_iterator" is my first idea (it retunrs set but setof indexes). I am sure so there are better names :) Pavel
2007/10/18, Merlin Moncure <mmoncure@gmail.com>: > On 10/18/07, Pavel Stehule <pavel.stehule@gmail.com> wrote: > > this function can help with array's iteration. > > > > create function generate_iterator(anyarray) > > returns setof integer > > as $$ > > select i > > from generate_series(array_lower($1,1), > > array_upper($1,1)) g(i) > > $$ language sql; > > There was a very similar proposal a little while back (google: > array_to_set). I think I like those names better since you are > returning a set, not an iterator :-). Also, this should be internal > as you suggest (there is an undocumented builtin that already does > this, _pg_expandarray). > > merlin > one sample: create or replace function array_unpack2(anyarray) returns setof anyelement as $$ select $1[i][j] from generate_iterator($1,1) i, generate_iterator($1,2) j$$ language sql; postgres=# select array_unpack2(ARRAY[[10,11,12],[13,14,15]]);array_unpack2 --------------- 10 11 12 13 14 15 (6 rows)
"Merlin Moncure" <mmoncure@gmail.com> writes:
> There was a very similar proposal a little while back (google:
> array_to_set). I think I like those names better since you are
> returning a set, not an iterator :-).
I agree, this is a very poor choice of name. There should be some
reference to arrays in it, for one thing.
generate_array_subscripts() maybe?
regards, tom lane
2007/10/18, Tom Lane <tgl@sss.pgh.pa.us>: > "Merlin Moncure" <mmoncure@gmail.com> writes: > > There was a very similar proposal a little while back (google: > > array_to_set). I think I like those names better since you are > > returning a set, not an iterator :-). > > I agree, this is a very poor choice of name. There should be some > reference to arrays in it, for one thing. > > generate_array_subscripts() maybe? > why not? Pavel
On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Merlin Moncure" <mmoncure@gmail.com> writes: > > There was a very similar proposal a little while back (google: > > array_to_set). I think I like those names better since you are > > returning a set, not an iterator :-). > > I agree, this is a very poor choice of name. There should be some > reference to arrays in it, for one thing. > > generate_array_subscripts() maybe? array_to_set or array_expand seem a little better imo (shorter, and symmetry with array_accum()), unless you want to differentiate between internal funcs (array_cat and the like) vs. user funcs. I would prefer a proper C implementation to a solution based around generate_series(). I'm doing a lot of C funcs lately and would be happy taking a stab at this... merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> generate_array_subscripts() maybe?
> array_to_set or array_expand seem a little better imo (shorter, and
> symmetry with array_accum()), unless you want to differentiate between
> internal funcs (array_cat and the like) vs. user funcs.
I don't much like either of those, because they seem misleading:
what I'd expect from a function named that way is that it returns
the *elements* of the array, not their subscripts.
Come to think of it, do we have a way of doing that directly? If you
only care about accessing the array elements, it seems like dealing in
the subscripts is just notational tedium. Perhaps there should be
array_expand(anyarray) returns setof anyelement, in addition to the
subscript generation function.
On the question of being too long, I could live with
generate_subscripts().
regards, tom lane
On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > I don't much like either of those, because they seem misleading: > what I'd expect from a function named that way is that it returns > the *elements* of the array, not their subscripts. > > Come to think of it, do we have a way of doing that directly? If you > only care about accessing the array elements, it seems like dealing in > the subscripts is just notational tedium. Perhaps there should be > array_expand(anyarray) returns setof anyelement, in addition to the > subscript generation function. > > On the question of being too long, I could live with > generate_subscripts(). how about array_iota? merlin
"Merlin Moncure" <mmoncure@gmail.com> writes:
> On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> On the question of being too long, I could live with
>> generate_subscripts().
> how about array_iota?
I think a lot of people wouldn't get the reference. How about
array_subscripts()?
regards, tom lane
Tom Lane wrote: > "Merlin Moncure" <mmoncure@gmail.com> writes: >> On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: >>> generate_array_subscripts() maybe? > >> array_to_set or array_expand seem a little better imo (shorter, and >> symmetry with array_accum()), unless you want to differentiate between >> internal funcs (array_cat and the like) vs. user funcs. > > I don't much like either of those, because they seem misleading: > what I'd expect from a function named that way is that it returns > the *elements* of the array, not their subscripts. > > Come to think of it, do we have a way of doing that directly? If you > only care about accessing the array elements, it seems like dealing in > the subscripts is just notational tedium. Perhaps there should be > array_expand(anyarray) returns setof anyelement, in addition to the > subscript generation function. I think what you're describing is the SQL2003 UNNEST feature. Joe
On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Merlin Moncure" <mmoncure@gmail.com> writes: > > On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> On the question of being too long, I could live with > >> generate_subscripts(). > > > how about array_iota? > > I think a lot of people wouldn't get the reference. How about > array_subscripts()? works for me.. merlin
done http://www.pgsql.cz/index.php/Iter%C3%A1tor_pole I'll send patch later Pavel 2007/10/18, Merlin Moncure <mmoncure@gmail.com>: > On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > "Merlin Moncure" <mmoncure@gmail.com> writes: > > > On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > >> On the question of being too long, I could live with > > >> generate_subscripts(). > > > > > how about array_iota? > > > > I think a lot of people wouldn't get the reference. How about > > array_subscripts()? > > > works for me.. > > merlin >
I see you just submitted the patch so we will address it after the commit fest --- thanks. --------------------------------------------------------------------------- Pavel Stehule wrote: > done > > http://www.pgsql.cz/index.php/Iter%C3%A1tor_pole > > I'll send patch later > > Pavel > > 2007/10/18, Merlin Moncure <mmoncure@gmail.com>: > > On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > "Merlin Moncure" <mmoncure@gmail.com> writes: > > > > On 10/18/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > > >> On the question of being too long, I could live with > > > >> generate_subscripts(). > > > > > > > how about array_iota? > > > > > > I think a lot of people wouldn't get the reference. How about > > > array_subscripts()? > > > > > > works for me.. > > > > merlin > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +