SEO

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;

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster