Proposal: plpgsql - "for in array" statement

On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]

What is the benefits compared with
FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?

--
Itagaki Takahiro

2010/9/28 Itagaki Takahiro <itagaki.takahiro@gmail.com>:

On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]

What is the benefits compared with
FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?

the speed

SELECT unnest() is full query, but array_expression is just simple
query and can be evaluated by
exec_eval_simple_expr - it can be significantly times faster.
CREATE OR REPLACE FUNCTION f1()
RETURNS void AS $$
DECLARE a int[] := ARRAY[1,2,3,4];
s int;
BEGIN
FOR i IN 1..10000 LOOP
s := 0;
FOR j IN array_lower(a,1)..array_upper(a,1)
LOOP
s := s + a[j];
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;

take about 255ms

CREATE OR REPLACE FUNCTION f1()
RETURNS void AS $$
DECLARE a int[] := ARRAY[1,2,3,4]; j int;
s int;
BEGIN
FOR i IN 1..10000 LOOP
s := 0;
FOR j IN SELECT unnest(a)
LOOP
s := s + j;
END LOOP;
END LOOP;
END;
$$ LANGUAGE plpgsql;

it takes abou 1000ms

Regards

Pavel Stehule

2010/9/28 Itagaki Takahiro <itagaki.takahiro@gmail.com>:

On Tue, Sep 28, 2010 at 3:24 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]

What is the benefits compared with
FOR ... IN SELECT unnest(array) or generate_subscripts(array) ?

the speed

Not to mention that it's far more aesthetically pleasing, and makes
the statement immediately understandable to people unfamiliar with the
plpgsql idioms you describe.

--
Regards,
Peter Geoghegan

Pavel Stehule <pavel.stehule@gmail.com> writes:

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]
LOOP

I don't have any opinion about whether the functionality proposed here
is worth the trouble, but I do have an opinion about that syntax: it's
an awful choice. plpgsql has enough trouble already distinguishing
between integer for-loops and query for-loops, not to mention trouble
in producing a helpful error message when somebody gets either of those
constructs slightly wrong. Providing a variant where a single
expression can follow IN will make both of those problems an order of
magnitude worse. As an example, is this a for-in-query or a
for-in-array?

FOR v IN (SELECT arraycol FROM tab) LOOP ...

Either answer is plausible depending on whether you assume the
parentheses make it a subquery.

Pick something less easily confusable with the existing constructs.

regards, tom lane

On Tue, Sep 28, 2010 at 10:34 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]
LOOP

I don't have any opinion about whether the functionality proposed here
is worth the trouble, but I do have an opinion about that syntax: it's
an awful choice.

I agree, on both points.

It's nice to try to reduce the excess verbosity that is IMHO the
biggest usability issue with PL/pgsql, but I can't help wondering
whether we're trying to dam the Nile with chicken wire.

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

On Sep 28, 2010, at 7:41 AM, Robert Haas wrote:

I don't have any opinion about whether the functionality proposed here
is worth the trouble, but I do have an opinion about that syntax: it's
an awful choice.

I agree, on both points.

It's nice to try to reduce the excess verbosity that is IMHO the
biggest usability issue with PL/pgsql, but I can't help wondering
whether we're trying to dam the Nile with chicken wire.

I would really like to have this functionality. Iterating over arrays is something I do in PL/pgSQL a *lot*.

I see two ways to handle syntax:

1. A new keyword. Some options:
   + FOREACH (too close to FOR?)
   + ITERATE (ew)

2. Require the subscripts() function as syntax.

Thoughts?

Best,

David

Robert Haas <robertmhaas@gmail.com> wrote:

FOR var IN [array variable | array expression]
LOOP

I don't have any opinion about whether the functionality proposed
here is worth the trouble, but I do have an opinion about that
syntax: it's an awful choice.

I agree, on both points.

How about distinguishing it this way:?

FOR var IN ARRAY array_expression LOOP

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

FOR var IN [array variable | array expression]
LOOP

How about distinguishing it this way:?

FOR var IN ARRAY array_expression LOOP

That occurred to me too, but it's got a small problem: it's not
impossible for ARRAY to be the first token of a valid scalar expression.

But I guess you could get around that if you had to by putting the ARRAY
expression inside parens, and it would be a pretty darn unusual case
anyway. So this is probably the best choice. I'm not thrilled with
David's suggestions of using FOREACH or ITERATE --- using a different
initial keyword makes it awkward to make generic statements about "all
types of FOR loop".

regards, tom lane

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I looked on some constructs that helps with iteration over array in
plpgsql. I propose a following syntax:

FOR var IN [array variable | array expression]
LOOP

I don't have any opinion about whether the functionality proposed here
is worth the trouble, but I do have an opinion about that syntax: it's
an awful choice.  plpgsql has enough trouble already distinguishing
between integer for-loops and query for-loops, not to mention trouble
in producing a helpful error message when somebody gets either of those
constructs slightly wrong.  Providing a variant where a single
expression can follow IN will make both of those problems an order of
magnitude worse.  As an example, is this a for-in-query or a
for-in-array?

       FOR v IN (SELECT arraycol FROM tab) LOOP ...

This is a subquery - so it is a for-in-array - should return one row
with one column. Similar construct is in SQL/PSM

where you can to write SET var = (SELECT ...)

You cannot to write just (SELECT ...) anywhere

Either answer is plausible depending on whether you assume the
parentheses make it a subquery.

Pick something less easily confusable with the existing constructs.

It's not simple - FOR i IN array is natural - Original ADA use a very
similar construct.

FOR i IN ARRAY has problem with constant array - FOR i IN ARRAY ARRAY[1,2,3,]
and FOREACH is used in Oracle for absolutely different task.

we have now a for-in-cursor, so there is a precedent.

regards

Pavel

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

FOR var IN [array variable | array expression]
LOOP

How about distinguishing it this way:?

FOR var IN ARRAY array_expression LOOP

I see one problem - when you can use a constant array, then you will
write two keywords ARRAY

FOR var IN ARRAY ARRAY[...] LOOP

iteration over cursor is supported now, and you don't write

FOR var IN CURSOR cursorvar

That occurred to me too, but it's got a small problem: it's not
impossible for ARRAY to be the first token of a valid scalar expression.

yes

But I guess you could get around that if you had to by putting the ARRAY
expression inside parens, and it would be a pretty darn unusual case
anyway.  So this is probably the best choice.

I don't agree - There isn't reason for complicating proposed syntax.
The situation is relative simple and we are able to print a correct
messages.

regards

Pavel Stehule

I'm not thrilled with

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

But I guess you could get around that if you had to by putting the ARRAY
expression inside parens, and it would be a pretty darn unusual case
anyway.  So this is probably the best choice.

I don't agree - There isn't reason for complicating proposed syntax.

Yes, there is. The syntax you propose is flat out ambiguous: there are
two possible legal interpretations of some commands. That's not
acceptable, especially not when it's so easily fixed.

regards, tom lane

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

But I guess you could get around that if you had to by putting the ARRAY
expression inside parens, and it would be a pretty darn unusual case
anyway.  So this is probably the best choice.

I don't agree - There isn't reason for complicating proposed syntax.

Yes, there is.  The syntax you propose is flat out ambiguous: there are
two possible legal interpretations of some commands.  That's not
acceptable, especially not when it's so easily fixed.

what are you thinking? The subquery cannot be interpreted different.
There are not possible use a isolated subquery as query. And subquery
have to return one row, one column.

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

As an example, is this a for-in-query or a
for-in-array?

       FOR v IN (SELECT arraycol FROM tab) LOOP ...

This is a subquery - so it is a for-in-array - should return one row
with one column.

That's not obvious at all. It's legal right now to write that, and it
will be interpreted as for-in-query. Furthermore, there are cases where
it's essential to be able to write a left paren before SELECT, so that
you can control the precedence of UNION/INTERSECT/EXCEPT constructs.
So you're proposing to remove functionality and break existing code in
order to have a "simple" syntax for for-in-array.

regards, tom lane

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Yes, there is.  The syntax you propose is flat out ambiguous: there are
two possible legal interpretations of some commands.

what are you thinking? The subquery cannot be interpreted different.

Sure it can: it could be a parenthesized top-level query. In fact,
that's what plpgsql will assume if you feed it that syntax today.

regards, tom lane

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

As an example, is this a for-in-query or a
for-in-array?

       FOR v IN (SELECT arraycol FROM tab) LOOP ...

This is a subquery - so it is a for-in-array - should return one row
with one column.

That's not obvious at all.  It's legal right now to write that, and it
will be interpreted as for-in-query.

but it has not a sense. It's based on implementation and I am sure, so
this isn't documented. Yes, we are able to write

a := 10 FROM tab WHERE y = 10

but it is just more bug then required feature.

FOR v IN (SELECT FROM) when select returns more than one row is big
inconsistency - and this is bug, when this is allowed

Regards

Pavel

 Furthermore, there are cases where

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Yes, there is.  The syntax you propose is flat out ambiguous: there are
two possible legal interpretations of some commands.

what are you thinking? The subquery cannot be interpreted different.

Sure it can: it could be a parenthesized top-level query.  In fact,
that's what plpgsql will assume if you feed it that syntax today.

no - there are not any legal construct FOR r IN (..)

I believe so we can find more than one similar undocumented features,
like this - so it means so plpgsql will be a buggy?

On Tue, Sep 28, 2010 at 4:39 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

As an example, is this a for-in-query or a
for-in-array?

       FOR v IN (SELECT arraycol FROM tab) LOOP ...

This is a subquery - so it is a for-in-array - should return one row
with one column.

That's not obvious at all.  It's legal right now to write that, and it
will be interpreted as for-in-query.

but it has not a sense.

It has a very fine sense. It's completely obvious to me what that
means, and you're proposing to break it. In a word: no.

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

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Sure it can: it could be a parenthesized top-level query.  In fact,
that's what plpgsql will assume if you feed it that syntax today.

no - there are not any legal construct FOR r IN (..)

You are simply wrong, sir, and I suggest that you go read the SQL
standard until you realize that. Consider for example

for r in (SELECT ... FROM a UNION SELECT ... FROM b) INTERSECT (SELECT ... FROM c) LOOP ...

The parentheses here are not merely legal, they are *necessary*, else
the semantics of the UNION/INTERSECT operations change.

regards, tom lane

On 09/28/2010 03:41 PM, Pavel Stehule wrote:

It's not simple - FOR i IN array is natural - Original ADA use a very
similar construct.

No it doesn't. In Ada (Note: not ADA) FOR can only iterate over one
thing: a discrete subtype (e.g. an integer or enumeration type, or a
range of it)[1]http://www.adaic.org/standards/05rm/html/RM-5-5.html. You can say:

for i in my_array'range loop ...

but that iterates over the array's index, not over its values.And there
is no ambiguity with other things you might loop over because there
aren't any.

cheers

andrew

[1]: http://www.adaic.org/standards/05rm/html/RM-5-5.html

Excerpts from Kevin Grittner's message of mar sep 28 12:28:12 -0400 2010:

How about distinguishing it this way:?

FOR var IN ARRAY array_expression LOOP

What about

FOR EACH var IN array_expr LOOP ...

I think this requires reserving EACH, which could cause a regression for
working code. Maybe there's a way to make it work?

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

Alvaro Herrera wrote:

What about

FOR EACH var IN array_expr LOOP ...

I think this requires reserving EACH, which could cause a regression for
working code. Maybe there's a way to make it work?

Code that quotes all of its identifiers, such as with:

FOR EACH "var" IN "array_expr" LOOP ...

... would also gain a significant amount of future-proofing since then the
language can add keywords at will, without there being conflicts with
user-defined identifiers.

Similarly, quoting identifiers also carries present-day advantages as then one
can name identifiers whatever is most suitably descriptive for them without
worrying whether the language has a pre-defined meaning for the used words.

The quoting also has the nice bonus of making them case-sensitive.

-- Darren Duncan

Alvaro Herrera wrote:

What about

FOR EACH var IN array_expr LOOP ...

I think this requires reserving EACH, which could cause a regression for
working code. Maybe there's a way to make it work?

What about saying FOR-EACH instead?

A good general solution that I'd expect to not cause regressions is to separate
multiple-word keywords with dashes rather than spaces.

Since unquoted identifiers don't have dashes, I think, and moreover because the
whole FOR-EACH would occupy the first position of the statement rather than the
first two, there should be no ambiguity.

Parsing should be easier, too, because keywords formatted like this would just
be a single term rather than having infinite variations due to embedded whitespace.

-- Darren Duncan

Alvaro Herrera <alvherre@commandprompt.com> writes:

Excerpts from Kevin Grittner's message of mar sep 28 12:28:12 -0400 2010:

How about distinguishing it this way:?
FOR var IN ARRAY array_expression LOOP

What about
FOR EACH var IN array_expr LOOP ...

That doesn't seem to have any obvious connection to looping over array
elements, as opposed to some other kind of iteration.

regards, tom lane

On 09/28/2010 09:31 PM, Darren Duncan wrote:

Alvaro Herrera wrote:

What about

FOR EACH var IN array_expr LOOP ...

I think this requires reserving EACH, which could cause a regression for
working code. Maybe there's a way to make it work?

Code that quotes all of its identifiers, such as with:

FOR EACH "var" IN "array_expr" LOOP ...

... would also gain a significant amount of future-proofing since then
the language can add keywords at will, without there being conflicts
with user-defined identifiers.

Similarly, quoting identifiers also carries present-day advantages as
then one can name identifiers whatever is most suitably descriptive
for them without worrying whether the language has a pre-defined
meaning for the used words.

The quoting also has the nice bonus of making them case-sensitive.

This doesn't help in the least if the array is an expression rather than
simply a variable - we're not going to start quoting expressions.

cheers

andrew

On 09/28/2010 09:43 PM, Darren Duncan wrote:

Alvaro Herrera wrote:

What about

FOR EACH var IN array_expr LOOP ...

I think this requires reserving EACH, which could cause a regression for
working code. Maybe there's a way to make it work?

What about saying FOR-EACH instead?

A good general solution that I'd expect to not cause regressions is to
separate multiple-word keywords with dashes rather than spaces.

Since unquoted identifiers don't have dashes, I think, and moreover
because the whole FOR-EACH would occupy the first position of the
statement rather than the first two, there should be no ambiguity.

Parsing should be easier, too, because keywords formatted like this
would just be a single term rather than having infinite variations due
to embedded whitespace.

This would actually make the parsing infinitely more ugly, not less. And
we are not gong to start introducing non-alphabetic characters into
keywords. It is also, as Tom noted about the earlier version, without
any obvious connection to array processing.

cheers

andrew

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2010/9/28 Tom Lane <tgl@sss.pgh.pa.us>:

Sure it can: it could be a parenthesized top-level query.  In fact,
that's what plpgsql will assume if you feed it that syntax today.

no - there are not any legal construct FOR r IN (..)

You are simply wrong, sir, and I suggest that you go read the SQL
standard until you realize that.  Consider for example

       for r in (SELECT ... FROM a UNION SELECT ... FROM b) INTERSECT (SELECT ... FROM c) LOOP ...

The parentheses here are not merely legal, they are *necessary*, else
the semantics of the UNION/INTERSECT operations change.

ok, then probably one variant is for-in-array array_expr. Is there agreement?

Regards

Pavel Stehule

Andrew Dunstan wrote:

On 09/28/2010 09:31 PM, Darren Duncan wrote:

Code that quotes all of its identifiers, such as with:

FOR EACH "var" IN "array_expr" LOOP ...

This doesn't help in the least if the array is an expression rather than
simply a variable - we're not going to start quoting expressions.

I wrote that wrong. I should have said "array_var" not "array_expr". I am
certainly not advocating quoting expressions, and didn't mean to imply that
here. My point was that if a token is always interpreted as a keyword rather
than an identifier when there is ambiguity, then quoting would let users name an
identifier "each" or "EACH". In any event, I will not push this since it
doesn't address the real issue of language changes not breaking the general case
of legacy code; it only says how users can insulate themselves. -- Darren Duncan