Lists: | pgsql-hackers |
---|
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Proposal: generate_iterator functions |
Date: | 2007-10-18 05:20:29 |
Message-ID: | 162867790710172220n1ab51d83kc9392f0ed6178cdb@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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;
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 12:02:12 |
Message-ID: | b42b73150710180502h4e9582f6y61cab48671e212b9@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 12:21:16 |
Message-ID: | 162867790710180521s66bcedc9scae41f8d04ac6094@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 12:45:33 |
Message-ID: | 162867790710180545m1d812dd8tf7d966fd7904c54@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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)
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 13:56:38 |
Message-ID: | 22538.1192715798@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"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
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Merlin Moncure" <mmoncure(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 14:09:36 |
Message-ID: | 162867790710180709g144c8c62w35ae040548ce0958@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 18:49:21 |
Message-ID: | b42b73150710181149s1bb8841cx24de3af8af398641@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 19:08:51 |
Message-ID: | 27004.1192734531@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"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
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 19:13:28 |
Message-ID: | b42b73150710181213l44bbff40u74313aef69758970@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 19:20:02 |
Message-ID: | 27251.1192735202@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
"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
From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 19:20:16 |
Message-ID: | 4717B1F0.4040707@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 20:33:06 |
Message-ID: | b42b73150710181333h6cd95782p992131b4230dd788@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
From: | "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2007-10-18 22:00:46 |
Message-ID: | 162867790710181500g673b18f0ja23d93a2999c9a40@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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
>
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | Merlin Moncure <mmoncure(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Proposal: generate_iterator functions |
Date: | 2008-03-25 00:08:24 |
Message-ID: | 200803250008.m2P08On17978@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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. +