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(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On 10/18/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On 10/18/07, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> "Merlin Moncure" <mmoncure(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)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(at)gmail(dot)com> writes:
> On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> writes:
> On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com> writes:
>> On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com>:
> On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > > On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)com>:
> > On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > "Merlin Moncure" <mmoncure(at)gmail(dot)com> writes:
> > > > On 10/18/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +