Re: proposal: FOREACH-IN-ARRAY (probably for 9.2?)

Hello

I am resending a redesigned proposal about special plpgsql statement
that support iteration over an array.

The most conflict issue of last proposal was a syntax. It enhanced
relative complex FOR statement. So now, it's based on new statement
with simple syntax. We can use a keyword FOREACH, this isn't in
conflict with PL/SQL - use a keyword FORALL and it isn't in conflict
with SQL/PSM too. More - this special statement can be used for
PostgreSQL's specific purposes. It can carry a new features in future.
The design of proposed functionality is simple, but respects a
possibility for enhancing a FOREACH cycle for future.

==proposed syntax:==

[ <<label>> ]
FOREACH var [, var [..]] IN ARRAY expr
LOOP
...
END LOOP [ label ]

==the goals:==
* cleaner syntax for full iteration over array
* reduce a overhead from only seq. access to any field in array
(it's not too significant)
* simplify iteration over multidimensional arrays

The most performance issue of access to a untoasted array is "solved"
with other patch.

== Iteration over multidimensional arrays ==
Its designed to reduce one dimension from source array. It can remove
a slicing and simplify code:

CREATE OR REPLACE FUNCTION public.fa(anyarray)
RETURNS void
LANGUAGE plpgsql
AS $function$
DECLARE i int[];
BEGIN
FOREACH i IN ARRAY $1
LOOP
RAISE NOTICE '%', i;
END LOOP;
END;
$function$

postgres=# select fa(array[[[1,2],[3,4]],[[1,2],[3,4]],[[5,6],[7,8]]]);
NOTICE: {{1,2},{3,4}}
NOTICE: {{1,2},{3,4}}
NOTICE: {{5,6},{7,8}}
fa
----

(1 row)

postgres=# select fa(array[[1,2,3,4],[1,2,3,4],[5,6,7,8]]);
NOTICE: {1,2,3,4}
NOTICE: {1,2,3,4}
NOTICE: {5,6,7,8}
fa
----

(1 row)

ideas, notes?

Regards

Pavel

Excerpts from Pavel Stehule's message of jue dic 16 16:19:17 -0300 2010:

> The most performance issue of access to a untoasted array is "solved"
> with other patch.

Was the other patch applied?

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

2010/12/17 Alvaro Herrera <alvherre(at)commandprompt(dot)com>:
> Excerpts from Pavel Stehule's message of jue dic 16 16:19:17 -0300 2010:
>
>> The most performance issue of access to a untoasted  array is "solved"
>> with other patch.
>
> Was the other patch applied?
>

no, it's in queue for next commitfest

https://commitfest.postgresql.org/action/patch_view?id=440

Regards

Pavel

> --
> Álvaro Herrera <alvherre(at)commandprompt(dot)com>
> The PostgreSQL Company - Command Prompt, Inc.
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> I am resending a redesigned proposal about special plpgsql statement
> that support iteration over an array.

OK ...

> == Iteration over multidimensional arrays ==
> Its designed to reduce one dimension from source array. It can remove
> a slicing and simplify code:

This seems like a really bad, confusing idea. I think it should throw
a type-mismatch error in this case. If there is any use-case for such a
thing, which I'm quite unconvinced of, it ought to use a separate syntax
rather than overloading the element-by-element syntax.

regards, tom lane

On Fri, Dec 17, 2010 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I am resending a redesigned proposal about special plpgsql statement
>> that support iteration over an array.
>
> OK ...
>
>> == Iteration over multidimensional arrays ==
>> Its designed to reduce one dimension from source array. It can remove
>> a slicing and simplify code:
>
> This seems like a really bad, confusing idea.  I think it should throw
> a type-mismatch error in this case.  If there is any use-case for such a
> thing, which I'm quite unconvinced of, it ought to use a separate syntax
> rather than overloading the element-by-element syntax.

I don't agree at all -- iterating arrays by slice is a frequently
requested feature (you can kinda sorta do it by slice notation, but
arr[n] giving null is a -general FAQ. This is how people think arrays
should work. I suppose that having this functionality reserved in a
tiny corner of plpgsql is not so good, but I think foreach... would
become the preferred way to iterate arrays always.

merlin

2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> I am resending a redesigned proposal about special plpgsql statement
>> that support iteration over an array.
>
> OK ...
>
>> == Iteration over multidimensional arrays ==
>> Its designed to reduce one dimension from source array. It can remove
>> a slicing and simplify code:
>
> This seems like a really bad, confusing idea.  I think it should throw
> a type-mismatch error in this case.  If there is any use-case for such a
> thing, which I'm quite unconvinced of, it ought to use a separate syntax
> rather than overloading the element-by-element syntax.

Without this feature any iteration over 2d and more dimensional array
is not practical. If I have a 2D array, then I would to get a vector.
Access to individual values can be to limiting, because I need a more
cycles to get a complete vector. Usually I can use a array of row
instead a 2d array, but still and in feature there is problem with
iteration over row. So sometime is more practical to use a 2d array.

Actually It raise a type mismatch error, when a user used a scalar
variable and data is a vector (array)

Pavel

>
>                        regards, tom lane
>

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Fri, Dec 17, 2010 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This seems like a really bad, confusing idea. I think it should throw
>> a type-mismatch error in this case. If there is any use-case for such a
>> thing, which I'm quite unconvinced of, it ought to use a separate syntax
>> rather than overloading the element-by-element syntax.

> I don't agree at all -- iterating arrays by slice is a frequently
> requested feature (you can kinda sorta do it by slice notation, but
> arr[n] giving null is a -general FAQ. This is how people think arrays
> should work. I suppose that having this functionality reserved in a
> tiny corner of plpgsql is not so good, but I think foreach... would
> become the preferred way to iterate arrays always.

Well, okay, if it's useful we can have it, but I still say it needs to
be a separate syntax. The example Pavel gives looks like nothing so
much as a beginner's error, ie putting [] on the target variable when
he shouldn't have.

Furthermore, it's underspecified: who's to say how many dimensions of
the array are supposed to get sliced off? There's no reasonable place
to extend this syntax to specify that. It will also be inconsistent
for "foreach scalar in array" to iterate element-by-element no matter
how many dimensions array has, while "foreach array in array" does
something different from that.

regards, tom lane

2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> On Fri, Dec 17, 2010 at 10:47 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> This seems like a really bad, confusing idea.  I think it should throw
>>> a type-mismatch error in this case.  If there is any use-case for such a
>>> thing, which I'm quite unconvinced of, it ought to use a separate syntax
>>> rather than overloading the element-by-element syntax.
>
>> I don't agree at all -- iterating arrays by slice is a frequently
>> requested feature (you can kinda sorta do it by slice notation, but
>> arr[n] giving null is a -general FAQ.  This is how people think arrays
>> should work.  I suppose that having this functionality reserved in a
>> tiny corner of plpgsql is not so good, but I think foreach... would
>> become the preferred way to iterate arrays always.
>
> Well, okay, if it's useful we can have it, but I still say it needs to
> be a separate syntax.  The example Pavel gives looks like nothing so
> much as a beginner's error, ie putting [] on the target variable when
> he shouldn't have.

Now the message is unclean - but it can be enhanced. We can a diagnose
situation when result is multidimensional array and target isn't
array, and the we can to throw user friendly message.

>
> Furthermore, it's underspecified: who's to say how many dimensions of
> the array are supposed to get sliced off?  There's no reasonable place
> to extend this syntax to specify that.  It will also be inconsistent
> for "foreach scalar in array" to iterate element-by-element no matter
> how many dimensions array has, while "foreach array in array" does
> something different from that.
>

it reduce just one dimension. Now I expect, and I think so it is
correct, so user knows a used dimension. Just doesn't know a data. So
user can to decide and fill correct type. The design strictly remove
any U.I. from design. So using a incorect type is bug.

Because a FOREACH syntax is new, we can to enhance it to possible direction:

FOREACH VALUE var IN ARRAY expr
LOOP
END LOOP

and then it will iterate per one field without a dimension reduction.
So this possibility is available and I think so could be implemented
too.

Pavel

>                        regards, tom lane
>

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> Furthermore, it's underspecified: who's to say how many dimensions of
>> the array are supposed to get sliced off? There's no reasonable place
>> to extend this syntax to specify that. It will also be inconsistent
>> for "foreach scalar in array" to iterate element-by-element no matter
>> how many dimensions array has, while "foreach array in array" does
>> something different from that.

> it reduce just one dimension. Now I expect, and I think so it is
> correct, so user knows a used dimension. Just doesn't know a data. So
> user can to decide and fill correct type. The design strictly remove
> any U.I. from design. So using a incorect type is bug.

In other words, your proposal is error-prone to use, restricted in what
it can do, and incapable of being extended later without breaking
things. If there is some redeeming social value to set against those
problems, I'm not seeing it.

What I think we should have is

FOREACH scalar-variable IN ARRAY array-expression

which iterates element by element regardless of how many dimensions the
array has. Then there should be some other syntax for iterating over
slices, and we should give some thought to being able to specify how
"deep" the slice is. I can definitely think of use cases for pulling
off either 1 dimension at a time (so you get vectors) or N-1 dimensions
at a time, and it's not out of the realm of reason to want intermediate
cases.

Maybe

FOR_EACH scalar-variable IN ARRAY array-expression

FOR_SLICE array-variable [DEPTH n] IN ARRAY array-expression

Or I guess you could use the same leading keyword if you make the depth
specification mandatory for the slice case:

FOREACH scalar-variable IN ARRAY array-expression

FOREACH array-variable SLICE n IN ARRAY array-expression

That might be a better idea since it avoids the inevitable argument over
whether the default slice depth should be 1 dimension or N-1 dimensions.

regards, tom lane

On Fri, Dec 17, 2010 at 11:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> Furthermore, it's underspecified: who's to say how many dimensions of
>>> the array are supposed to get sliced off?  There's no reasonable place
>>> to extend this syntax to specify that.  It will also be inconsistent
>>> for "foreach scalar in array" to iterate element-by-element no matter
>>> how many dimensions array has, while "foreach array in array" does
>>> something different from that.
>
>> it reduce just one dimension. Now I expect, and I think so it is
>> correct, so user knows a used dimension. Just doesn't know a data. So
>> user can to decide and fill correct type. The design strictly remove
>> any U.I. from design. So using a incorect type is bug.
>
> In other words, your proposal is error-prone to use, restricted in what
> it can do, and incapable of being extended later without breaking
> things.  If there is some redeeming social value to set against those
> problems, I'm not seeing it.
>
> What I think we should have is
>
>        FOREACH scalar-variable IN ARRAY array-expression
>
> which iterates element by element regardless of how many dimensions the
> array has.  Then there should be some other syntax for iterating over
> slices, and we should give some thought to being able to specify how
> "deep" the slice is.  I can definitely think of use cases for pulling
> off either 1 dimension at a time (so you get vectors) or N-1 dimensions
> at a time, and it's not out of the realm of reason to want intermediate
> cases.
>
> Maybe
>
>        FOR_EACH scalar-variable IN ARRAY array-expression
>
>        FOR_SLICE array-variable [DEPTH n] IN ARRAY array-expression
>
> Or I guess you could use the same leading keyword if you make the depth
> specification mandatory for the slice case:
>
>        FOREACH scalar-variable IN ARRAY array-expression
>
>        FOREACH array-variable SLICE n IN ARRAY array-expression
>
> That might be a better idea since it avoids the inevitable argument over
> whether the default slice depth should be 1 dimension or N-1 dimensions.

another way:

FOREACH scalar IN ARRAY arr_exp DIMS in dim_var

dim_var being int[], or possibly text, of length #dimensions, giving
per dimesion index.

I like this because it would fit well with alternate form of unnest,
should it ever be written:

create function unnest(anyarray, dims out int[], elem out anyelement)
returns setof...

SLICE notation is still good though, and it's probably faster since
you have less work to do in iteration step? It's certainly easier,
but very plpgsql specific.

merlin

2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>> Furthermore, it's underspecified: who's to say how many dimensions of
>>> the array are supposed to get sliced off?  There's no reasonable place
>>> to extend this syntax to specify that.  It will also be inconsistent
>>> for "foreach scalar in array" to iterate element-by-element no matter
>>> how many dimensions array has, while "foreach array in array" does
>>> something different from that.
>
>> it reduce just one dimension. Now I expect, and I think so it is
>> correct, so user knows a used dimension. Just doesn't know a data. So
>> user can to decide and fill correct type. The design strictly remove
>> any U.I. from design. So using a incorect type is bug.
>
> In other words, your proposal is error-prone to use, restricted in what
> it can do, and incapable of being extended later without breaking
> things.  If there is some redeeming social value to set against those
> problems, I'm not seeing it.
>
> What I think we should have is
>
>        FOREACH scalar-variable IN ARRAY array-expression
>
> which iterates element by element regardless of how many dimensions the
> array has.  Then there should be some other syntax for iterating over
> slices, and we should give some thought to being able to specify how
> "deep" the slice is.  I can definitely think of use cases for pulling
> off either 1 dimension at a time (so you get vectors) or N-1 dimensions
> at a time, and it's not out of the realm of reason to want intermediate
> cases.

I am not against

>
> Maybe
>
>        FOR_EACH scalar-variable IN ARRAY array-expression
>
>        FOR_SLICE array-variable [DEPTH n] IN ARRAY array-expression
>
> Or I guess you could use the same leading keyword if you make the depth
> specification mandatory for the slice case:
>
>        FOREACH scalar-variable IN ARRAY array-expression
>
>        FOREACH array-variable SLICE n IN ARRAY array-expression
>

I prefer FOREACH keyword. The syntax can be enhanced and I like a talk
about it. I am not sure if SLICE is good keyword for this, but I don't
know better - hope so native speakers can select well. I could to use
maybe "DIMENSIONS" ?

Regards

Pavel

> That might be a better idea since it avoids the inevitable argument over
> whether the default slice depth should be 1 dimension or N-1 dimensions.
>
>                        regards, tom lane
>

2010/12/17 Merlin Moncure <mmoncure(at)gmail(dot)com>

> On Fri, Dec 17, 2010 at 11:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> >> 2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> >>> Furthermore, it's underspecified: who's to say how many dimensions of
> >>> the array are supposed to get sliced off? There's no reasonable place
> >>> to extend this syntax to specify that. It will also be inconsistent
> >>> for "foreach scalar in array" to iterate element-by-element no matter
> >>> how many dimensions array has, while "foreach array in array" does
> >>> something different from that.
> >
> >> it reduce just one dimension. Now I expect, and I think so it is
> >> correct, so user knows a used dimension. Just doesn't know a data. So
> >> user can to decide and fill correct type. The design strictly remove
> >> any U.I. from design. So using a incorect type is bug.
> >
> > In other words, your proposal is error-prone to use, restricted in what
> > it can do, and incapable of being extended later without breaking
> > things. If there is some redeeming social value to set against those
> > problems, I'm not seeing it.
> >
> > What I think we should have is
> >
> > FOREACH scalar-variable IN ARRAY array-expression
> >
> > which iterates element by element regardless of how many dimensions the
> > array has. Then there should be some other syntax for iterating over
> > slices, and we should give some thought to being able to specify how
> > "deep" the slice is. I can definitely think of use cases for pulling
> > off either 1 dimension at a time (so you get vectors) or N-1 dimensions
> > at a time, and it's not out of the realm of reason to want intermediate
> > cases.
> >
> > Maybe
> >
> > FOR_EACH scalar-variable IN ARRAY array-expression
> >
> > FOR_SLICE array-variable [DEPTH n] IN ARRAY array-expression
> >
> > Or I guess you could use the same leading keyword if you make the depth
> > specification mandatory for the slice case:
> >
> > FOREACH scalar-variable IN ARRAY array-expression
> >
> > FOREACH array-variable SLICE n IN ARRAY array-expression
> >
> > That might be a better idea since it avoids the inevitable argument over
> > whether the default slice depth should be 1 dimension or N-1 dimensions.
>
> another way:
>
> FOREACH scalar IN ARRAY arr_exp DIMS in dim_var

> dim_var being int[], or possibly text, of length #dimensions, giving
> per dimesion index.
>
If dim_var contains length it is need to be renamed:
FOREACH scalar IN ARRAY arr_exp SIZES IN sizes_var.

>
> I like this because it would fit well with alternate form of unnest,
> should it ever be written:
>
> create function unnest(anyarray, dims out int[], elem out anyelement)
> returns setof...
>
> SLICE notation is still good though, and it's probably faster since
> you have less work to do in iteration step? It's certainly easier,
> but very plpgsql specific.
>
> merlin
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

--
// Dmitriy.

On Sat, Dec 18, 2010 at 02:03, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>        FOREACH scalar-variable IN ARRAY array-expression
>>        FOR_EACH scalar-variable IN ARRAY array-expression
>>        FOR_SLICE array-variable [DEPTH n] IN ARRAY array-expression
>>        FOREACH scalar-variable IN ARRAY array-expression
>>        FOREACH array-variable SLICE n IN ARRAY array-expression
> FOREACH scalar IN ARRAY arr_exp DIMS in dim_var

It should be not a main subject, but I remember there was a discussion
that "IN ARRAY array-expression" looks redundant for a literal array:

IN ARRAY ARRAY[1, 3, 5]

Are there any improvement for the issue?

--
Itagaki Takahiro

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> another way:

> FOREACH scalar IN ARRAY arr_exp DIMS in dim_var

> dim_var being int[], or possibly text, of length #dimensions, giving
> per dimesion index.

[ scratches head... ] I don't follow what you envision this doing,
exactly?

I'm not thrilled with that specific syntax because it'd require making
DIMS a reserved word, but right at the moment I'm more concerned about
what semantics you have in mind.

regards, tom lane

2010/12/17 Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>:
> On Sat, Dec 18, 2010 at 02:03, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>>        FOREACH scalar-variable IN ARRAY array-expression
>>>        FOR_EACH scalar-variable IN ARRAY array-expression
>>>        FOR_SLICE array-variable [DEPTH n] IN ARRAY array-expression
>>>        FOREACH scalar-variable IN ARRAY array-expression
>>>        FOREACH array-variable SLICE n IN ARRAY array-expression
>> FOREACH scalar IN ARRAY arr_exp DIMS in dim_var
>
> It should be not a main subject, but I remember there was a discussion
> that "IN ARRAY array-expression" looks redundant for a literal array:
>
>  IN ARRAY ARRAY[1, 3, 5]
>
> Are there any improvement for the issue?

yes. It know it. The reason for this is bigger space for possible
future features related to FOREACH loop.

Regards

Pavel

>
> --
> Itagaki Takahiro
>

On Fri, Dec 17, 2010 at 12:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> another way:
>
>> FOREACH scalar IN ARRAY arr_exp DIMS in dim_var
>
>> dim_var being int[], or possibly text, of length #dimensions, giving
>> per dimesion index.
>
> [ scratches head... ]  I don't follow what you envision this doing,
> exactly?
>
> I'm not thrilled with that specific syntax because it'd require making
> DIMS a reserved word, but right at the moment I'm more concerned about
> what semantics you have in mind.

It's like _pg_expandarray but alterted support multiple dimensions:

select * from unnest_dims(array[['a','b'],['c','d']]) returns
[1,1], 'a'
[1,2], 'b'
[2,1], 'c'
[2,2], 'd'

this provides alternate way of pulling slices, slower possibly, but
more abstract.

merlin

On 12/17/2010 12:15 PM, Pavel Stehule wrote:
> 2010/12/17 Itagaki Takahiro<itagaki(dot)takahiro(at)gmail(dot)com>:
>>
>> It should be not a main subject, but I remember there was a discussion
>> that "IN ARRAY array-expression" looks redundant for a literal array:
>>
>> IN ARRAY ARRAY[1, 3, 5]
>>
>> Are there any improvement for the issue?
> yes. It know it. The reason for this is bigger space for possible
> future features related to FOREACH loop.
>

So what you're saying is we need to allow ugliness now so we can have
more ugliness in future? I don't find that a convincing argument. I
share the dislike for this syntax.

cheers

andrew

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Fri, Dec 17, 2010 at 12:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> [ scratches head... ] I don't follow what you envision this doing,
>> exactly?

> It's like _pg_expandarray but alterted support multiple dimensions:

> select * from unnest_dims(array[['a','b'],['c','d']]) returns
> [1,1], 'a'
> [1,2], 'b'
> [2,1], 'c'
> [2,2], 'd'

Oh, so that's an *output* not an input. And IIUC what you are returning
is the subscripts associated with the current element, not the array's
dimensions. Seems like it should go beside the normal target variable
then, not at the end.

FOREACH variable_for_value [, variable_for_subscripts ] IN ARRAY ...

regards, tom lane

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> On 12/17/2010 12:15 PM, Pavel Stehule wrote:
>> The reason for this is bigger space for possible
>> future features related to FOREACH loop.

> So what you're saying is we need to allow ugliness now so we can have
> more ugliness in future? I don't find that a convincing argument. I
> share the dislike for this syntax.

Well, we did beat up Pavel over trying to shoehorn this facility into
the existing FOR syntax, so I can hardly blame him for thinking this
way. The question is whether we're willing to assume that FOREACH will
be limited to iterating over arrays, meaning we'll be stuck with
inventing yet another initial keyword if some other fundamentally
different concept comes along. Right at the moment I can't think of
any plausible candidates, but ...

regards, tom lane

2010/12/17 Andrew Dunstan <andrew(at)dunslane(dot)net>:
>
>
> On 12/17/2010 12:15 PM, Pavel Stehule wrote:
>>
>> 2010/12/17 Itagaki Takahiro<itagaki(dot)takahiro(at)gmail(dot)com>:
>>>
>>> It should be not a main subject, but I remember there was a discussion
>>> that "IN ARRAY array-expression" looks redundant for a literal array:
>>>
>>>  IN ARRAY ARRAY[1, 3, 5]
>>>
>>> Are there any improvement for the issue?
>>
>> yes. It know it. The reason for this is bigger space for possible
>> future features related to FOREACH loop.
>>
>
> So what you're saying is we need to allow ugliness now so we can have more
> ugliness in future? I don't find that a convincing argument. I share the
> dislike for this syntax.

can be strange from me, but it is. If we close a back door now, then
we have not a space after ten years. There can be possible loops over
records, maybe over other iterable data. With this design is important
one think. A keyword after K_IN must not be a reserved keyword.

I am expecting, so typical use case doesn't be a iteration over
constant array, but over variable

so mostly often you have to write

FOREACH var IN ARRAY second_var
LOOP
...
END LOOP

Regards

Pavel

>
> cheers
>
> andrew
>

On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:

> Well, we did beat up Pavel over trying to shoehorn this facility into
> the existing FOR syntax, so I can hardly blame him for thinking this
> way. The question is whether we're willing to assume that FOREACH will
> be limited to iterating over arrays, meaning we'll be stuck with
> inventing yet another initial keyword if some other fundamentally
> different concept comes along. Right at the moment I can't think of
> any plausible candidates, but ...

FOREACH pair IN HSTORE…

David

"David E. Wheeler" <david(at)kineticode(dot)com> writes:
> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
>> Well, we did beat up Pavel over trying to shoehorn this facility into
>> the existing FOR syntax, so I can hardly blame him for thinking this
>> way. The question is whether we're willing to assume that FOREACH will
>> be limited to iterating over arrays, meaning we'll be stuck with
>> inventing yet another initial keyword if some other fundamentally
>> different concept comes along. Right at the moment I can't think of
>> any plausible candidates, but ...

> FOREACH pair IN HSTORE

I don't actually see any problem with allowing that (or any other
"collection" kind of object) with the same syntax as for arrays.

The issue that we had with adding this to FOR was that it wasn't clear
whether the expression after IN should be thought of as a source of
rows, or as a "scalar" expression yielding a collection object that
should get iterated through --- and because SQL allows sub-SELECT as a
kind of expression, this was an actual formal ambiguity not just the
sort of near-ambiguity that trips up users. If you will, it wouldn't
have been clear whether to iterate vertically or horizontally.

The direction that this proposal establishes is that FOR is for vertical
iteration and FOREACH is for horizontal iteration; that is, the argument
of FOREACH is a scalar expression in SQL terms, but it yields some kind
of collection object that we are going to iterate through the members
of. Given that understanding, I'm not seeing a need for the syntax to
distinguish whether the collection object is an array, an hstore, or
some other kind of collection. It's sufficient if we can determine this
by examining the type of the expression.

We would need an extra keyword if there were some third kind of
iteration that was fundamentally different from either of these, but
like I said, I don't see a plausible candidate. So right at the moment,
I'm leaning to the position that we could do without the ARRAY keyword
in FOREACH. If we do think of something else that could need its own
keyword there, it's arguably going to be different enough that a
different leading keyword would be a better idea anyhow.

regards, tom lane

On Fri, Dec 17, 2010 at 1:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
>>> Well, we did beat up Pavel over trying to shoehorn this facility into
>>> the existing FOR syntax, so I can hardly blame him for thinking this
>>> way.  The question is whether we're willing to assume that FOREACH will
>>> be limited to iterating over arrays, meaning we'll be stuck with
>>> inventing yet another initial keyword if some other fundamentally
>>> different concept comes along.  Right at the moment I can't think of
>>> any plausible candidates, but ...
>
>>     FOREACH pair IN HSTORE…
>
> I don't actually see any problem with allowing that (or any other
> "collection" kind of object) with the same syntax as for arrays.
>
> The issue that we had with adding this to FOR was that it wasn't clear
> whether the expression after IN should be thought of as a source of
> rows, or as a "scalar" expression yielding a collection object that
> should get iterated through --- and because SQL allows sub-SELECT as a
> kind of expression, this was an actual formal ambiguity not just the
> sort of near-ambiguity that trips up users.  If you will, it wouldn't
> have been clear whether to iterate vertically or horizontally.
>
> The direction that this proposal establishes is that FOR is for vertical
> iteration and FOREACH is for horizontal iteration; that is, the argument
> of FOREACH is a scalar expression in SQL terms, but it yields some kind
> of collection object that we are going to iterate through the members
> of.  Given that understanding, I'm not seeing a need for the syntax to
> distinguish whether the collection object is an array, an hstore, or
> some other kind of collection.  It's sufficient if we can determine this
> by examining the type of the expression.
>
> We would need an extra keyword if there were some third kind of
> iteration that was fundamentally different from either of these, but
> like I said, I don't see a plausible candidate.  So right at the moment,
> I'm leaning to the position that we could do without the ARRAY keyword
> in FOREACH.  If we do think of something else that could need its own
> keyword there, it's arguably going to be different enough that a
> different leading keyword would be a better idea anyhow.

Unfortunately, there are likely to be a limited number of such
keywords available. While I agree it's helpful to have a clear
distinction between what FOR does and what FOREACH does, it's wholly
conventional here and won't be obvious without careful reading of the
documentation. If we had FOR and FOREACH and FOREVERY and, uh,
FORGET, it'd quickly become notational soup. I am still wondering if
there's a way to make something like "FOR ELEMENT e IN a" work. I
suspect we'd be less likely to paint ourselves into a corner that way.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> We would need an extra keyword if there were some third kind of
> iteration that was fundamentally different from either of these, but
> like I said, I don't see a plausible candidate.  So right at the moment,
> I'm leaning to the position that we could do without the ARRAY keyword
> in FOREACH.  If we do think of something else that could need its own
> keyword there, it's arguably going to be different enough that a
> different leading keyword would be a better idea anyhow.
>

Maybe I propage a higher verbosity than is necessary, but it descrease
a risk so code will do some unexpected work. With ARRAY keyword we can
verify so result of expression is really a array. Next advantage is a
clean implementation now and in future. Without a auxilary keyword is
necessary to wait on execution time. So now, when we have full control
over syntax, we can protect self before "FOR" statement
implementation's complexity.

Personally - syntax without ARRAY keyword isn't significant problem
for me. Just I think so using it wisely.

Second semi argument for using ARRAY keyword is a verbosity of
PL/pgSQL. So from this perspective a ARRAY should be minimally
optional and ensure, so expr result will be really a array. But with a
optional ARRAY keyword we leaving a simple enhancing in future (on
parser level).

Pavel

>                        regards, tom lane
>

On 17.12.2010 21:04, Robert Haas wrote:
> Unfortunately, there are likely to be a limited number of such
> keywords available. While I agree it's helpful to have a clear
> distinction between what FOR does and what FOREACH does, it's wholly
> conventional here and won't be obvious without careful reading of the
> documentation. If we had FOR and FOREACH and FOREVERY and, uh,
> FORGET, it'd quickly become notational soup. I am still wondering if
> there's a way to make something like "FOR ELEMENT e IN a" work. I
> suspect we'd be less likely to paint ourselves into a corner that way.

As a side note, Oracle has FORALL, which is a kind of bulk update
operation over a collection type. So whatever we choose, not FORALL...

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

2010/12/17 Robert Haas <robertmhaas(at)gmail(dot)com>:
> On Fri, Dec 17, 2010 at 1:38 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "David E. Wheeler" <david(at)kineticode(dot)com> writes:
>>> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
>>>> Well, we did beat up Pavel over trying to shoehorn this facility into
>>>> the existing FOR syntax, so I can hardly blame him for thinking this
>>>> way.  The question is whether we're willing to assume that FOREACH will
>>>> be limited to iterating over arrays, meaning we'll be stuck with
>>>> inventing yet another initial keyword if some other fundamentally
>>>> different concept comes along.  Right at the moment I can't think of
>>>> any plausible candidates, but ...
>>
>>>     FOREACH pair IN HSTORE…
>>
>> I don't actually see any problem with allowing that (or any other
>> "collection" kind of object) with the same syntax as for arrays.
>>
>> The issue that we had with adding this to FOR was that it wasn't clear
>> whether the expression after IN should be thought of as a source of
>> rows, or as a "scalar" expression yielding a collection object that
>> should get iterated through --- and because SQL allows sub-SELECT as a
>> kind of expression, this was an actual formal ambiguity not just the
>> sort of near-ambiguity that trips up users.  If you will, it wouldn't
>> have been clear whether to iterate vertically or horizontally.
>>
>> The direction that this proposal establishes is that FOR is for vertical
>> iteration and FOREACH is for horizontal iteration; that is, the argument
>> of FOREACH is a scalar expression in SQL terms, but it yields some kind
>> of collection object that we are going to iterate through the members
>> of.  Given that understanding, I'm not seeing a need for the syntax to
>> distinguish whether the collection object is an array, an hstore, or
>> some other kind of collection.  It's sufficient if we can determine this
>> by examining the type of the expression.
>>
>> We would need an extra keyword if there were some third kind of
>> iteration that was fundamentally different from either of these, but
>> like I said, I don't see a plausible candidate.  So right at the moment,
>> I'm leaning to the position that we could do without the ARRAY keyword
>> in FOREACH.  If we do think of something else that could need its own
>> keyword there, it's arguably going to be different enough that a
>> different leading keyword would be a better idea anyhow.
>
> Unfortunately, there are likely to be a limited number of such
> keywords available.  While I agree it's helpful to have a clear
> distinction between what FOR does and what FOREACH does, it's wholly
> conventional here and won't be obvious without careful reading of the
> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
> FORGET, it'd quickly become notational soup.  I am still wondering if
> there's a way to make something like "FOR ELEMENT e IN a" work.  I
> suspect we'd be less likely to paint ourselves into a corner that way.

I understand. But it is true too , so now is FOR statement
implementation too rich. You can see to attachment with initial
implementation. It's absolutely clean and simple. There is more valid
ideas then one. One valid idea is so FOR statement is compatible with
PL/SQL (what isn't true now :() and FOREACH can carry a pg's specific
features.

But I absolutely agree with you, so we can use a only one pg specific
keyword. There are FOREACH (pg), FOR (shared with PL/SQL), FORALL (not
implemented yet - use a PL/SQL).

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> Unfortunately, there are likely to be a limited number of such
> keywords available. While I agree it's helpful to have a clear
> distinction between what FOR does and what FOREACH does, it's wholly
> conventional here and won't be obvious without careful reading of the
> documentation. If we had FOR and FOREACH and FOREVERY and, uh,
> FORGET, it'd quickly become notational soup.

All true, but in the absence of any plausible candidate for third or
fourth or fifth types of iteration, this objection seems a bit thin.

> I am still wondering if
> there's a way to make something like "FOR ELEMENT e IN a" work. I
> suspect we'd be less likely to paint ourselves into a corner that way.

I'm afraid that's only really feasible if you are willing for the second
word to be a fully reserved word, so it can be distinguished from a
plain variable name in that position. Which is probably worse than
inventing multiple initial keywords. It doesn't seem to me that this
would reduce the intellectual burden of remembering which syntax does
what, anyway.

regards, tom lane

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> Second semi argument for using ARRAY keyword is a verbosity of
> PL/pgSQL. So from this perspective a ARRAY should be minimally
> optional and ensure, so expr result will be really a array. But with a
> optional ARRAY keyword we leaving a simple enhancing in future (on
> parser level).

No. If we are going to put a keyword there, it can't be optional.
Making it optional would require it to be a fully reserved word
--- and in the case of ARRAY, even that isn't good enough, because
of the conflict with ARRAY[...] syntax.

regards, tom lane

2010/12/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> Second semi argument for using ARRAY keyword is a verbosity of
>> PL/pgSQL. So from this perspective a ARRAY should be minimally
>> optional and ensure, so expr result will be really a array. But with a
>> optional ARRAY keyword we leaving a simple enhancing in future (on
>> parser level).
>
> No.  If we are going to put a keyword there, it can't be optional.
> Making it optional would require it to be a fully reserved word
> --- and in the case of ARRAY, even that isn't good enough, because
> of the conflict with ARRAY[...] syntax.

yes, it's true

Pavel

>
>                        regards, tom lane
>

On Fri, Dec 17, 2010 at 2:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> Unfortunately, there are likely to be a limited number of such
>> keywords available.  While I agree it's helpful to have a clear
>> distinction between what FOR does and what FOREACH does, it's wholly
>> conventional here and won't be obvious without careful reading of the
>> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
>> FORGET, it'd quickly become notational soup.
>
> All true, but in the absence of any plausible candidate for third or
> fourth or fifth types of iteration, this objection seems a bit thin.

Well, Heikki just pointed out one that Oracle supports, so that makes
at least #3...

>> I am still wondering if
>> there's a way to make something like "FOR ELEMENT e IN a" work.  I
>> suspect we'd be less likely to paint ourselves into a corner that way.
>
> I'm afraid that's only really feasible if you are willing for the second
> word to be a fully reserved word, so it can be distinguished from a
> plain variable name in that position.

What if we cheat and peak ahead an extra token?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Dec 17, 2010 at 2:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> Unfortunately, there are likely to be a limited number of such
>>> keywords available. While I agree it's helpful to have a clear
>>> distinction between what FOR does and what FOREACH does, it's wholly
>>> conventional here and won't be obvious without careful reading of the
>>> documentation. If we had FOR and FOREACH and FOREVERY and, uh,
>>> FORGET, it'd quickly become notational soup.

>> All true, but in the absence of any plausible candidate for third or
>> fourth or fifth types of iteration, this objection seems a bit thin.

> Well, Heikki just pointed out one that Oracle supports, so that makes
> at least #3...

If you posit that we might someday wish to support what Oracle is doing
there, it seems to me to be a precedent for using a different first
keyword, not for what you're suggesting. I'm not arguing that we might
want to duplicate Oracle's syntax; only that if it's going to be cited
as a precedent that we consider what it's actually a precedent for.

>> I'm afraid that's only really feasible if you are willing for the second
>> word to be a fully reserved word, so it can be distinguished from a
>> plain variable name in that position.

> What if we cheat and peak ahead an extra token?

plpgsql's parser is rickety enough that I don't have a lot of confidence
in its ability to do things that way. In particular, there's too much
knowledge at the lexer level instead of the grammar --- you'd have to
have a way of keeping the lexer from returning T_DATUM in this one
particular context, even if "element" happened to match some variable.

regards, tom lane

On Fri, Dec 17, 2010 at 2:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Fri, Dec 17, 2010 at 2:15 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>>> Unfortunately, there are likely to be a limited number of such
>>>> keywords available.  While I agree it's helpful to have a clear
>>>> distinction between what FOR does and what FOREACH does, it's wholly
>>>> conventional here and won't be obvious without careful reading of the
>>>> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
>>>> FORGET, it'd quickly become notational soup.
>
>>> All true, but in the absence of any plausible candidate for third or
>>> fourth or fifth types of iteration, this objection seems a bit thin.
>
>> Well, Heikki just pointed out one that Oracle supports, so that makes
>> at least #3...
>
> If you posit that we might someday wish to support what Oracle is doing
> there, it seems to me to be a precedent for using a different first
> keyword, not for what you're suggesting.  I'm not arguing that we might
> want to duplicate Oracle's syntax; only that if it's going to be cited
> as a precedent that we consider what it's actually a precedent for.

I don't quite follow what you're getting at here. My goal was to try
to think of something more mnemonic than FOREACH, and I thought
something involving the word "element" or "array" would do the trick.
The problem is only to find a place to put it that's before the word
"IN". But maybe that's hopeless and we should just go with FOREACH.

>>> I'm afraid that's only really feasible if you are willing for the second
>>> word to be a fully reserved word, so it can be distinguished from a
>>> plain variable name in that position.
>
>> What if we cheat and peak ahead an extra token?
>
> plpgsql's parser is rickety enough that I don't have a lot of confidence
> in its ability to do things that way.

Bummer. Rickety is not good.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Fri, Dec 17, 2010 at 2:58 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> plpgsql's parser is rickety enough that I don't have a lot of confidence
>> in its ability to do things that way.

> Bummer. Rickety is not good.

Agreed, but it's not entirely the parser's fault: the language
definition is pretty d*mn bogus to start with. Read the comments for
the for_variable production, and ask yourself whether you really want
to inject even more difficult-to-disambiguate cases right there.

regards, tom lane