Re: Proposal: plpgsql - "for in array" statement

2010/9/28 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

>                        regards, tom lane
>